How to build mysql query string in more beautiful way in python?

Question

Here is how I do this now:

sql_q = """INSERT INTO measurements(time, voltage, current, frequency, powerActive, powerReactive, powerApparent, powerFactor, phaseAngle, totalFAE, totalRAE, totalFRE, totalRRE, phase)
        VALUES ('%s', %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f)""" % (now, j['phases'][0]['voltage'], j['phases'][0]['current'], j['phases'][0]['frequency'], j['phases'][0]['powerActive'], j['phases'][0]['powerReactive'], j['phases'][0]['powerApparent'], j['phases'][0]['powerFactor'], j['phases'][0]['phaseAngle'], j['phases'][0]['totalForwardActiveEnergy'], j['phases'][0]['totalReverseActiveEnergy'], j['phases'][0]['totalForwardReactiveEnergy'], j['phases'][0]['totalReverseReactiveEnergy'], j['phases'][0]['number'])
sql_q += """, ('%s', %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f)""" % (now, j['phases'][1]['voltage'], j['phases'][1]['current'], j['phases'][1]['frequency'], j['phases'][1]['powerActive'], j['phases'][1]['powerReactive'], j['phases'][1]['powerApparent'], j['phases'][1]['powerFactor'], j['phases'][1]['phaseAngle'], j['phases'][1]['totalForwardActiveEnergy'], j['phases'][1]['totalReverseActiveEnergy'], j['phases'][1]['totalForwardReactiveEnergy'], j['phases'][1]['totalReverseReactiveEnergy'], j['phases'][1]['number'])
sql_q += """, ('%s', %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f)""" % (now, j['phases'][2]['voltage'], j['phases'][2]['current'], j['phases'][2]['frequency'], j['phases'][2]['powerActive'], j['phases'][2]['powerReactive'], j['phases'][2]['powerApparent'], j['phases'][2]['powerFactor'], j['phases'][2]['phaseAngle'], j['phases'][2]['totalForwardActiveEnergy'], j['phases'][2]['totalReverseActiveEnergy'], j['phases'][2]['totalForwardReactiveEnergy'], j['phases'][2]['totalReverseReactiveEnergy'], j['phases'][2]['number'])

this table have one more column id which is auto increment int. Is there a way to do this in better/smarter way?

Write your data into a file and then load data from file

lllrnr101
– lllrnr101

2021-01-31 10:37:46 +00:00
Commented Jan 31, 2021 at 10:37 — lllrnr101
– lllrnr101, Commented Jan 31, 2021 at 10:37

deceze · Accepted Answer · 2021-01-31 10:41:30Z

5

You are open to SQL injection, you should use parameterised queries to begin with instead of string interpolation. Further, the Python MySQL connector (should that be what you're using) supports executemany for cases like this. Your final code should look something like:

stmt = 'INSERT INTO measurements (time, voltage, current, ...) '
       'VALUES (%(time)s, %(voltage)s, %(current)s, ...)'

# Create a list of dicts, each containing all keys for %(...)s placeholders above
values = [{'time': now, **vals} for vals in j['phases']]

cursor.executemany(stmt, values)

You may also be able to skip the use of now entirely. If your database column is a DATETIME type or similar, you can use ... VALUES(NOW, ..., and MySQL will insert the current time. You can even set that as the DEFAULT value for that column and skip it entirely during an INSERT. In that case, you don't even need to change those dicts you already have, and just do:

stmt = 'INSERT INTO measurements (time, voltage, current, ...) '
       'VALUES (NOW, %(voltage)s, %(current)s, ...)'

cursor.executemany(stmt, j['phases'])

edited Jan 31, 2021 at 10:41

answered Jan 31, 2021 at 10:32

deceze♦

525k89 gold badges806 silver badges954 bronze badges

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

2 Comments

J.Doe Over a year ago

using DATETIME from MySQL will give me different time for three records which must always have exactly the same time. I have 3 sets of values every measurements and each of it need to have exatc same time in MySQL for future MySQL queries. executemany will add it one by one not all 3 at once, am I right?

deceze Over a year ago

Read the last few paragraphs on dev.mysql.com/doc/connector-python/en/….

Collectives™ on Stack Overflow

How to build mysql query string in more beautiful way in python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related