Python insert data into MySQL

Question

I've encounter a problem when i try to insert values into mysql using python connector. The problem is that i'm trying to pass an input as a value in mysql, but the input is added as name of the table instead of value of field. Can anyone let me now what am i doing wrong?

My code is:

import mysql.connector
from mysql.connector import errorcode
def main():
    try:
        connection= mysql.connector.connect(user='root',passwd='',host='localhost',port='3306', database='game_01')
        print("Welcome")
        name_of_char = input("Your name?: ")
        con = connection.cursor()
        con.execute("INSERT into charachter (name,intel,strenght,agil) values(%s,0,0,0)" % str(name_of_char))
        con.execute("SELECT * FROM charachter")
        for items in con:
            print(items[1])
    except mysql.connector.Error as err: 
        print(err)
    else:
         connection.close()
main()

Thanks.

P.S The error is : 1054: Unknown column in 'field list'. I forgot to mention that in the post. It seems if i enter the tables attribute,it will work but won't add any value.

you can't save anything to table? what is the exception error ? — Reza-S4, Dec 15 '14 at 23:27
The error is : 1054: Unknown column in 'field list'. I forgot to mention that in the post. It seems if i enter the tables attribute,it will work but won't add any value. — Victor, Dec 16 '14 at 00:04
make sure you have `name`,`intel`,`strenght`,`agil` in your table. The error occurs when you do not have a column in the table. — Reza-S4, Dec 16 '14 at 07:50

score 3 · Answer 1 · edited May 23 '17 at 11:43

3

if you using MySQLdb driver , after execute the query that insert into database or update , you should use connection.commit() to complete saving operation.

try this:

con = connection.cursor()
con.execute("INSERT into `charachter` (`name`,`intel,`strenght`,`agil`) values('%s',0,0,0)" % str(name_of_char))
connection.commit()

if you use any other driver , you should set the auto_commit option true.

see this: How can I insert data into a MySQL database?

edited May 23 '17 at 11:43

Community

1
1

answered Dec 15 '14 at 23:32

Reza-S4

1,042
1
18
36

1

this answer is correct but i rather than use ORM such as peewee http://peewee.readthedocs.org – Zaaferani Apr 20 '15 at 07:08
1

ORMs are the best for most mvc frameworks db connection. But for tiny scripts maybe the raw query down the job quickly. But I prefer to use orms everywhere! and perhaps someone didn't like to use it. – Reza-S4 Apr 20 '15 at 21:02

Python insert data into MySQL

1 Answers1