1

I want to insert a row in a table of a database from a python script. The column fiels values are saved in variables of different formats: strings, integers, and floats. I search in forums, I tried differente options but no one is working

I tried this options:

cursor.execute('INSERT INTO table(device, number1, number2) VALUES (%s,%d,%f)',(device_var,number1_var, number2_var))

I also tried:

 cursor.execute('INSERT INTO table(device, number1, number2) VALUES ({0},{1},{2})',(device_var,number1_var, number2_var))

And

 cursor.execute('INSERT INTO table(device, number1, number2) VALUES ({0},{1},{2})'.format (device_var,number1_var, number2_var))

ERROR:OperationalError: (1054, "Unknown column 'device_var_content' in 'field list'")

I aslo tried this to see if there is a problem in the table but this works OK:

cursor.execute('INSERT INTO table(device, number1, number2) VALUES ("dev1",1,2.4)'

Thanks for your time

SOLVED:

 cursor.execute('INSERT INTO table(device, number1, number2) VALUES ("{}",{},{})'.format (string_var,number1_var, number2_var))

Thanks for your help, your answers give me the way where keep looking.

user3278790
  • 75
  • 1
  • 3
  • 9
  • Possible duplicate of [How can I insert data into a MySQL database?](https://stackoverflow.com/questions/5687718/how-can-i-insert-data-into-a-mysql-database) – Van Peer Oct 24 '17 at 11:16

4 Answers4

3

you can use parameter binding. you don't need to worry about the datatypes of the variables that being passed. 

cursor.execute('INSERT INTO table(device, number1, number2) VALUES (?, ?, ?)', ("dev1",1,2.4))
Anbarasan
  • 1,197
  • 13
  • 18
0

Check if you're calling commit() post execution of query or also you can enable autocommit for connection. 

connection_obj.commit()
Mahesh Karia
  • 2,045
  • 1
  • 12
  • 23
0

The column fiels values are saved in variables of different formats: strings, integers, and floats. I search in forums, I tried differente options but no one is working

Have you read the db-api specs ? The placeholder in the query are not python formatting instructions but just plain placeholders - the db connector will take care of type conversion, escaping, and even a bit of sanitization. FWIW it's a pity the MySQLdb authors choosed the python string format placeholder (%s) as it causes a lot of confusion... This would be clearer if they had choosed something like "?" instead.

Anyway - the "right way" to write your query is:

cursor.execute(
  'INSERT INTO table(device, number1, number2) VALUES (%s,%s,%s)',
   (device_var,number1_var, number2_var)
   )

And of course do no forget to commit your transaction at some point...

bruno desthuilliers
  • 75,974
  • 6
  • 88
  • 118
-1

Firstly,

cursor.execute('INSERT INTO table(device, number1, number2) VALUES (%s,%d,%f)',(device_var,number1_var, number2_var))

seems wrong. Similarly second version is wrong too. Third with .format is correct.

Then why did query not run? Because of the reason below:

Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you'll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.

Conversely, you can also use connection.rollback() to throw away any changes you've made since the last commit.

Commit before closing your connection.

  1. db=mysql.connect(user="root",passwd="",db="some_db",unix_socket="/opt/lampp/var/mysql/mysql.sock")
  2. cursor=db.cursor()
  3. cursor.execute("insert into my table ..")
  4. db.commit()
imox
  • 1,544
  • 12
  • 12
  • 2
    __Do NOT use string formatting for SQL queries__. If you don't know why : http://bobby-tables.com/ – bruno desthuilliers Oct 24 '17 at 11:52
  • Thank you for bringing it into my notice, again. I know it and never use it. I now realize that i shouldn't have given that advice here. I wrote that from a different point of view and totally overlooked the fact that it was not only a string formatting question but also a database query. – imox Oct 24 '17 at 12:00
  • I'm using commit with .format, but the error I'm having is:OperationalError: (1054, "Unknown column '3816F3' in 'field list'"), where '3816F3' is devID_var – user3278790 Oct 24 '17 at 12:01
  • @user3278790 the code you posted cannot generate such an error. No one can help if the code you post is not the one causing the problem you get. – bruno desthuilliers Oct 24 '17 at 12:08