Unable to insert any data to the database using python

Question

import sqlite3

def create_a_new_table():
    Myschool = sqlite3.connect('normal database for experiment purpose.db')
    curschool = Myschool.cursor()
    curschool.execute("""
                           CREATE TABLE new_Basic_Player_Info
                           (
                              Ranking INTEGER,
                              Player_name TEXT,

                              Country TEXT,
                              Speciality TEXT,
                              Value INTEGER,
                              Cost INTEGER
                            );
                      """)
    Myschool.close()


def insert_data():
    Myschool = sqlite3.connect('normal database for experiment for purpose.db')
    curschool = Myschool.cursor()
   # nm = input("Enter the name of the player: ")
    sql = """INSERT INTO TABLE new_Basic_Player_Info(Ranking, Player_name) 
        VALUES(%s, %s);"""

May be %s, %s is the problem. Or, sql named string should be ended with a semi-colon(;) The main code that is not being executed

try:
    curschool.execute(sql, (1, "aNIKET GHOSH"))
    Myschool.commit()
    Myschool.close()

except:
    Myschool.rollback()

even I have used try and except keywords too.

create_a_new_table()
insert_data()

Stop catching and hiding all the errors and always rolling back. Remove that try/except and let Python tell you what is going wrong. — Daniel Roseman
– Daniel Roseman, Commented Jun 23, 2018 at 20:57
I struggled with similar errors/issues when grappling with pymysql. I realized it is best to use Flask's ORM implementation, Flask-SQLAlchemy as it abstracts any differences between different SQL variants and has well-supported documentation. — amanb
– amanb, Commented Jun 23, 2018 at 22:30

Mattias · Accepted Answer · 2018-06-23 22:32:11Z

1

I tested your code. I found three issues.

One issue which is that you are using two different sqlite files (missing a "for" in the first one). A good trick is to always think about DRY (Don't Repeat Yourself).

Two issues with your insert statement:

INSERT INTO TABLE ... whereas the correct syntax is INSERT INTO ...
You are using %s instead of the correct syntax ?

I.e. replace

sql = """INSERT INTO TABLE new_Basic_Player_Info(Ranking, Player_name) VALUES(%s, %s);"""

with

sql = """INSERT INTO new_Basic_Player_Info(Ranking, Player_name) VALUES(?, ?);"""

and you will be on track again.

answered Jun 23, 2018 at 22:32

Mattias

1607 bronze badges

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

1 Comment

A K Over a year ago

Thanks, It's working fine for me. Those two was the major errors into the code.

Collectives™ on Stack Overflow

Unable to insert any data to the database using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related