CODEWITHSUNDEEP
pythonmysqlpython-3.x
chink
chink

Reputation: 1643

create a dynamic insert query in python to save data in mysql db

I have a table in mysql and i am inserting data in it from a python client.

I am using a insert query to insert data into the table

code

sql_insert_query = """ INSERT INTO Data
                          (`deviceID`,`date`,`timestamp`,`counter`,`rssi`,
                            `CO2 Sensor Value`,
                            `Supply DPT`,
                            `block`,
                            `floor`) 
                             VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""

connection = mysql.connector.connect(host='localhost',database='minniedb',user='',
                                    password='',auth_plugin='mysql_native_password')
cursor = connection.cursor()

"""
 create 'insert_tuple' based on some api calls
"""
cursor.execute(sql_insert_query,insert_tuple)
connection.commit()
cursor.close()
connection.close()
print('inserted in db')

This works fine when everything is static. I have a case when the number of columns in my table is around 60-70 and the parameters I get from api is a subset of columns(around 10-15) and these parameters can change every time. The api returns the column name and the value. Sample return from api can be of form

{
    'deviceID':20,
    'counter' :61,
    'block'   :'A'

}

or it can be

{
   'deviceID'        :25,
   'CO2 Sensor Value':600,
   'floor'           : 5

}

How do i write a query in such case to insert whatever data i received from api in the respective columns and have others as null.

Upvotes: 0

Views: 1230

Answers (1)

DARK_C0D3R
DARK_C0D3R

Reputation: 2257

You can try like this :

sensor_data = {
    'deviceID':20,
    'counter' :61,
    'block'   :'A'

} 
sql_insert_query = """ INSERT INTO Data {} VALUES {}""".format(tuple(sensor_data.keys()), tuple(sensor_data.values()))

P.S: For sensor data I'll suggest using google Firebase :)

Upvotes: 1

Related Questions