CODEWITHSUNDEEP
pythonsqldatabaseconnection
jitesh2796
jitesh2796

Reputation: 304

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

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?

Upvotes: 2

Views: 341

Answers (2)

lyq
lyq

Reputation: 98

# 导入SQLite驱动:
import sqlite3

# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('samples/sample.db')
# 创建一个Cursor:
cursor = conn.cursor()
if __name__ != '__main__':
    # 执行一条SQL语句，创建user表:
    cursor.execute('CREATE TABLE user(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20))')
    # 继续执行一条SQL语句，插入一条记录:
    cursor.execute("INSERT INTO user(id,name) VALUES ('1','Michael')")
    # 通过rowcount获得插入的行数:
    print(cursor.rowcount)
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()

remeber conn.commit().

Upvotes: 0

Shan Chathusanda Jayathilaka
Shan Chathusanda Jayathilaka

Reputation: 426

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.

Upvotes: 1

Related Questions