Python variables in mysql request

Question

I'm quite new in Python (Python 3.4.6) :)
I'm trying to insert into a mysql db some lines but with variables.
At the beginning, I've a dictionary list_hosts.
Here is my code :

import mysql.connector
import time
db = mysql.connector.connect(host='localhost', user='xxxxx', passwd='xxxxx', database='xxxxx')
cursor = db.cursor()
now_db = time.strftime('%Y-%m-%d %H:%M:%S')
for key, value in list_hosts
    key_db += key+", "
    value_ex += "%s, "
    value_db += "\""+value+"\", "
key_db = key_db.strip(" ")                                                                                                                                                                                                                            
key_db = key_db.strip(",")
value_ex = value_ex.strip(" ")
value_ex = value_ex.strip(",")
value_db = value_db.strip(" ")
value_db = value_db.strip(",")
add_host = ("INSERT INTO nagios_hosts (date_created, date_modified, "+key_db+") VALUES ("+value_ex+")")
data_host = ("\""+now_db+"\", \""+now_db+"\", "+value_db)
cursor.execute(add_host, data_host)
db.commit()
db.close()

Example of list_hosts:

OrderedDict([('xxxx1', 'data1'), ('xxxx2', 'data2'), ('xxxx3', 'data3'), ('xxxx4', 'data4'), ('xxxx5', 'data5'), ('xxxx6', 'data6')])

I've simplified the code of course. I did it like this as I've never have the same amount of items in the dictionnary. I'm trying to create something like this :

add_host - INSERT INTO TABLE (date_created, date_modified, xxxx1, xxxx2, xxxx3, xxxx4, xxxx5, xxxx6) VALUES (%s, %s, %s, %s, %s, %s)
data_host - now, now, data1, data2, data3, data4, data5, data6

Where there are never the same number of xxxx... They all exist in the DB, but I don't need to fill each column for each item in the dictionnary.

When I execute I get this error :

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxxxxxxxxxxxxxxxxxxxx' at line 1

As I'm beginning with Python, I think there are a lot of things we can clean too... don't hesitate :)

You should be using query params. I'm sure what you're trying to do with value_ex and data_host here. — khelwood
– khelwood, Commented Aug 21, 2018 at 11:08
query params ? how ? I've never the same amount of items in the source dictionnary, so I don't know how many items I'm gonna insert in the DB. I didn't know how to do otherwise as I don't know how many %s I'm suppose to have in my query (value_ex). The data_host, it's just to add the datetime in the DB not provided by the dictionnary. — amans san
– amans san, Commented Aug 21, 2018 at 11:17
Never use any kind of string formatting on SQL queries, it will lead to SQL injection vulnerabilities. Use the method shown in the docs. — Klaus D.
– Klaus D., Commented Aug 21, 2018 at 11:21
I already read the doc. It's not helping me. I didn't find or didn't understand how I need to do in my situation. — amans san
– amans san, Commented Aug 21, 2018 at 11:24
Please post the definition (or and example of) list_hosts. — bruno desthuilliers
– bruno desthuilliers, Commented Aug 21, 2018 at 11:30

bruno desthuilliers · Accepted Answer · 2018-08-21 12:17:03Z

1

Here's a canonical python3 (python2 compatible) solution:

import time
from collections import OrderedDict

list_hosts = OrderedDict([("field1", "value1"), ("field2", "value2"), ("fieldN", "valueN")])

# builds a comma-separated string of db placeholders for the values:
placeholders = ", ".join(["%s"] * (len(list_hosts) + 2))

# builds a comma-separated string of field names     
fields =  ", ".join(("date_created","date_modified") + tuple(list_hosts.keys()))

# builds a tuple of values including the dates
now_db = time.strftime('%Y-%m-%d %H:%M:%S')
values = (now_db, now_db) + tuple(list_hosts.values())


# build the SQL query:
sql = "INSERT INTO nagio_hosts({}) VALUES({})".format(fields, placeholders)

# and safely execute it 
cursor.execute(sql, values)
db.commit()

answered Aug 21, 2018 at 12:17

bruno desthuilliers

78.3k6 gold badges103 silver badges129 bronze badges

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

Comments

Ildar Akhmetov · Accepted Answer · 2018-08-21 11:55:03Z

As @khelwood mentioned in the comments, you should use parameterized queries.

If the number of columns you're inserting varies, you might prefer to generate a tuple and use it in a parameterized query then.

cursor.execute() accepts two parameters:

a query as a string;
parameters as a tuple.

The idea is to generate the string and the tuple and pass those to cursor.execute().

You'll need something like this:

list_hosts = {'xxxx1': 'data1', 'xxxx2': 'data2', 'xxxx3': 'data3', 'xxxx4': 'data4'}

keys = [] # creating a list for keys
values = () # creating a tuple for values
for key, value in list_hosts.items():
    keys.append(key)
    values = values + (value,)

keys_str = ', '.join(keys)
ps = ', '.join(['%s'] * len(list_hosts))

query = "INSERT INTO tbl (%s) VALUES (%s)" % (keys_str, ps)

print(query)
# INSERT INTO tbl (data_created, data_modified, xxxx1, xxxx2, xxxx3, xxxx4) VALUES (%s, %s, %s, %s)

cursor.execute(query, values)

Just tried it on a sample data, works fine!

Collectives™ on Stack Overflow

Python variables in mysql request

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related