By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,190 Members | 1,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,190 IT Pros & Developers. It's quick & easy.

Newbie problem inserting into MySQL

P: n/a
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
Aug 18 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Aug 19, 1:54 am, len <lsumn...@gmail.comwrote:
| 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

Aug 18 '08 #2

P: n/a
len wrote:
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?
>>import MySQLdb
MySQLdb.paramstyle
'format'

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

Peter
Aug 18 '08 #3

P: n/a
len wrote:
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
Aug 19 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.