Insert Python List into a single column in mySQL Database
Question: Hi I am trying to insert a python list into a single column but it keeps
giving an error on the syntax. New to this. Appreciate any help. Thanks.

    
    
    from time import time
    import MySQLdb
    import urllib
    import re
    from bs4 import BeautifulSoup
    
    db = MySQLdb.connect("localhost","testuser","test123","testdb" )
    cursor = db.cursor()
    
    x=1
    while x<2:
    
        url = "http://search.insing.com/ts/food-drink/bars-pubs/bars-pubs?page=" +str(x)
        htmlfile = urllib.urlopen(url)
        soup = BeautifulSoup(htmlfile)
        reshtml = [h3.a for h3 in soup.find("div", "results").find_all("h3")]
        reslist = []
        for item in reshtml:
    
                res = item.text.encode('ascii', 'ignore')
                reslist.append(' '.join(res.split()))
    
    
        sql = "INSERT INTO insing(name) \
        VALUES %r" \
        % reslist
    
    
    
    
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()
            db.close()
    
            x += 1
    

The output for SQL is

> 'INSERT INTO insing(name) VALUES [\'AdstraGold Microbrewery & Bistro Bar\',
> \'Alkaff Mansion Ristorante\', \'Parco Caffe\', \'The Fat Cat Bistro\',
> \'Gravity Bar\', \'The Wine Company (Evans Road)\', \'Serenity Spanish Bar &
> Restaurant (VivoCity)\', \'The New Harbour Cafe & Bar\', \'Indian Times\',
> \'Sunset Bay Beach Bar\', \'Friends @ Jelita\', \'Talk Cock Sing Song @
> Thomson\', \'En Japanese Dining Bar (UE Square)\', \'Magma German Wine
> Bistro\', "Tam Kah Shark\'s Fin", \'Senso Ristorante & Bar\', \'Hard Rock
> Cafe (HPL House)\', \'St. James Power Station\', \'The St. James\',
> \'Brotzeit German Bier Bar & Restaurant (Vivocity)\']'


Answer: what about

    
    
    insert into table(name) values ('name1'), ('name2'), ... , ('name36');
    

[Inserting multiple rows in a single SQL
query?](http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-
a-single-sql-query)

That might help too.

**EDIT**

I automated the process as well:

    
    
    dataSQL = "INSERT INTO PropertyRow (SWID, Address, APN, PropertyType, PermissableUse, UseDetail, ReviewResult, Analysis, DocReviewed, AqDate, ValuePurchase, ValueCurrent, ValueDate, ValueBasis, ValueSale, SaleDate, PropPurpose, LotSize, Zoning, ParcelValue, EstRevenue, ReqRevenue, EnvHistory, TransitPotential, PlanObjective, PrevHistory, LastUpdDate, LastUpdUser)"
    fields = "VALUES ("+"'"+str(rawID)+"', "
    
    if(cell.ctype != 0):
        while column < 27:
        #column 16 will always be blank
        if (column == 16):
            column += 1
        #column 26 is the end
        if (column == 26):
            fields += "'"+str(sh.cell_value(rowx=currentRow, colx=column)) + "'"
        else:
            #append to the value string
            fields += "'"+str(sh.cell_value(rowx=currentRow, colx=column)) + "', "
            #print fields
            column+=1
            fields += ');'
            writeFyle.write(dataSQL)
            writeFyle.write(fields)
    

In this implementation I am writing an insert statement for each row that I
wanted to insert. This wasn't necessary but it was much easier.

