CODEWITHSUNDEEP
pythonmysqlinsert
user3278790
user3278790

Reputation: 75

Python MySQL INSERT INTO table how to pass values from variables of different formats

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.

Upvotes: 1

Views: 8339

Answers (4)

imox
imox

Reputation: 1554

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

Upvotes: -1

bruno desthuilliers
bruno desthuilliers

Reputation: 77912

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

Upvotes: 0

Mahesh Karia
Mahesh Karia

Reputation: 2055

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

connection_obj.commit()

Upvotes: 0

Anbarasan
Anbarasan

Reputation: 1207

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

Upvotes: 3

Related Questions