Database query as variable in Python using MySQLdb library

Question

I want to save all data in dictionary (or any thing else which will work) and INSERT to mysql database all variables added to my variable. The problem is that, I don't know how many variables I will have.

It will look like that:

cur.execute("INSERT INTO db_name(keys) VALUES (dict[key])")

I have a lot of things in my program, some of them are boolean variables (True, False) and some are integers, others are strings. As default, I can add them like that:

cur.execute(""" INSERT INTO db_name(a, b, c, d) VALUES ('%s', '%s', %s, %s) """, ('abc', 123, True, 'NULL')

As you can see, for string and integer '%s' and for boolean, NULL it must be %s without ''

Reason for this is that, if variable not found, it must be NULL in database, but I don't know how to add string and integer variables as NULL because it uses '%s' which inserts them as string.

Something like that: (its not python code, its just syntaxis idea)

cur.execute("INSERT INTO db_name( data.keys() ) VALUES ( data.values() ) ")

@GrijeshChauhan I need to generate mysql query using variables. But there are strings, integers and Booleans. — Emin Mastizada
– Emin Mastizada, Commented Mar 4, 2014 at 14:05
and any thing can be null? is choosing format string is your problem? — Grijesh Chauhan
– Grijesh Chauhan, Commented Mar 4, 2014 at 14:05
@GrijeshChauhan if i will generate query using variables that i have, all other columns in database will be NULL automatically. But If I cannot generate query I need to add empty variables as NULL — Emin Mastizada
– Emin Mastizada, Commented Mar 4, 2014 at 14:07
your problem is if string is null then because of '%s' if you supply 'NULL' then query will insert 'NULL' instead of NULL. Am I correct? — Grijesh Chauhan
– Grijesh Chauhan, Commented Mar 4, 2014 at 14:07
@GrijeshChauhan yes, but it would be great to generate query using variables that I have. So, some of variables will not be in the query and in database they will be added as NULL — Emin Mastizada
– Emin Mastizada, Commented Mar 4, 2014 at 14:09

Emin Mastizada · Accepted Answer · 2014-03-04 15:05:18Z

1

Thanks everyone for help, problem solved using format function:

query = "INSERT INTO shData("
query_v = "VALUES ("
for key in q_i:
    query += key + ','
    if isinstance(q_i[key],str):
        query_v += "'{" + key + "}',"
    else:
        query_v += "{" + key + "},"
query = query.rstrip(",")
query_v = query_v.rstrip(",")
query = query + ") " + query_v + ") "
cur.execute(query.format(**q_i))

answered Mar 4, 2014 at 15:05

Emin Mastizada

1,4052 gold badges17 silver badges31 bronze badges

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

Comments

DhruvPathak · Accepted Answer · 2014-03-04 14:39:09Z

0

Thats because you are using %s placeholder in quotes, and also passing a string NULL , and not a null value (None in python)

Corrected version would be :

    cur.execute(""" INSERT INTO db_name(a, b, c, d)
    VALUES
    (%s, %s, %s, %s) """, ('abc', 123, True, None)   
 # no quotes on %s , and None instead of 'NULL'

See documentation here

answered Mar 4, 2014 at 14:39

DhruvPathak

43.4k17 gold badges125 silver badges180 bronze badges

1 Comment

Emin Mastizada Over a year ago

Already tested %s but it give error for strings like "abc abc". I know about None, but for %s without '' 'NULL' worked too.

Collectives™ on Stack Overflow

Database query as variable in Python using MySQLdb library

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related