FLASK学习记录-sqlite3基本操作
sqltie3是内置模块,数据库操作,以及表的增删改查参考https://www.runoob.com/sqlite/sqlite-python.html
实例
创建数据库
$ sqlite3 test.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. sqlite> .databases main: /usr/dog/flask_web/flask-test2/test.db r/w sqlite> .exit (flask-test2) [xxx flask-test2]$ ls Pipfile Pipfile.lock test.db
建表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
$ cat createTable.py #!/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) NOT NULL, SALARY REAL ); ''') print ("数据表创建成功") conn.commit() conn.close()
INSERT 操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
$ cat insert.py #!/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()
SELECT 操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
$ cat select.py #!/usr/bin/python import sqlite3 conn = sqlite3.connect('test.db') c = conn.cursor() print ("数据库打开成功") cursor = c.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()
UPDATE 操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
$ cat update.py #!/usr/bin/python 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 = c.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()
DELETE 操作,操作语句如下
c.execute("DELETE from COMPANY where ID=2;")