sqlite3使用

python3中使用sqlite3例子

import sqlite3
import os

DB_FILE_PATH = ''
TABLE_NAME = ''
SHOW_SQL = True

def get_conn(path):
    conn = sqlite3.connect(path)
    if os.path.exists(path) and os.path.isfile(path):
        print('硬盘上面：[{}]'.format(path))
        return conn
    else:
        conn = None
        print('内存上面：[:memory:]')
        return sqlite3.connect(':memory:')

def get_cursor(conn):
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()

#########################################################################
###################删除|创建表操作  START#################################
#########################################################################
def drop_table(conn, table):
    if table is not None and table != '':
        sql = 'DROP TABLE IF EXISTS ' + table
        if SHOW_SQL:
            print('执行sql：[{}]'.format(sql))
        cu = get_cursor(conn)
        cu.execute(sql)
        conn.commit()
        print('删除数据库表[{}]成功'.format(table))
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def create_table(conn, sql):
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql：[{}]'.format(sql))
        cu.execute(sql)
        conn.commit()
        print('创建数据库表[student]成功')
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

#########################################################################
###################删除|创建表操作  END#################################
#########################################################################

def close_all(conn, cu):
    try:
        if cu is not None:
            cu.close()
    finally:
        if cu is not None:
            cu.close()

