CODEWITHSUNDEEP
pythonmysqlmysql-python
Emin Mastizada
Emin Mastizada

Reputation: 1405

Database query as variable in Python using MySQLdb library

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() ) ")

Upvotes: 0

Views: 321

Answers (2)

Emin Mastizada
Emin Mastizada

Reputation: 1405

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))

Upvotes: 1

DhruvPathak
DhruvPathak

Reputation: 43245

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

Upvotes: 0

Related Questions