python sqlite3 模板

python sqlite3 模板

import sqlite3

class MZsqlite(object):
    def __init__(self,db_name,tb_name):
        super(MZsqlite, self).__init__()

        self.db_name=db_name
        self.tb_name=tb_name

    def init_sqlite(self):
        """
           初始化数据库
        :param db_name:  数据库名称
        :param tb_name:  表名称
        :return:
        """
        # 连接数据库，如果不存在则会在当前目录创建
        conn = sqlite3.connect(self.db_name)
        try:
            # 创建游标
            cursor = conn.cursor()
            SQL = '''
              CREATE TABLE IF NOT EXISTS '{}'(
                id int primary key ,
                user_name VARCHAR(200) NOT NULL,
                password VARCHAR(200) not null,
                auto int not null,
                save int not null
            )
                '''.format(self.tb_name)
            # 创建数据库表
            cursor.execute(SQL)
            # 提交到数据库
            conn.commit()
            print('连接数据库成功')
        except Exception as e:
            print('连接数据库失败',e)
        return conn,cursor


    def update_info(self,data):
        """
        更新数据,这里直接通过用户名来判定
        :param data:
        :return:
        """
        conn,cursor=self.init_sqlite()
        sql="replace into info(id,user_name,password,auto,save) values({},'{}','{}',{},{})".format(1,data['user_name'],data['password'],data['auto'],data['save'])
        try:
            cursor.execute(sql)
            conn.commit()
            print('更新保存成功')
        except Exception as update_info_ERR:
            print('update_info_ERR:',update_info_ERR)
        finally:
            conn.close()


    def get_info(self):
        """
        获取表种所有信息
        :return:
        """
        conn, cursor = self.init_sqlite()
        sql = "select * from info"
        try:
            cursor.execute(sql)
            result=cursor.fetchall()
            if result:
                # print(result)
                for i in result:
                    print(i)
            # conn.commit()
            else:
                print('没有表')
        except Exception as update_info_ERR:
            print('update_info_ERR:', update_info_ERR)
        finally:
            conn.close()

    def _delete_db(self):
        """
        删除数据库
        :return:
        """

    def delete_data(self,name):
        """
        删除指定人名行
        :param name:
        :return:
        """
        conn, cursor = self.init_sqlite()
        sql = "delete from '{}' where name = '{}'".format(self.tb_name,name)
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as delete_data_ERR:
            print('delete_data_ERR:', delete_data_ERR)
        finally:
            conn.close()

    def _drop_table(self,tb_name):
        """
        删除表
        :param tb_name:
        :return:
        """
        conn, cursor = self.init_sqlite()
        sql = "drop table '{}'".format(tb_name)
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as _drop_table_ERR:
            print('_drop_table_ERR:', _drop_table_ERR)
        finally:
            conn.close()


if __name__ == '__main__':
    db_name = 'user.db'
    tb_name = 'info'
    data={'user_name':'Master','password':'123456','auto':0,'save':1}
    mz_sqlite=MZsqlite(db_name,tb_name)