Python3 sqlite的简单用法

# -*- coding: utf-8 -*-
# @Time    : 2018/9/13 10:25
# @Author  : 
# @Email   : 
# @File    : sqlite_test.py

import sqlite3
import os

work_path_name = 'sqlite_test'
home_path = os.path.expanduser('~')
db_file_name = 'test.db'
db_file_path = os.path.join(home_path, work_path_name, db_file_name)


def get_conn(file_path):
    '''
    创建数据库文件
    :param file_path:
    :return:
    '''
    if os.path.isfile(file_path):
        print('文件存在')
    elif os.path.isdir(file_path):
        print('包含%s文件价,删除' % file_path)
        os.rmdir(file_path)
    else:
        print('未找到文件')
        db_file_dir = os.path.dirname(file_path)
        if os.path.isfile(db_file_dir):
            print('有一个跟目录名一样的文件')
            os.remove(db_file_dir)
            os.makedirs(db_file_dir)
        elif not os.path.exists(db_file_dir):
            print('目录不存在创建,创建')
            os.makedirs(db_file_dir)
    return sqlite3.connect(db_file_path)


def get_cursor(conn):
    '''
    获取数据库操作游标对象
    :param conn:
    :return:
    '''
    if conn is not None:
        print('获取游标对象')
        return conn.cursor()
    else:
        print('conn is None')


def create_table(conn):
    pass


def execute_sql(conn, sql):
    '''
    执行sql语句
    :param conn:
    :param sql:
    :return:
    '''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        try:
            cu.execute(sql)
        except sqlite3.OperationalError as e:
            print(e)
            return
        conn.commit()
        close_all(conn, cu)


def close_all(conn, cu):
    '''
    关闭数据
    :param conn:
    :param cu:
    :return:
    '''
    if cu != None:
        cu.close()
    if conn != None:
        conn.close()


if __name__ == '__main__':
    conn = get_conn(db_file_path)

    execute_sql(conn, '''CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);''')
    pass