python/school/_unsupported/070.py · 1037 字节 · 下载原文
import sqlite3
databaseName = 'database.sqlite3'
tableName = 'articles'
def doSql(sql):
'''执行SQL语句'''
with sqlite3.connect(databaseName) as conn:
cur = conn.cursor()
cur.execute(sql)
conn.commit()
def getData(sql):
'''根据指定的SELECT语句获取并返回数据'''
with sqlite3.connect(databaseName) as conn:
cur = conn.cursor()
cur.execute(sql)
temp = cur.fetchall()
return temp
def hasTable(tableName):
sql = f'SELECT COUNT(*) FROM sqlite_master WHERE name="{tableName}"'
return getData(sql)[0][0]!=0
if not hasTable(tableName):
# 创建表时会自动创建系统表sqlite_sequence,存储每个用户表中RowId的最大值
sql = (f'CREATE TABLE {tableName}' +
'(id INTEGER PRIMARY KEY AUTOINCREMENT, author TEXT,'+
' content TEXT, publishTime DATETIME)')
doSql(sql)
sql = (f'INSERT INTO {tableName}(author,content,publishTime)'+
' VALUES("dfg","test",datetime("now","localtime"))')
doSql(sql)