how to insert form data into MYSQL using python

Question

i am trying to insert the data entered into the web form into database table,i am passing the data to the function to insert the data,but it was not sucessful below is my code

def addnew_to_database(tid,pid,usid,address,status,phno,email,ord_date,del_date):
    connection = mysql.connector.connect(user='admin_operations', password='raghu',host='127.0.0.1',database='tracking_system')
    try:
        print tid,pid,usid,address,status,phno,email,ord_date,del_date
        cursor = connection.cursor()
        cursor.execute("insert into track_table (tid,pid,usid,address,status,phno,email,ord_date,del_date) values(tid,pid,usid,address,status,phno,email,ord_date,del_date)")
        cursor.execute("insert into user_table (tid,usid) values(tid,usid)")
    finally:
        connection.close()

hiro protagonist · Accepted Answer · 2015-07-31 20:49:20Z

1

you need to commit your statements:

connection.commit()

and you need to add data to your insert statement:

cursor.execute('''insert into user_table (tid, usid) 
                  values ({}, {})'''.format(tid, usid))

(not tested)

UPDATE

i made a baby-example (and tested it):

database setup:

$ mysql -u root
mysql> CREATE USER 'test_user' IDENTIFIED BY 'test_pass';
mysql> CREATE DATABASE test_db;
mysql> CREATE TABLE test_db.test_table (number INTEGER(16), string CHAR(16));
mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON test_db.* TO 'test_user'@'%';
mysql> FLUSH PRIVILEGES;

# test:
$ mysql -u test_user -D test_db -p

then this python code inserts data (very basic version; no exception handling, no transaction handling):

import mysql.connector as my

connection = my.connect(user='test_user', password='test_pass',
                        host='127.0.0.1', database='test_db')

cursor = connection.cursor()
cursor.execute('''INSERT INTO test_table(number, string) VALUES({}, '{}')
               '''.format(43, 'hello world'))
connection.commit()    

cursor.execute("SELECT * FROM test_table")
print(cursor.fetchall())
connection.close()

note the quotes around '{}' where .format enters a string value.

this uses the following versions:

python3                 3.4.0-0ubuntu2
python3-mysql.connector 1.1.6-1
mysql                   5.5.44-0ubuntu0.14.04.1

edited Jul 31, 2015 at 20:49

answered Jul 31, 2015 at 11:49

hiro protagonist

47.3k17 gold badges93 silver badges119 bronze badges

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

10 Comments

learner Over a year ago

i had used connection.close()

hiro protagonist Over a year ago

if you did not set autocommit to True, this will not automatically commit. you will have to call connection.commit() before closing the connection. did you try it?

learner Over a year ago

now i had tried it but row is set but data was not inserted in the table

hiro protagonist Over a year ago

oh, you do not provide any data to your statemtents... i will update the answer. hang on...

hiro protagonist Over a year ago

then please update your question; post the bare minimum of code that you have now and that reproduces your problem. does python print a traceback? or is there just no data in the database?

|

Collectives™ on Stack Overflow

how to insert form data into MYSQL using python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related