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)\']'

Make the list into one string and save it to the DB. If you need it in list form, when you load it form the DB split it back into a list. — kylieCatt, Jan 29 '14 at 18:02

score 0 · Answer 1 · edited May 23 '17 at 10:25

what about

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

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.

Insert Python List into a single column in mySQL Database

1 Answers1