SQLite - Python开发教程

import sqlite3  #加载本地数据库
#步骤1，连接数据库
conn=sqlite3.connect('test1.db')
# 步骤2，创建cursor
mycursor=conn.cursor()
# 步骤3，执行sql命令
# 创建表
'''
cmd="CREATE TABLE COMPANY(" \
    "ID INT PRIMARY KEY     NOT NULL," \
    "NAME           TEXT    NOT NULL," \
    "AGE            INT     NOT NULL, " \
    "ADDRESS        CHAR(50)," \
    "SALARY         REAL);"
mycursor.execute(cmd)
'''
# 删除表
'''
mycursor.execute("drop table COMPANY")
'''
# 向表中插入元素
'''
mycursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")

mycursor.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
'''
# 查询数据
def query():
    cursor = mycursor.execute("SELECT id, name, address, salary  from COMPANY")
    print(cursor)           #cursor为<sqlite3.Cursor object at 0x0075CCE0>，可以通过循环来遍历
    for item in cursor:
        print(item)  #<class 'tuple'>
query()
# 数据库修改
'''
mycursor.execute("UPDATE COMPANY set SALARY = 28000.00 where ID=1")
'''
# 数据库删除
'''
mycursor.execute("DELETE from COMPANY where ID=2;")
'''

query()
# 步骤4，提交当前修改事物
conn.commit()
# 步骤5，关闭数据库连接
conn.close()