1.安装PyMySQL
使用pip命令安装PyMySQL库。
pip install PyMySQL
2.创建数据库
使用脚本create_employee.sql创建数据库mytestdb,以及创建数据表employee。
create_employee.sql代码如下:
create database if not exists mytestdb character set utf8;
use mytestdb;
drop table if exists `employee`;
create table `employee`(
`id` int(11) not null auto_increment,
`name` varchar(40) default null,
`age` int default null,
`sex` char(1) default null,
`income` float default null,
primary key (`id`)
)engine=innodb default charset=utf8;
mysql> source C:/Users/INT 3/Desktop/create_employee.sql;
3.数据库插入操作
import pymysql
#打开数据库连接
db = pymysql.connect(host='127.0.0.1', user='root',password='123456',
database='mytestdb',port=3306, charset='utf8')
#使用cursor()方法获取操作游标
cursor = db.cursor()
#SQL插入语句
sql = "insert into employee(name, age, sex, income) values('王五', 26, 'F', 9000)"
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
也可以使用变量向SQL语句中传递参数:
import pymysql
#打开数据库连接
db = pymysql.connect(host='127.0.0.1', user='root',password='123456',
database='mytestdb',port=3306, charset='utf8')
cursor = db.cursor()
sql = "insert into employee(name, age, sex, income) values('%s', '%d', '%s', '%d')"%('张三',30, 'M', 6000)
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
4.批量插入操作
cursor.executemany批量执行sql语句。
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306,
database='mytestdb', charset='utf8')
cursor = db.cursor()
sql = "insert into employee(name, age, sex, income)values (%s, %s, %s, %s)"
ls = []
employ1 = ('张三', 22, 'M', 7000)
employ2 = ('李四', 25, 'F', 8000)
ls.append(employ1)
ls.append(employ2)
try:
#cursor.executemany批量执行sql语句
cursor.executemany(sql, ls)
#提交到数据库执行
db.commit()
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
5.查询操作
python查询Mysql,使用fetchone()方法获取单条数据,使用fetchall()方法获取多条数据。
rowcount是一个只读属性,并返回execute()方法后影响的行数。
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mytestdb', port=3306, charset='utf8')
#使用cursor方法操作游标
cursor = db.cursor()
sql = "select * from employee where income > '%d'" %(2000)
try:
#执行sql语句
cursor.execute(sql)
#获取所有记录列表
results = cursor.fetchall()
for row in results:
id = row[0]
name = row[1]
age = row[2]
sex = row[3]
income = row[4]
#打印结果
print("id=%s, name=%s, age=%d, sex=%s, income=%d" %(id, name, age, sex, income))
except Exception as e:
print(e)
finally:
db.close()
6.更新操作
#数据库更新操作
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
sql = "update employee set age = '%d' where id = '%s'"%(28, 1)
try:
#执行sql语句
cursor.execute(sql)
#提交到数据库执行
db.commit()
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
7.删除操作
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
sql = "delete from employee where id='%s'"%(1)
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
#发生错误时回滚
db.rollback()
finally:
db.close()
8.执行事务
#执行事务
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
#SQL删除记录语句
sql = "delete from employee where age > '%d'"%(20)
try:
#执行SQL语句
cursor.execute(sql)
#向数据库提交
db.commit()
except:
#发生错误时回滚
db.rollback()