Newbie problem inserting into MySQL

[len]

Hi All

I have started a little pet project to learn python and MySQL. The
project involves figuring out all the combinations for a 5 number
lottery and storing the data in a MySQL file.

The file looks like this;
+----------+---------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------+---------------------+------+-----+---------
+----------------+
| lottryid | int(11) | NO | PRI | NULL |
auto_increment |
| lottryno | char(10) | YES | | NULL
| |
| no1 | tinyint(3) unsigned | NO | | NULL
| |
| no2 | tinyint(3) unsigned | NO | | NULL
| |
| no3 | tinyint(3) unsigned | NO | | NULL
| |
| no4 | tinyint(3) unsigned | NO | | NULL
| |
| no5 | tinyint(3) unsigned | NO | | NULL
| |
| nosum | tinyint(3) unsigned | NO | | NULL
| |
| nohits | int(10) unsigned | YES | | NULL
| |
+----------+---------------------+------+-----+---------
+----------------+

The code looks as follows;
#!/usr/lib/env python

import MySQLdb
import datetime

db = MySQLdb.Connection(host="localhost", user="lenyel",
passwd="lsumnler", \
db="lottery")

cursor = db.cursor()

cursor.execute('delete from littlelottery')

listofrec = []

tupcnt = 0
print "first tuple created"
for a in xrange(1,36):
for b in xrange(2,37):
for c in xrange(3,38):
for d in xrange(4,39):
for e in xrange(5,40):
tupcnt += 1
thekey = ('%02i%02i%02i%02i%02i' % (a,b,c,d,e))
mysum = a + b + c + d + e
rectuple = tupcnt, thekey, a, b, c, d, e, mysum, 0
listofrec.append(rectuple)
if tupcnt % 10000 == 0:
print "beginnign of mysql write"
print datetime.datetime.now().time()
cursor.executemany('''insert into
littlelottery
values (?,?,?,?,?,?,?,?,?)''', listofrec)
db.close()
print "end of mysql write"
print datetime.datetime.now().time()
os._exit()

print "insert into mysql completed"

i get the following error on insert;
raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting
Script terminated.

Do I have to covert all of the fields in the tuple records to string
or what?

Len Sumnler

 

[John Machin]

| lottryid | int(11) | NO | PRI | NULL |
auto_increment |
tupcnt += 1
rectuple = tupcnt, thekey, a, b, c, d, e, mysum, 0
listofrec.append(rectuple)
cursor.executemany('''insert into
littlelottery
values (?,?,?,?,?,?,?,?,?)''', listofrec)

i get the following error on insert;
raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting

{caveat: I'm not a MySQL user]

Could this be caused by trying to insert a value for an auto_increment
column using the form of insert where you don't specify column names?
IOW, your tuple has one more item than it is expecting ...

In any case, why have an auto_increment column but then increment it
yourself?

HTH,
John

 

[Peter Otten]

I have started a little pet project to learn python and MySQL. The
project involves figuring out all the combinations for a 5 number
lottery and storing the data in a MySQL file.

import MySQLdb

cursor.executemany('''insert into
littlelottery
values (?,?,?,?,?,?,?,?,?)''', listofrec)

i get the following error on insert;
raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting
Script terminated.

Do I have to covert all of the fields in the tuple records to string
or what?

'format'

So it looks like you need to replace the '?' in your SQL statement
with '%s'.

Peter

 

[John Nagle]

I have started a little pet project to learn python and MySQL. The
project involves figuring out all the combinations for a 5 number
lottery and storing the data in a MySQL file.

1. As someone else mentioned, the placeholder for MySQL data
is "%s", not "?".
2. After inserting, you must call "db.commit()", or, when the
program exits, all the insertions will be backed out.
(Assuming you're using a table type that supports
transactions, like InnoDB. But commit anyway.)
3. If you're inserting a huge number of records, look into
LOAD DATA. It's much faster.
4. Your code will make 10000 entries, then exit. Is that
what you want?
5. Creating a database of computed values is a useful exercise,
but not all that useful.

John Nagle