Python mysql database insert query, thread error

Question

This is my code that makes the call (everything works great except for the last line with the insert) all the required imports are there and working. There must be something wrong with the query.

db = Database()
soup = bs(mytrades)

for row in soup.findAll("tr"):
    cols = row.findAll("td")
    data = []
    for col in cols:
        data.append(col.text)

        query = """INSERT INTO zulutrades VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s), (128391,"""+data[0]+""","""+data[1]+""","""+data[2]+""","""+data[3]+""","""+data[4]+""","""+data[5]+""","""+data[6]+""","""+data[7]+""","""+data[8]+""","""+data[9]+""","""+data[10]+""")"""       
        db.insert(query)

*The "error" (I didn't post it because I didn't think it means much) *

Exception in thread Thread-192 (most likely raised during interpreter shutdown):Exception in thread Thread-2 (most likely raised during interpreter shutdown):
Traceback (most recent call last):

File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner
File "/usr/lib/python2.7/threading.py", line 763, in run
File "/usr/local/lib/python2.7/dist-packages/windmill-1.6-py2.7.egg/windmill/server/https.py", line 401, in start
File "/usr/lib/python2.7/SocketServer.py", line 280, in handle_request
File "/usr/lib/python2.7/SocketServer.py", line 291, in _handle_request_noblock
<type 'exceptions.AttributeError'>: 'NoneType' object has no attribute 'error'
Traceback (most recent call last):
File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner
File "/usr/lib/python2.7/threading.py", line 763, in run
File "/usr/lib/python2.7/SocketServer.py", line 597, in process_request_thread
File "/usr/lib/python2.7/SocketServer.py", line 471, in shutdown_request
<type 'exceptions.AttributeError'>: 'NoneType' object has no attribute 'error'

I'm using the following mysql Database class:

class Database:

    host = 'localhost'
    user = 'wind'
    password = 'mill'
    db = 'windmill'

    def __init__(self):
        self.connection = MySQLdb.connect(self.host, self.user, self.password, self.db)
        self.cursor = self.connection.cursor()

    def insert(self, query):
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except:
            self.connection.rollback()



    def query(self, query):
        cursor = self.connection.cursor( MySQLdb.cursors.DictCursor )
        cursor.execute(query)

        return cursor.fetchall()

    def __del__(self):
        self.connection.close()

Here's the mysql table

CREATE TABLE IF NOT EXISTS `zulutrades` (
`id` int(10) NOT NULL,
`currency` varchar(8) NOT NULL,
`type` varchar(8) NOT NULL,
`std_lots` int(8) NOT NULL,
`date_open` varchar(20) NOT NULL,
`date_closed` varchar(20) NOT NULL,
`open_close` varchar(20) NOT NULL,
`high` float NOT NULL,
`low` float NOT NULL,
`roll` float NOT NULL,
`profit` varchar(10) NOT NULL,
`total` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

query = """INSERT INTO zulutrades VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s), (128391,"""+data[0]+""","""+data[1]+""","""+data[2]+""","""+data[3]+""","""+data[4]+""","""+data[5]+""","""+data[6]+""","""+data[7]+""","""+data[8]+""","""+data[9]+""","""+data[10]+""")"""       

Here, you're building an INSERT query for two rows (note the two parenthesized groups after the VALUES clause); the first row consists only of 12 placeholders, while the second consists of 12 values directly concatenated within the query string. This query will not succeed, because you never provide values for the placeholders in your cursor.execute(query) call inside your Database.query method.

Your Database.query and Database.insert methods need to be rewritten in order to support passing of query parameters to the cursor.execute method:

class Database:
    ...

    def insert(self, query, params):
        try:
            self.cursor.execute(query, params)
            self.connection.commit()
        except:
            self.connection.rollback()

    def query(self, query, params):
        cursor = self.connection.cursor( MySQLdb.cursors.DictCursor )
        cursor.execute(query, params)

        return cursor.fetchall()

However, your loops are also wrong. You're executing an INSERT query for each cell returned by rows.findAll('td'), not for each row returned by soup.findAll('tr'). The INSERT should happen in the outer loop, not the inner:

query = 'INSERT INTO zulutrades VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
for row in soup.findAll("tr"):
    cols = row.findAll("td")
    data = []
    for col in cols:
        data.append(col.text)

    db.insert(query, [128391] + data)

As you see, the query string itself no longer needs to be defined inside the loop body, as it no longer changes for each row you want to insert — only the parameters for the execution change now, but those are supplied as a separate parameter to db.insert.