Python操作SQLite

1. 创建数据库

import sqlite3

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

print("Opened database successfully.")

2.创建表

import sqlite3

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

print("Opened database successfully.")


curs = conn.cursor()

curs.execute('''
            CREATE TABLE USERS(
            ID INT PRIMARY KEY NOT NULL,
            NAME TEXT NOT NULL,
            AGE INT NOT NULL,
            ADDRESS CHAR(500),
            SALARY REAL
            );
            ''')

print("Table create successfully.")

conn.commit()

conn.close()

3.插入数据

import sqlite3

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

curs = conn.cursor()

curs.execute('''
        INSERT INTO USERS(ID,NAME,AGE,ADDRESS,SALARY) 
        VALUES(1,'Paul',32,'California',3200)
        ''')

curs.execute('''
        INSERT INTO USERS(ID,NAME,AGE,ADDRESS,SALARY) 
        VALUES(2,'Allen',25,'Texas',5300)
        ''')

curs.execute('''
        INSERT INTO USERS(ID,NAME,AGE,ADDRESS,SALARY) 
        VALUES(3,'Martin',40,'Norway',3600)
        ''')

curs.execute('''
        INSERT INTO USERS(ID,NAME,AGE,ADDRESS,SALARY) 
        VALUES(4,'Teddy',34,'Boston',4500)
        ''')

curs.execute('''
        INSERT INTO USERS(ID,NAME,AGE,ADDRESS,SALARY) 
        VALUES(5,'Mark',29,'Rich-Mond',3700)
        ''')

conn.commit()

conn.close()

4.查询数据

import sqlite3

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


curs = conn.cursor()

cursor = curs.execute('''
            SELECT ID,NAME,AGE,ADDRESS,SALARY FROM USERS
            ''')

for row in cursor:
    print("ID =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("ADDRESS =", row[3])
    print("SALARY =", row[4])

conn.close()

5.更新数据

import sqlite3

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


curs = conn.cursor()

curs.execute('''
            UPDATE USERS SET SALARY = 4700 WHERE ID = 5
            ''')

conn.commit()

print("Total number of rows updated:", conn.total_changes)

dataRow = curs.execute('''
                SELECT ID,NAME,AGE,ADDRESS,SALARY FROM USERS WHERE ID = 5
                ''')

for row in dataRow:
    print("ID =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("ADDRESS =", row[3])
    print("SALARY =", row[4])

conn.close()

6.删除数据

import sqlite3

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

curs = conn.cursor()

curs.execute('''
            DELETE FROM USERS WHERE ID = 5
            ''')

print("Total number of delete:", conn.total_changes)
conn.commit()

conn.close()