Python-MySQLdb fails to insert values, but running SQL printed to console inserts data?

Question

I wrote the code below to generate some test data. The script fails to insert the data into the db. However printing the generated SQL to the console and copy pasting that into mysql inserts the records, why would this be?

First, here is the SQLfiddle http://sqlfiddle.com/#!9/cf3bfe/1

And below is the script:

import MySQLdb
from datetime import *
import random

db1=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")
db2=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")

get_participant=db1.cursor()
insert_test=db2.cursor()

dt = datetime.now()

for y in xrange(0,5):
    for x in xrange(0,440):
        random_dt = dt + timedelta(minutes=random.randrange(15, 30, 1))
        get_participant.execute('''SELECT COUNT(*) FROM Participant WHERE (InternalGroup IS NOT NULL) AND (idParticipant = ''' + str(x + 1) + ''');''')
        row = get_participant.fetchone()
        if row[0] != 0:
            sql2 = 'INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values (\'' + str(random_dt) + '\', \'' + str(x + 1) + '\');'
            insert_test.execute(sql2)
            print sql2

Below are some lines printed on the console by the print sql2 statement. Copy and paste these rows exactly as they are, inserts the data into the db

INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:09:46.308746', '243');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:09:46.308746', '244');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:10:46.308746', '245');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:03:46.308746', '255');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:04:46.308746', '266');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 16:58:46.308746', '272');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 16:57:46.308746', '286');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:10:46.308746', '289');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:03:46.308746', '294');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:02:46.308746', '333');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:04:46.308746', '363');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:03:46.308746', '372');
INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values ('2017-10-06 17:05:46.308746', '388');

EDIT 1:

I changed the code to this:

import MySQLdb
from datetime import *
import random

db1=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")
db2=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")

get_participant=db1.cursor()
insert_test=db2.cursor()

dt = datetime.now()

for y in xrange(0,5):
    for x in xrange(0,439):
        xn = x + 1
        random_dt = dt + timedelta(minutes=random.randrange(15, 30, 1))
        get_participant.execute('''SELECT COUNT(*) FROM Participant WHERE (InternalGroup IS NOT NULL) AND (idParticipant = %s)''', (xn,))
        row = get_participant.fetchone()
        if (row[0] != 0):
            insert_test.execute('''INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values (%s,%s)''', (random_dt, xn))

But it still does not insert the records.

EDIT 2:

Moving the cursors inside the loop makes no difference either:

db1=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")
db2=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")

dt = datetime.now()

for y in xrange(0,5):
    for x in xrange(0,439):
        get_participant = db1.cursor()
        insert_test = db2.cursor()
        xn = x + 1
        random_dt = dt + timedelta(minutes=random.randrange(15, 30, 1))
        get_participant.execute('''SELECT COUNT(*) FROM Participant WHERE (InternalGroup IS NOT NULL) AND (idParticipant = %s)''', (xn,))
        row = get_participant.fetchone()
        if (row[0] != 0):
            insert_test.execute('''INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values (%s,%s)''', (random_dt, xn))

EDIT 3:

Forgot to commit to the db, changed code to below and all is working correctly now:

import MySQLdb
from datetime import *
import random

db1=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")
db2=MySQLdb.connect(host="localhost", user="user", passwd="password", db="racedb_bu")

dt = datetime.now()

for y in xrange(0,5):
    for x in xrange(0,439):
        get_participant = db1.cursor()
        insert_test = db2.cursor()
        xn = x + 1
        random_dt = dt + timedelta(minutes=random.randrange(15, 30, 1))
        get_participant.execute('''SELECT COUNT(*) FROM Participant WHERE (InternalGroup IS NOT NULL) AND (idParticipant = %s)''', (xn,))
        row = get_participant.fetchone()
        if (row[0] != 0):
            insert_test.execute('''INSERT INTO racedb_bu.TimeEntry (DateTimeStamp, Participant) values (%s,%s)''', (random_dt, xn))
            db2.commit()

Python-MySQLdb fails to insert values, but running SQL printed to console inserts data?

Answers (1)

Related Questions