CODEWITHSUNDEEP
databasepython-2.7mysql-python
Niranga Sithara
Niranga Sithara

Reputation: 63

python2.7 MySQLdb syntax error when inserting a list

I am trying to insert the scraped data into a MySQL database.....I found all the codes I needed so far from the internet but can understand most of it.......here is my code:

import MySQLdb
from 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 = []
for tits in news:
    titles.append(tits.contents[3].find_all("h2", {"class": "title"})
[0].text)
print titles
images = []
for image_link in news:
    images.append(image_link.img['src'])
print images    
for 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 into lot_data VALUES('%s', '%s', '%s')" % \
 (titles, images , "nothing") 
rows = dbc.execute(sql)
db.commit()
db.close()

when I run the script it gives out the 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 an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'The Monday Grind: MELLOW HARSHER Served Cold\', u"METALLICA\'s Former Producer Apo\' at line 1')

when I replace titles or images by strings,they get inserted without errors.....also please point me if there is a better, organized way to do the inserting....

Upvotes: 2

Views: 95

Answers (1)

jps
jps

Reputation: 22575

titles and images are lists. You can't insert a list directly this way. insertcreates one row at a time. You need to iterate over the lists and insert one row in each iteration.

Provided that the two lists are corresponding (same length, elements on the same index belong together) you can rewrite the lower part of your program, where you insert into the db, like this:

sql = "insert into lot_data VALUES(%s, %s, %s)"
for (title, imagelink) in zip(titles, images):
    data = (title, imagelink, "nothing")
    dbc.execute(sql, data)

This code iterates over the two lists and inserts the elements one by one.

Edit: optimized the sql statement. Replace the existing code between dbc.ececute(... and db.commit() with my code above.

Upvotes: 1

Related Questions