sqlite3 with python(顺带解决中文插入的问题) |
您所在的位置：网站首页 › python3支持中文 › sqlite3 with python(顺带解决中文插入的问题) |
sqlite3 with python(顺带解决中文插入的问题)
|
1、链接数据库
import sqlite3
conn = sqlite3.connect('python_test.db')
c = conn.cursor()
print ("Opened database successfully")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print ("Records created successfully")
conn.close()
2、建立表
import sqlite3
conn = sqlite3.connect('python_test.db')
print ("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print("Table created successfully")
conn.commit()
conn.close()
3、插入操作
import sqlite3
conn = sqlite3.connect('p1_test.db')
c = conn.cursor()
print ("Opened database successfully")
c.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
''')
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print ("Records created successfully")
conn.close()
4、显示
import sqlite3
conn = sqlite3.connect('python_test.db')
c = conn.cursor()
cursor = c.execute("SELECT * FROM COMPANY")
for row in cursor:
print( "ID = ", row[0])
print( "NAME = ", row[1])
print( "ADDRESS = ", row[2])
print( "SALARY = ", row[3], "\n")
conn.close()
5、更新
import sqlite3
conn = sqlite3.connect('python_test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("UPDATE COMPANY set ADDRESS = '中国' where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("ADDRESS = ", row[2])
print ("SALARY = ", row[3], "\n")
print ("Operation done successfully")
conn.close()
6、删除
import sqlite3
conn = sqlite3.connect('python_test.db')
print ("Operation done succddessfully")
c = conn.cursor()
print ("Opened database successfully")
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print ("Total number of rows deleted :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("ADDRESS = ", row[2])
print ("SALARY = ", row[3], "\n")
print ("Operation done successfully")
conn.close()
注：我的系统是ubuntu20.04，在使用sqlite命令来插入中文时在终端无法显示也无法插入中文，但是通过python来执行命令就能插入中文了，并且显示也能正常显示。 |
【本文地址】