[FIXED] How to insert values directly into SQL base table using Python?

Issue

I am trying to add values in a SQL table by taking user input by building connection through python.

I have tried building connection with SQL database and adding values in the table, but the values are added only temporarily in python and the values doesn't get inserted in the SQL table

I tried the following:

database ='c:\\sqlite\db\chinook.db'

conn =sqlite3.connect(database)
cur =conn.cursor()

sql ="Insert into books(id,title,author,genre,owner) values (?,?,?,?,?) "

value1 =(2,'Outliers','Malcom Gladwell','Non-Fiction','Ishant Sahu')
cur.execute(sql,value1)

sql2 ="Select * from books; "
db = pd.read_sql(sql2,conn)

I can see the values inserted in db:

id  title   author  genre   owner
1   Shoe Dog    Phil Knight Memoir  Jitesh Singla
2   Outliers    Malcom Gladwell Non-Fiction Ishant Sahu

But When I'm running the table on SQL server, there is no change:

1   Shoe Dog    Phil Knight Memoir  Jitesh Singla

Is there any way to do this and if not why is this not possible?

Solution

In python database connections, default the auto commit is false. So you need to commit the query in order to save the data to the database.

Answered By - Shan Chathusanda Jayathilaka

Answer Checked By - Mary Flores (PHPFixing Volunteer)