欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Python学习笔记(二十一)sqlite数据库

程序员文章站 2022-07-07 22:56:31
...

参考资料:https://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001388320596292f925f46d56ef4c80a1c9d8e47e2d5711000

       Python定义了一套操作数据库的API接口,任何数据库要连接到Python,只需要提供符合Python标准的数据库驱动即可。
由于SQLite的驱动内置在Python标准库中,所以我们可以直接来操作SQLite数据库。Python就内置了SQLite3。

       下面是我的学习代码:

import sqlite3

#创建SQLITE表 db-数据库文件名 tablename-表名 fields-字段定义列表
#返回值:0-成功 1-打开数据库失败 2-游标错误 3-其他异常
def createTable(db, tablename, fields):
    code = 0
    error = None
    try:
        conn = sqlite3.connect(db)
        if conn:
            cursor = conn.cursor()
            if cursor:
                sFields = ''
                for s in fields:
                    if sFields != '':
                        sFields = sFields + ','
                    sFields = sFields + s
                sql = 'CREATE TABLE %s(%s)' % (tablename, sFields)
                cursor.execute(sql)
                cursor.close()
                cursor = None
                conn.commit()
            else:
                code = 2
            conn.close()
            conn = None
        else:
            code = 1
    except BaseException, e:
        code = 3
        error = e.message
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
    return (code, error)

#读取表字段列表 db-数据库文件 table-表名
#返回值 字段列表,错误码(1-打开数据库失败 2-游标错误 3-其他异常),错误信息
def getFields(db, table):
    fields = None
    code = 0
    error = None
    try:
        conn = sqlite3.connect(db)
        if conn:
            cursor = conn.cursor()
            if cursor:
                sql = 'PRAGMA table_info(%s)' % table
                cursor.execute(sql)
                fields = []
                for c in cursor:
                    fields.append(c[1])
                cursor.close()
                cursor = None
                conn.commit()
            else:
                code = 2
            conn.close()
            conn = None
        else:
            code = 1
    except BaseException, e:
        code = 3
        error = e.message
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
    return (code, error, fields)

#执行SQL语句 db-数据库文件 sql-要执行的SQL语句
#返回值 错误码(1-打开数据库失败 2-游标错误 3-其他异常),错误信息
def executeSQL(db, sql):
    code = 0
    error = None
    try:
        conn = sqlite3.connect(db)
        if conn:
            cursor = conn.cursor()
            if cursor:
                cursor.execute(sql)
                cursor.close()
                cursor = None
                conn.commit()
            else:
                code = 2
            conn.close()
            conn = None
        else:
            code = 1
    except BaseException, e:
        code = 3
        error = e.message
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
    return (code, error)

#获取sql执行结果
#返回值 结果列表,错误码(1-打开数据库失败 2-游标错误 3-其他异常),错误信息
def getValues(db, sql):
    values = None
    code = 0
    error = None
    try:
        conn = sqlite3.connect(db)
        if conn:
            cursor = conn.cursor()
            if cursor:
                cursor.execute(sql)
                values = cursor.fetchall()
                cursor.close()
                cursor = None
                conn.commit()
            else:
                code = 2
            conn.close()
            conn = None
        else:
            code = 1
    except BaseException, e:
        code = 3
        error = e.message
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
    return (code, error, values)

#测试入口
def Test():
    #输入数据库文件名
    d = raw_input('input a database filename:')
    #输入表名
    t = raw_input('input a tablename:')
    #输入字段定义
    fields = []
    while True:
        f = raw_input('input field define, none to break:')
        if f and f != '':
            fields.append(f)
        else:
            break
    #创建表
    ecode, emsg = createTable(d, t, fields)
    if ecode == 0:
        print 'create successfull!'
    else:
        print 'create failure, error: %d, %s' % (ecode, emsg)
    #获取字段
    ecode, emsg, fields = getFields(d, t)
    if ecode == 0:
        ff = ''
        vv = ''
        i = 0
        for f in fields:
            if i > 0:
                ff = ff + ','
                vv = vv + ','
            ff = ff + f
            vv = vv + str(i)
            i = i + 1
        #执行SQL语句
        sql = 'INSERT INTO %s(%s)VALUES(%s)' % (t, ff, vv)
        print 'excute sql:', sql
        ecode, emsg = executeSQL(d, sql)
        if ecode == 0:
            #执行SQL语句并返回结果集
            ecode, emsg, values = getValues(d, 'SELECT * FROM %s' % t)
            if ecode == 0:
                print 'values of table %s:' % t
                print values
            else:
                print 'error on getValues table %s:%d %s' % (t, ecode, emsg)
        else:
            print 'error on excute sql:%d %s' % (ecode, emsg)
    else:
        print 'error to read fields of table %s:%d %s' % (t, ecode, emsg)
今天就学习到这里,下一节从MySQL学起。