Python 操作 sqlite 数据库笔记

1. 导入 sqlite 库

import sqlite3

2. 查询数据库

def sqlExecte(cls, eventid, date_min, date_max):
    # 一条 sql 查询语句
    sql = """select * from (select dayno, total, row_number() over(order by total desc) rank
                            from
                            (select
                            dayno,
                            count(1) as total
                            from systemstamp
                            where eventid = '{}' and dayno > {} and dayno < {}
                            group by dayno
                            )as t1
                            )as t2
                            where rank =3 and total > 12""".format(eventid, str(date_min), str(date_max))

    # 连接数据库
    conn = sqlite3.connect(dpfile)

    # 获得光标
    cursor = conn.cursor()

    # 执行sql
    cursor.execute(sql)

    # 提取查询结果
    ret = cursor.fetchall()

    # 关闭连接
    conn.close()