Python Sqlit3入门实战

1.可视化工具

sqlite可视化管理工具 windows下载
(1)从http://www.sqliteexpert.com下载个人免费版安装。			
（2）打开*.db文件，就打开了数据库。点击已创建的表在右边可看见SQL,点击它可在里面输入SQL语句对该表操作。有边Data可显示数据及属性。右边Design可对表的属性进行设置。

2.基本语句

"""
# 建表只能运行一次
#NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL
#PRIMARY KEY(主键)
c.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print("Table created successfully")
"""

# 第一种：execute "INSERT"
#c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(1, 'Paul', 32, 'California', 20000.00 )")
#c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES(2, 'Yaul', 18,'af',200.0)")
"""
# 第二种：execute multiple commands
emp = [(2, 'Allen', 25, 'Texas', 15000.00),
 (3, 'Teddy', 23, 'Norway', 20000.00)
 ]
c.executemany('insert into COMPANY values (?,?,?,?,?)', emp)
# 第三种：using the placeholder
c.execute("insert into COMPANY values (?,?,?,?,?)", (4, 'Allen', 25, 'Texas', 15000.00))
cursor = c.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
 print("ID = ", row[0])
 print("NAME = ", row[1])
 print("ADDRESS = ", row[2])
 print("SALARY = ", row[3], "\n")

 
# update
# c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
# conn.commit()
# print("Total number of rows updated :{0}".format(conn.total_changes))
# delete
c.execute("DELETE from COMPANY where ID>=1 and ID <=4")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)
print("Operation done successfully")
# Connection 和 Cursor 对象，打开后一定要关闭。
"""

# 必须用commit 函数提交你的修改
# 否则你的修改不会被写入数据库
#conn.commit() conn.close()

3.实战代码

# coding=utf-8
import sqlite3

class Database:
    def __init__(self):
        print("\t 初始化 ")
        print("\n 数据库连接中 \n")
        # 接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象 conn
        self.conn = sqlite3.connect('students.db')
        print("\n 数据库连接成功 \n")

        # 创建一个游标 cursor，用来运行 SQL 语句
        self.c = self.conn.cursor()

    def connect_table(self):
        print("\t 连接表 ")
        #连接表或创建表
        self.table_name=input("\n 输入表名如' \"table\" ': ")
        #返回一个你要查询的表名对象（Cursor类型）
        curs=self.c.execute('''SELECT count(*)
        FROM sqlite_master WHERE type='table' AND name='''+self.table_name)
        if(curs.fetchall()[0][0]):
            print("\n 表存在！\n")
            return True
        else:
            print("\n 表不存在！\n")
            if int(input("\n 是否要创建表' 0:否 ':")):
                self.add_table()
                return True
            else:
                return False
        
        """
        表不存在则创建
        self.c.execute('''CREATE TABLE IF NOT EXISTS '''+self.tabel_name+'''
        ('''+input("\n 输入属性名与类型如\" no int,name text \":")+''')''')
        """
        print("\n 连接表成功 \n")

    def delete_databases(self):
        pass

    def add_databases(self):
        pass

    def search_databases_result(self):
        pass

    def add_table(self):
        self.c.execute("""CREATE TABLE """+input("\n 输入创建的表名如' \"students\" ':")+
                """("""+input("\n 输入创建的表的属性名与类型如' no int,name text ':")+""")""")
        print("Table created successfully")

    def search_table_result(self):
        pass

    def delete_table(self):
        print("\t 删除表")
        #删除表
        self.table_name=input("\n输入删除表名如' \"ll\" ': ")
        #返回一个你要查询的表名对象（Cursor类型）
        curs=self.c.execute('''SELECT count(*)
        FROM sqlite_master WHERE type='table' AND name='''+self.table_name)
        if(curs.fetchall()[0][0]):
            print("\n 表存在！\n")
            self.c.execute('''DROP TABLE '''+self.table_name)
            print("\n 表删除! \n")
        else:
            print("\n 表不存在！\n")

    def add_data(self):
        print("\t 增加数据")
        #增加数据
        self.c.execute("""INSERT INTO"""+self.table_name+"""
        ("""+input("\n 输入数据属性名如' ID,NAME '：")+""") VALUES("""+input("\n 输入数据属性值如' 20,ton '(字符型数据需引号括起)：")+""")""")
        self.conn.commit()

    def delete_data(self):
        print("\t 删除数据 ")
        self.c.execute("""DELETE from """+self.table_name+""" where """+input("设置删除条件如' ID>=1 and ID <=4 '："))
        self.conn.commit()
        print("Total number of rows deleted :""", self.conn.total_changes)

    def update_data(self):
        print("\t 更新数据 \n")
        self.c.execute("""UPDATE """+self.table_name+""" set """+input("需变更的属性如' 属性名=值 ':")+""" where """+input("输入主键如' 属性名=值 ':"))
        self.conn.commit()
        print("Total number of rows updated :{0} \n".format(self.conn.total_changes))

    def search_data_result(self):
        print("\t 查找数据 \n")
        curs=self.c.execute("""SELECT * FROM """+self.table_name+""" where """+input("输入查询条件如' stu_id=3928  ':"))
        print("",curs.fetchall(),"",sep="\n")
        

    def shut_down(self):
        print("\t 关闭连接")
        #关闭资源
        self.c.close()
        # 必须用 commit 函数提交你的修改
        # 否则你的修改不会被写入数据库
        self.conn.commit()
        self.conn.close()


if __name__=="__main__":
    #属性 stu_id INT PRIMARY KEY NOT NULL,stu_name char[10] not null,gender char[1] not null,age int not null,phone int not null,enroll_date text not null,home_address text not null
    #38,"yfc",'m',20,12306,"2017/9/1","China"
    my=Database()
    while not my.connect_table():
        if not int(input("\n 退出数据库-> 0 :")):
            break
        print("\n 重新->")

    #my.add_table()
    my.search_data_result()
    #my.delete_table()
    #my.add_data()
    #my.delete_data()
    #my.update_data()

    
    my.shut_down()