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
– Reza-S4, Commented Dec 15, 2014 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
– Victor, Commented Dec 16, 2014 at 0: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
– Reza-S4, Commented Dec 16, 2014 at 7:50

Community · Accepted Answer · 2017-05-23 11:43:36Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered Dec 15, 2014 at 23:32

Reza-S4

1,0421 gold badge18 silver badges37 bronze badges

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

2 Comments

Zaaferani Over a year ago

this answer is correct but i rather than use ORM such as peewee peewee.readthedocs.org

Reza-S4 Over a year ago

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.

Collectives™ on Stack Overflow

Python insert data into MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related