[Tutor] DateTime and MySQLdb

[Magnus Lyckå]

At 15:30 2003-05-16 -0700, Michael Montagne wrote:
>How do I insert a DateTime object into a datetime column in a mysql
>table using mysqldb.

Are you talking about mx.DateTime objects? That should work just
the same as most other objects. You use %s in the SQL code and
put the DateTime object in the parameter list.

>I have this to start but I know it won't work.  odt is a DateTime object.
>
>cursor.execute("INSERT INTO readings 
>(readingdate,systolic,diastolic,pulse,comments) VALUES 
>(odt,%d,%d,%d,%s)",(systolic,diastolic,pulse,comments))

No, that wouldn't work. There are two problems (apart from
the very long line)... You need to do:

cursor.execute("""INSERT INTO readings
   (readingdate, systolic, diastolic, pulse, comments)
   VALUES (%s, %s, %s, %s, %s)""",
   (odt, systolic, diastolic, pulse, comments))

(You don't need to break the lines like I did of course,
but such long lines as you use makes the code difficult
to read.)

I.e. you must pass in odt in the same way as the
other parameters, and for MySQLdb I think you need
to use %s for all parameters, never %d etc.

That %s is needed instead of %d is due to a misunder-
standing of the DB-API spec, which wasn't perfectly clear.
There is currently a discussion about this on the db-sig
mailing list. Not only MySQLdb, but also a few other DB
interfaces have the same limitation.

As you might notice, parameter passing in MySQLdb is
very similar to using the normal python %-operator,
and in fact it's implemented using that.

For instance, having ...

 >>> a = "Good"
 >>> b = "Morning"
 >>> c = "Sir"

Consider the difference between...

 >>> print "a %s, %s!" % (b, c)
a Morning, Sir!

... and ...

 >>> print "%s %s, %s!" % (a, b, c)
Good Morning, Sir!

The only text inside the SQL-string that is interpreted in
some way is the %s (or possibly other % format codes).
Writing "...VALUES (odt,..." in the SQL code means that
you send the text "odt" instead of a date, and without
quoting by the way. That's a syntax error for MySQLdb.

But unless you have a completely different MySQLdb module
that I have, you will get: "TypeError: an integer is required"
before you get near the MySQL engine because they numeric
values are cast to strings in the db interface code before
they are passed into the SQL string. So first of all: change
all %d to %s.

Note that "cursor.execute(sql_string, (a,b,c))" is NOT the
same as "cursor.execute(sql_string % (a,b,c))" though. Use
comma, not percent. The main difference is that strings will
be escaped and quoted by the execute method if you pass them
in correctly. I.e "can't" will turn into "'can''t'" which is
what SQL wants. The downside is that you have to type %s for
all parameters, regardless of type, at least for MySQLdb,
sqlite and one of the PostgreSQL drivers, but as long as you
just remember that, things should work. (Well, *this* should
work...there's always another bug lurking around the corner. :)



--
Magnus Lycka (It's really Lyckå), magnus@thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program