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

建表

$ 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 操作

$ 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 操作

$ 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 操作

$ 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;")