Cannot insert None using python and mysql in datetime field

Question

Consider the following code: The first query does not work while the second does. The problem is... since I need to compose a query beforehand via a function this function returns a variable as 'val'. So I need to get the first query to work. The second query is only added by example to show what happens when the content of 'val' is used literally. Please help! So, I want to insert a datetime but 'None' is also to be accepted. The current error is:

    query = query % db.literal(args)
TypeError: not enough arguments for format string

Now the script:

#!/usr/bin/python

import MySQLdb as mdb

def prepare():
# Assume a database named 'db_test'
    conn = mdb.connect(host   = 'localhost', \
                       user   = 'test',      \
                       passwd = 'test',      \
                       db     = 'db_test')

    cur = conn.cursor()

    cur.execute("DROP TABLE IF EXISTS TBL_TEST")
    cur.execute("CREATE TABLE TBL_TEST(Moo1 DATETIME, Moo2 DATETIME)")

    return cur

if __name__ == '__main__':
    qry = "INSERT INTO TBL_TEST (Moo1, Moo2) VALUES (%s, %s)"
    val = "('2012-04-03 08:40:39', None)"

    cur = prepare()
    cur.execute(qry, val) # NOK
    cur.execute("INSERT INTO TBL_TEST (Moo1, Moo2) VALUES (%s, %s)", ('2012-04-03 08:40:39', None)) # OK

Trust me - I searched the world but without any success.. Thanks in advance!

Pavel Anossov · Accepted Answer · 2013-01-28 15:47:35Z

3

Your val is a string, it should be a tuple:

val = ('2012-04-03 08:40:39', None)

answered Jan 28, 2013 at 15:47

Pavel Anossov

63.3k16 gold badges156 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Szjoin Over a year ago

This worked for the example. The 'real' code is as follows (just a small part): val = (parseRow(cursor1),) This makes val a tuple (checked with 'print type(val)') cursor2.execute(qry, val) gives: query = query % db.literal(args) TypeError: not enough arguments for format string What am I doing wrong here?

Pavel Anossov Over a year ago

Does parseRow return a single value? Then you want val = (parseRow(cursor1), None).

Szjoin Over a year ago

Parserow returns a string type which is dynamically composed. Therefore, I cannot put 'None' behind it. I tried 'val = (parseRow(cursor1),)' to enforce a tuple but with no luck

Szjoin Over a year ago

More information: def parseRow(cur): values = [] for field in cur.fetchone(): if (field): values.append(field.strftime('%Y-%m-%d %H:%M:%S')) else: values.append(str(field)) return values 'print var' leads to: ['2012-04-03 08:40:39', 'None'] How can I get parseRow to return something I can execute() ?

Pavel Anossov Over a year ago

To convert it to tuple, call tuple: val = tuple(parseRow(cursor1))

Collectives™ on Stack Overflow

Cannot insert None using python and mysql in datetime field

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related