Python3.9+Sqlite3
python&sqlite简介
Sqlite3作为Python内置的数据库,不需要再单独下载sqlite,直接import即可。
使用python操作sqlite
创建并连接数据库
也可以把数据库名称指定为 :memory:,这样就会在 RAM 中创建一个数据库而不会在硬盘上生成文件.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print ("数据库打开成功")
创建库表
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print ("数据库打开成功")
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 ("数据表创建成功")
conn.commit()
conn.close()
新增数据
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
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 ("数据插入成功")
conn.close()
修改数据
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
c.execute("UPDATE COMPANY set SALARY = 25000.00 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 ("数据操作成功")
conn.close()
删除数据
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
c.execute("DELETE from COMPANY where ID=2;")
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 ("数据操作成功")
conn.close()
查询数据
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
result = c.execute("SELECT id, name, address, salary from COMPANY")
for row in result:
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("SALARY = ", row[3], "\n")
print ("数据操作成功")
conn.close()
查询数据库所有表名
import sqlite3
conn = sqlite3.connect("E:\python\test.db")
cursor = conn.cursor()
sql = """select name from sqlite_master where type='table' order by name"""
cursor.execute(sql)
result = cursor.fetchall()
print(result)
print(type(result))
conn.close()
查看表所有字段信息
import sqlite3
conn = sqlite3.connect("test.db")
cursor = conn.cursor()
sql = """pragma table_info(COMPANY)"""
cursor.execute(sql)
result = cursor.fetchall()
print(result)
print(type(result))
conn.close()
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)