sqlite3学习笔记[数据库教程]

Z时代
2024-01-10
分类：综合
database
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @descrip : operate SqLite intrface
# @Time    : 2020/04/22 21:57
# @Author  : mingfei.tang
importsqlite3
class SqLite3_Opt():
def__init__(self):
print(sqlite3.apilevel)
        self.conn = sqlite3.connect(‘first.db‘)
def create_table(self):
        self.cursor = self.conn.cursor()
#create table -1
        self.cursor.execute(‘‘‘create table user_tb(
                               _id integer primary key autoincrement,
                               name text,
                               password text,
                               gender text)‘‘‘)
#create table -2
        self.cursor.execute(‘‘‘create table order_tb(
                               _id integer primary key autoincrement,
                               item_name text,
                               item_price text,
                               item_number text,
                               user_id inteter,
                               foreign key(user_id) references user_tb(_id))‘‘‘)
        self.cursor.close()
#insert-1  many
def insert_many_value(self):
        self.cursor = self.conn.cursor()
        self.cursor.executemany(‘insert into user_tb values(null, ?, ?, ?)‘,
                                ((‘孙悟空-1‘, ‘423456‘,‘male‘),
                                (‘孙悟空-2‘, ‘423456‘,‘male‘),
                                (‘孙悟空-3‘, ‘423456‘,‘male‘),
                                (‘孙悟空-4‘, ‘423456‘,‘male‘)))
        self.conn.commit()
        self.cursor.close()
#insert-2 single
def insert_single_value(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute(‘insert into user_tb values(null, ?, ?, ?)‘,(‘孙悟空-0‘, ‘423456‘,‘male‘))
        self.conn.commit()
        self.cursor.close()
#delete single
def delete_single_data(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("DELETE FROM user_tb WHERE _id=?", (1,))
        self.conn.commit()
        self.cursor.close()
#update single
def update_data(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("UPDATE user_tb SET name=? WHERE _id=?", ("猪八戒", 1))
        self.conn.commit()
        self.cursor.close()
#fatch all data
def fetch_all(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("select * from user_tb")
print(self.cursor.fetchall())
        self.cursor.close()
#fatch one data
def fetch_one(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("select * from user_tb")
print(self.cursor.fetchone())
        self.cursor.close()
#fatch many data
def fetch_many(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("select * from user_tb")
print(self.cursor.fetchmany(10))
        self.cursor.close()
def closedb(self):
        self.conn.close()
class Unit_test():
def__init__(self):
pass
def infor_test(self):
        test = SqLite3_Opt()
#test.create_table()
#test.insert_single_value()
#test.update_data()
#test.delete_single_data()
#test.fetch_all()
#test.fetch_one()
        test.fetch_many()
        test.closedb()
if__name__ == "__main__":
    utest=Unit_test()
    utest.infor_test()