Error While Inserting Into Mysql From Python For Loop

Possible Duplicate: convert list to string to insert into my sql in one row in python scrapy Is this script correct. I want to insert the scraped out h2,h3,meta data directly in

Solution 1:

It seems one of your list has only one element in them, which is causing the problem. Please check all the lists :

 items = [site.select('//h2').extract()]
 item = [site.select('//h3').extract()]
 item1 = [site.select('//meta').extract()]

Make sure they are as expected.

for index,index1,index2 in range (len( items)),range(len(item)),range(len(item1))

this syntax iterates over all the lists at once, if any of the len of lists don't match, value error will be raised,

For Better understanding of your problem see below:

In [1]: l1 = [1,2,3]

In [2]: l2 = [4,5,6]

In [3]: l3 = [7]

In [4]: for index,index1,index2 in range (len( l1)),range(len(l2)),range(len(l3)):
   ....:     print "Hi"
   ....:     
   ....:     
Hi
Hi
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)

/home/avasal/<ipython console> in <module>()

ValueError: need more than 1 value to unpack

can you try this if possible:

for index,index1,index2 in zip(range (len( items)),range(len(item)),range(len(item1)))

Solution 2:

Just to check - are you sure this is what you want to do?

the command

for x, y, z in l1, l2, l3

will return the first elements from each list, then the second, and so on. The better way to do this is using itertools, specifically izip

from itertools import izip

for x, y, z in izip(l1, l2, l3)

is logically equivalent, but much more efficient (izip is a generator)

however, it looks like what you are trying to to is index the elements of each list sequentially, so there's a lot of overhead in your code, and it breaks if the lists are of different lengths. As a first pass, I would do something logically equivalent like this. It will break on the assert for the same reason, but it should be more clear.

from itertools import izip
import traceback

def parse(self, response):
    hxs = HtmlXPathSelector(response)
    # get the data however you got it (not sure exactly what the goal is here)
    sites = hxs.select('//ul/li')
    items = [site.select('//h2').extract()]
    item = [site.select('//h3').extract()]
    item1 = [site.select('//meta').extract()]
    # open a connection to the server
    con = MySQLdb.connect(
               host="localhost",
               user="dreamriks",
               passwd="dreamriks",
               db="scraped_data",
          )
    cur = con.cursor()
    # run the commands:
    try:
        for st, st1, st2 in izip(items, item, item1):
             assert st and st1 and st2, 'one of the three values is missing. strings were: (%s, %s, %s)' % (st, st1, st2)
             cur.execute("Insert into heads(h2, h3, meta) Values(%s, %s, %s)" % (st, st1, st2))
        con.commit()
    except:
        #if there is a failure, print the error and die
        traceback.print_stack()
    finally:
        #no matter what happens, close the connection to the server
        con.close()