database - python2.7 MySQLdb syntax error when inserting a list -
i trying insert scraped data mysql database.....i found codes needed far internet can understand of it.......here code:
import mysqldb bs4 import beautifulsoup import requests r = requests.get("http://www.metalinjection.net/") data = r.text soup = beautifulsoup(data, "lxml") news = soup.find_all("article", {"class": "post"}) titles = [] tits in news: titles.append(tits.contents[3].find_all("h2", {"class": "title"}) [0].text) print titles images = [] image_link in news: images.append(image_link.img['src']) print images link in news: l1 = link.a['href'] db = mysqldb.connect(host="localhost", user="admin", passwd="admin", db="lot_data") t2 =d2 =i2 = "die" dbc = db.cursor() db.set_character_set('utf8') dbc.execute('set names utf8;') dbc.execute('set character set utf8;') dbc.execute('set character_set_connection=utf8;') sql = "insert lot_data values('%s', '%s', '%s')" % \ (titles, images , "nothing") rows = dbc.execute(sql) db.commit() db.close()
when run script gives out following error:
traceback (most recent call last): file "c:/python27/ques.py", line 32, in <module> rows = dbc.execute(sql) file "c:\python27\lib\site-packages\mysqldb\cursors.py", line 205, in execute self.errorhandler(self, exc, value) file "c:\python27\lib\site-packages\mysqldb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue programmingerror: (1064, 'you have error in sql syntax; check manual corresponds mariadb server version right syntax use near \'the monday grind: mellow harsher served cold\', u"metallica\'s former producer apo\' @ line 1')
when replace titles or images strings,they inserted without errors.....also please point me if there better, organized way inserting....
titles
, images
lists. can't insert list directly way. insert
creates 1 row @ time. need iterate on lists , insert 1 row in each iteration.
provided 2 lists corresponding (same length, elements on same index belong together) can rewrite lower part of program, insert db, this:
sql = "insert lot_data values(%s, %s, %s)" (title, imagelink) in zip(titles, images): data = (title, imagelink, "nothing") dbc.execute(sql, data)
this code iterates on 2 lists , inserts elements 1 one.
edit: optimized sql statement. replace existing code between dbc.ececute(...
, db.commit()
code above.
Comments
Post a Comment