#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @descrip : operate SqLite intrface
# @Time    : 2020/04/22 21:57
# @Author  : mingfei.tang
import sqlite3

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()