python3 sqlite3学习总结

1.创建并打开数据库

1）创建硬盘数据库

conn = sqlite3.connect("test.db")

该指令会在本地目录创建test.db文件，数据库关闭重新开启后，历史操作记录仍然存在

2）创建内存数据库

conn = sqlite3.connect(":memory:")

该指令创建的内存数据库仅支持当前操作，关闭重新开启后，历史操作会全部丢失。

2.执行sql语句

conn = sqlite3.connect("test.db")
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);''')')

#插入
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()

# 查询
cursor = c.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print("%s %s %s %s"% (row[0],row[1],row[2],row[3]))

更新
c.excute("UPDATE COMPANY SET SALARY = 25000.00 WHERE ID=1")
conn.commit()


删除
c.execute("delete from company where id=2;")
conn.commit()

sql语句不区分大小写，且执行execute语句时，末尾可加分号；也可不加；。