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