#########################################################################
###################    数据库操作   START #################################
#########################################################################
def save(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql：[{}], 参数：[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
        else:
            print('the [{}] is empty or equal None!'.format(sql))

def fetchall(conn, sql):
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        r = cu.fetchall()
        if len(r) > 0:
            for e in range(len(r)):
                print(r[e])
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def fetchone(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            d = (data, )
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}], 参数:[{}]'.format(sql, data))
            cu.execute(sql, d)
            r = cu.fetchall()
            if len(r) > 0:
                for e in range(len(r)):
                    print(r[e])
        else:
            print('the [{}] equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def update(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}], 参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def delete(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}], 参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
      print('the [{}] is empty or equal None!'.format(sql))

#########################################################################
###################    数据库操作   START #################################
#########################################################################

#########################################################################
###################    测试操作   START #################################
#########################################################################
def drop_table_test():
    print('删除数据库表测试...')
    conn = get_conn(DB_FILE_PATH)
    drop_table(conn, TABLE_NAME)

def create_table_test():
    print('创建数据库表测试...')
    create_table_sql = '''CREATE TABLE 'student' (
                          'id' int(11) NOT NULL,
                          'name' varchar(20) NOT NULL,
                          'gender' varchar(4) DEFAULT NULL,
                          'age' int(11) DEFAULT NULL,
                          'address' varchar(200) DEFAULT NULL,
                          'phone' varchar(20) DEFAULT NULL,
                          PRIMARY KEY('id')
                          )'''

    conn = get_conn(DB_FILE_PATH)
    create_table(conn, create_table_sql)

def save_test():
    print('保存数据测试...')
    save_sql = 'INSERT INTO student values (?, ?, ?, ?, ?, ?)'
    data = [(1, 'Hongten', '男', 20, '广东省广州市', '13423****62'),
            (2, 'Tom', '男', 22, '美国旧金山', '15423****63'),
            (3, 'Jake', '女', 18, '广东省广州市', '18823****87'),
            (4, 'Cate', '女', 21, '广东省广州市', '14323****32')]
    conn = get_conn(DB_FILE_PATH)
    save(conn, save_sql, data)

def fetchall_test():
    print('查询所有数据...')
    fetchall_sql = 'SELECT * FROM student'
    conn = get_conn(DB_FILE_PATH)
    fetchall(conn, fetchall_sql)

def fetchone_test():
    print('查询一条数据...')
    fetchone_sql = 'SELECT * FROM student WHERE ID = ?'
    data = 1
    conn = get_conn(DB_FILE_PATH)
    fetchone(conn, fetchone_sql, data)

def update_test():
    print('更新数据...')
    update_sql = 'UPDATE student SET name = ? WHERE ID = ?'
    data = [('HongtenAA', 1),
            ('HongtenBB', 2),
            ('HongtenCC', 3),
            ('HongtenDD', 4)]
    conn = get_conn(DB_FILE_PATH)
    update(conn, update_sql, data)

def delete_test():
    print('删除数据...')
    delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ?'
    data = [('HongtenAA', 1),
            ('HongtenDD', 4)]
    conn = get_conn(DB_FILE_PATH)
    delete(conn, delete_sql, data)

#########################################################################
###################    测试操作   END #################################
#########################################################################


def init():
    global DB_FILE_PATH
    DB_FILE_PATH = 'c:\wgj\work\python\magic_box\hongten.db'
    global TABLE_NAME
    TABLE_NAME = 'student'
    global SHOW_SQL
    SHOW_SQL = True
    print('show_sql: {}'.format(SHOW_SQL))
    drop_table_test()
    create_table_test()
    save_test()

def main():
    init()
    fetchall_test()
    print('#' * 50)
    fetchone_test()
    print('#' * 50)
    update_test()
    fetchall_test()
    print('#' * 50)
    delete_test()
    fetchall_test()

if __name__ == '__main__':
    main()

结果：

C:\Users\lj0sb5\AppData\Local\Programs\Python\Python35-32\python.exe C:/wgj/work/python/magic_box/testFile2.py
show_sql: True
删除数据库表测试...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql：[DROP TABLE IF EXISTS student]
删除数据库表[student]成功
创建数据库表测试...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql：[CREATE TABLE 'student' (
'id' int(11) NOT NULL,
'name' varchar(20) NOT NULL,
'gender' varchar(4) DEFAULT NULL,
'age' int(11) DEFAULT NULL,
'address' varchar(200) DEFAULT NULL,
'phone' varchar(20) DEFAULT NULL,
PRIMARY KEY('id')
)]
创建数据库表[student]成功
保存数据测试...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql：[INSERT INTO student values (?, ?, ?, ?, ?, ?)], 参数：[(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')]
执行sql：[INSERT INTO student values (?, ?, ?, ?, ?, ?)], 参数：[(2, 'Tom', '男', 22, '美国旧金山', '15423****63')]
执行sql：[INSERT INTO student values (?, ?, ?, ?, ?, ?)], 参数：[(3, 'Jake', '女', 18, '广东省广州市', '18823****87')]
执行sql：[INSERT INTO student values (?, ?, ?, ?, ?, ?)], 参数：[(4, 'Cate', '女', 21, '广东省广州市', '14323****32')]
查询所有数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[SELECT * FROM student]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
(2, 'Tom', '男', 22, '美国旧金山', '15423****63')
(3, 'Jake', '女', 18, '广东省广州市', '18823****87')
(4, 'Cate', '女', 21, '广东省广州市', '14323****32')
##################################################
查询一条数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[SELECT * FROM student WHERE ID = ?], 参数:[1]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
##################################################
更新数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[UPDATE student SET name = ? WHERE ID = ?], 参数:[('HongtenAA', 1)]
执行sql:[UPDATE student SET name = ? WHERE ID = ?], 参数:[('HongtenBB', 2)]
执行sql:[UPDATE student SET name = ? WHERE ID = ?], 参数:[('HongtenCC', 3)]
执行sql:[UPDATE student SET name = ? WHERE ID = ?], 参数:[('HongtenDD', 4)]
查询所有数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[SELECT * FROM student]
(1, 'HongtenAA', '男', 20, '广东省广州市', '13423****62')
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(3, 'HongtenCC', '女', 18, '广东省广州市', '18823****87')
(4, 'HongtenDD', '女', 21, '广东省广州市', '14323****32')
##################################################
删除数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[DELETE FROM student WHERE NAME = ? AND ID = ?], 参数:[('HongtenAA', 1)]
执行sql:[DELETE FROM student WHERE NAME = ? AND ID = ?], 参数:[('HongtenDD', 4)]
查询所有数据...
硬盘上面：[c:\wgj\work\python\magic_box\hongten.db]
执行sql:[SELECT * FROM student]
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(3, 'HongtenCC', '女', 18, '广东省广州市', '18823****87')

Process finished with exit code 0