pip3 install pysqlite3

import sqlite3
# 連接數(shù)據(jù)庫(如果不存在則創(chuàng)建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")
?
# 創(chuàng)建游標
cursor = conn.cursor()

# 關(guān)閉游標
cursor.close()
# 提交事物
conn.commit()
# 關(guān)閉連接
conn.close()

import sqlite3

conn = sqlite3.connect('test.db')
print ("數(shù)據(jù)庫打開成功")
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);''')
print ("數(shù)據(jù)表創(chuàng)建成功")
conn.commit()
conn.close()

sql="select name from sqlite_master where type='table' order by name"
tables=cursor.execute(sql).fetchall()
print(len(tables))

sql="DROP TABLE database_name.table_name"
cursor.execute(sql)

...
# 創(chuàng)建游標
cursor = conn.cursor()
?
# 查詢數(shù)據(jù)
sql = "select * from Student"
values = cursor.execute(sql)
for i in values:
? ? print(i)
?
# 查詢數(shù)據(jù) 2
sql = "select * from Student where id=?"
values = cursor.execute(sql, (1,))
for i in values:
? ? print('id:', i[0])
? ? print('name:', i[1])
? ? print('age:', i[2])
?
# 提交事物
conn.commit()
...

...
# 創(chuàng)建游標
cursor = conn.cursor()
?
# 插入數(shù)據(jù)
sql = "INSERT INTO Student(Name, Age) VALUES(\'love\', 22)"
cursor.execute(sql)
?
# 插入數(shù)據(jù) 2
data = ('love2', 2221) # or ['love2', 2221]
sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"
cursor.execute(sql, data)
?
# 提交事物
conn.commit()
...

# 增 ? 兩種方法
sql1="INSERT INTO table_name VALUES (value1,value2,value3,...);"
sql2='''INSERT INTO table_name?
?? ?(column1,column2,column3,...)?
?? ?VALUES (value1,value2,value3,...);'''
# 刪
sql="DELETE FROM table_name WHERE [condition];"
# 改
sql="UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];"
# 查
sql="SELECT * FROM table_name;"

# 重命名
sql="ALTER TABLE 老表名 RENAME TO 新表名;"
# 添加字段
sql="ALTER TABLE 表名 ADD COLUMN 新列 TEXT;"

# 查看所有字段名
sql="PRAGMA table_info([表名])"

import sqlite3?
conn=connectSqlite("myDB.db")
conn.row_factory = sqlite3.Row # 這句一定要加
cursor=conn.cursor()
rows=cursor.execute("SELECT * from myTable ")

row=rows.fetchone()
row.key() # 返回所有字段名，
print(row[0]) # 輸出第一個字段
print(row["field1"]) # 輸出字段名為field1的值

# ...上接上面的第一塊內(nèi)容
s=0
for row in rows:# 迭代就不用fetchone()了
?? ?s+=1
?? ?print("打印第{}個數(shù)據(jù)".format(s))
?? ?for r in row:
?? ??? ?print(r)

'''
description: 根據(jù)輸入條件，從sqlite數(shù)據(jù)庫中返回JSON數(shù)據(jù)?
param {*} db_name:str 數(shù)據(jù)庫名稱
param {*} fields:list 篩選的字段
param {*} table_name:str 要查詢的表名
param {*} condition:str 查詢的條件，注意條件的值是字符串的話需要轉(zhuǎn)義
return {*} json
author: https://blog.csdn.net/Crayonxin2000
'''
def returnJsonFromSqlite(db_name,fields,table_name,condition):
? ? data = {}
? ? rows = [] ?# 數(shù)據(jù)
? ? conn = connectSqlite(db_name)
? ? conn.row_factory = sqlite3.Row
? ? cursor = conn.cursor()
? ? fields_str = ",".join(fields) ?# 列表轉(zhuǎn)字段，fields為所有的字段
? ? if condition=="" or condition==None:
? ? ? ? sql="SELECT {} from {} ".format(fields_str,table_name)
? ? else:
? ? ? ? sql="SELECT {} from {} WHERE {}".format(fields_str,table_name,condition)
? ? print(sql)
? ? result=cursor.execute(sql)
? ? for item in result:
? ? ? ? row = []
? ? ? ? for field in fields:
? ? ? ? ? ? row.append(item[field])
? ? ? ? rows.append(row)
? ? data["header"] = fields
? ? data["rows"] = rows
? ? # 關(guān)閉數(shù)據(jù)庫
? ? cursor.close()
? ? conn.close()
? ? return jsonify(data)?