使用python操作数据库

标签：execute python 数据库 db cursor user close 操作 conn

实例1、创建SQLite数据库文件

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('create table user(id int(10) primary key,name varchar(20))')
cursor.close()
conn.close()

实例2、新增用户数据信息

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('insert into user(id,name) values("1","MRSOFT")')
cursor.execute('insert into user(id,name) values("2","Andy")')
cursor.execute('insert into user(id,name) values("3","明日科技小助手")')
cursor.close()
conn.commit()
conn.close()

实例3、使用3种方式查询用户数据信息

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('select*from user')
result1=cursor.fetchone()
print(result1)
cursor.close()
conn.close()

实例4、修改用户数据信息

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('update user set name =?where id=?',('MR',1))
cursor.execute('select*from user')
result=cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

实例5、删除用户数据信息

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('delete from user where id=?',(1,))
cursor.execute('select* from user')
result=cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

实例6、使用PyMySQL连接数据库

import pymysql
db=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft")
cursor=db.cursor()
cursor.execute("SELECT VERSION()")
data=cursor.fetchone()
print("Database version : %s"%data)
db.close()

实例7、创建books图书表

import pymysql
db=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft")
cursor=db.cursor()
cursor.execute("DROP TABLE IF EXISTS books")
sql="""
CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
"""
cursor.execute(sq1)
db.close()

实例8、向books图书表添加图书数据

import pymysql
db=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft",charset="utf8")
cursor=db.cursor()
data=[("零基础学Python","Python","79.80","2018-5-20"),
("python从入门到精通","Python","69.80","2018-6-18"),
("零基础学PHP","PHP","69.80","2017-5-21"),
("PHP项目开发实战入门","PHP","79.80","2016-5-21"),
("零基础学Java","Java","69.80","2017-5-21")
]
try:
cursor.executemany("insert into books(name,category,price,publish_time) value (%s,%s,%s,%s)",data)
db.commit()
except:
db.rollback()
db.close()

实战1、获取指定数据表中的信息

import pymysql
a=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft")
cursor=a.cursor()
cursor.execute("select name,price from books")
h=cursor.fetchall()
for i in range(0,len(h)):
print("图书：《" + str(h[i][0]) + "》，价格：￥" + str(h[i][1]) + "元")
cursor.close()
a.close()

实战2、查找指定年份之后的图书信息

import pymysql
a=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft")
cursor=a.cursor()
cursor.execute("select name,price,publish_time from books where price < 70 and publish_time >= '2017-01-01'")
h=cursor.fetchall()
for i in range(0,len(h)):
print("图书：《" + str(h[i][0]) + "》，价格：￥" + str(h[i][1]) + "元，出版日期：" + str(h[i][2]))
cursor.close()
a.close()

实战3、批量删除指定的图书信息

import pymysql
a=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft")
cursor=a.cursor()
cursor.execute("delete from books where category = 'PHP'")
cursor.execute("select name,price from books")
h=cursor.fetchall()
for i in range(0,len(h)):
print("图书：《" + str(h[i][0]) + "》，价格：￥" + str(h[i][1]) + "元")
cursor.close()
a.close()

标签：execute,python,数据库,db,cursor,user,close,操作,conn
From： https://www.cnblogs.com/666u/p/17018303.html