#利用python操作本地数据库
#创建/打开数据库
#!/usr/bin/python
import sqlite3
#conn = sqlite3.connect("test.db")
conn = sqlite3.connect("C:\\Users\\Administrator\\Desktop\\4.6.0\\localdata\\ETS.db")
print(" Opened database successfully")
c=conn.cursor()
#利用指针作为一个游标c
'''
#创建表(创建/打开数据库后可以直接对数据库中的已存在表进行操作)
c.execute(""" create table company_xixi
(id int parimary key not null,
name text not null,
age int not null,
address char(50),
salary real)""")
print ("table created successfully")
conn.commit()
#一定要有conn.commit()这句来提交事务,否则不能真正的插入数据
conn.close()
#关闭connection
#利用python对sqlite3数据库进行简单的增删查改
#insert操作————插入、增加
c.execute("insert into company_xixi (id,name,age,address,salary)\
values(2018009488,'yangxi',24,'guangdongshenguangzhoushi',1000)")
c.execute("insert into company_xixi (id,name,age,address,salary)\
values(2018009488,'yangxi',24,'guangdongshenguangzhoushi',2000)")
c.execute("insert into company_xixi (id,name,age,address,salary)\
values(1,'yangxi',24,'guangdongshenguangzhoushi',2000)")
c.execute("insert into company_xixi (id,name,age,address,salary)\
values(2,'yangxi',24,'guangdongshenguangzhoushi',2000)")
print("records created successfully")
#如何避免重复插入相同数据
#select操作————查询
cursor=c.execute("select id,name,age,address,salary from company_xixi")
for row in cursor:
print ("id=",row[0])
print ("name=",row[1])
print ("age=",row[2])
print ("address=",row[3])
print ("salary=",row[4])
print ("operation done successfully")
#update————修改
c.execute("update company_xixi set salary=9000 where id=2018009488")
print("total number of rows update:" )
cursor=c.execute("select id,name,age,address,salary from company_xixi")
for row in cursor:
print("id=",row[0])
print("name=",row[1])
print("age=",row[2])
print("address=",row[3])
print("salary=",row[4])
print("operation done successfully")
#delete————删除操作
c.execute("delete from company_xixi where id=1")
print("total number of rows deleted:")
cursor=c.execute("select id,name,age,address,salary from company_xixi")
for row in cursor:
print("id=",row[0])
print("name=",row[1])
print("age=",row[2])
print("address=",row[3])
print("salary=",row[4])
print("operation done successfully")
conn.commit()
#一定要有conn.commit()这句来提交事务,否则不能真正的插入数据
conn.close()
#关闭connection
#注意:对数据库进行增删查改操作之后,为了检验结果,需要对数据库进行遍历输出。
#实践1————查询PC端localdata数据库ETS.db中已有的数据
#1、在user表中查询账号18520423401的用户名
cursor=c.execute("select name from user where account=18520423401")
print("账户18520423401的用户名如下:")
for row in cursor:
print("name=",row[0])
print("select finished!")
#2、查询paperpubno=10024时的作业信息
cursor=c.execute("select * from resourcedetail where paperpubno in ('10024')")
print("paperpubno=10024时作业的信息如下:")
for row in cursor:
print("struct:", row[19])
print("信息如上!")
'''
conn.commit()
conn.close()