[toc]
/* 准备数据 */
-- 创建一个数据库
create database `day18`;
-- 创建一个商品goods数据表
CREATE TABLE `goods`(
`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
`NAME` VARCHAR(150) NOT NULL,
`cate_name` VARCHAR(40) NOT NULL,
`brand_name` VARCHAR(40) NOT NULL,
`price` DECIMAL(10,3) NOT NULL DEFAULT 0,
`is_show` TINYINT NOT NULL DEFAULT 1,
`is_saleoff` TINYINT NOT NULL DEFAULT 0
)ENGINE=INNODB DEFAULT CHARSET=utf-8;
/* goods表中插入数据 */
INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
1. MySQL与Python交互
1.1 分表
-
分表是因为当一张表的数据量比较多时,但是我们只需要查询其中的某个字段数据,就会导致查询效率降低,所以此处我们需要使用到分表。
-
商品分类表
-
创建商品分类表
goods_cates
。
create table `goods_cates`(
`id` int unsigned primary key auto_increment not null,
`name` varchar(40) not null
);
- 将商品表
goods
中的商品种类goods_cates
查询并插入到 商品分类表里。
insert into `goods_cates` (name) select `cate_name` from `goods` group by `cate_name`;
- 将商品表
goods
中的商品种类goods_cates
更改为 商品表分类表goods_cates
对应的 id。
update `goods` as g inner join `goods_cates` as c on g.`cate_name`=c.`name` set g.`cate_name`=c.`id`;
1.2 安装pymysql
- 在windows操作系统上
-
Python3: pip install pymysql
-
Python2: pip install MySQLdb
1.3 Python操作系统MySQL步骤
序号 | 步骤 | 备注 |
---|---|---|
1 | 开始 | |
2 | 创建:connection | |
3 | 获取:cursor | |
4 | 执行查询 、执行命令、获取数据、处理数据 | |
5 | 关闭:cursor | |
6 | 关闭:connection | |
7 | 结束 |
1.4 几本案例
# 1.导入库
import pymysql
# 2.连接数据库
conn = pymysql.connect(
host="127.0.0.1", # IP地址
port=3306, # 端口号,注意没有引号
user='root', # 用户名
passwd='111111', # 用户密码
db='day18' # 需要连接的数据库名称
)
# 3.获取游标对象
cs = conn.cursor()
# 4.通过游标对象直行sql语句
sql = "select * from goods;"
# cs.execute(sql)
r = cs.execute(sql) 返回的是行数
# 5.得到数据
# 5.1 查询一条
print(cs.fetchone()) # 第一条
print(cs.fetchone()) # 第二条
# 5.2 获取多条数据
print(cs.fetchmany())
# cs.fetchmany(size) 返回行数数量为 size+1 行,默认为1。
# 5.3 获取全部
print(cs.fetchall())
'''
获取完毕之后,再往下获取,则返回值为None
print(cs.fetchone())
'''
1.5 练习
1.5.1 练习1
'''
封装DB类
'''
from pymysql import *
class MyDb(object):
# 实现对象再初始化连接数据库
def __init__(self):
self.my_conn()
def my_conn(self):
try:
self.conn = connect(
host="127.0.0.1", # IP地址
port=3306, # 端口号,注意没有引号
user='root', # 用户名
passwd='111111', # 用户密码
db='day18' # 需要连接的数据库名称
)
except Exception as e:
print(e)
def get_one(self):
sql = 'select * from goods;'
cs = self.conn.cursor()
cs.execute(sql)
res = cs.fetchone()
cs.close()
self.conn.close()
return res
def main():
obj = MyDb()
res = obj.get_one()
print(res)
if __name__ == '__main__':
main()
# ...
def main():
obj = MyDb()
res = obj.get_one()
res = obj.get_one() # 获取第二条报错,原因在get_one方法中已经关闭游标和连接
print(res)
if __name__ == '__main__':
main()
# ...
def get_one(self):
sql = 'select * from goods;'
cs = self.conn.cursor()
cs.execute(sql)
res = cs.fetchone()
cs.close()
return res
def close_conn(self):
self.conn.close()
def main():
obj = MyDb()
print(obj.get_one())
print(obj.get_one())
if __name__ == '__main__':
main()
# ...
def get_one(self):
sql = 'select * from goods;'
cs = self.conn.cursor()
cs.execute(sql)
res = cs.fetchone()
cs.close()
return res
def __del__(self): # 自动关闭数据库连接
self.conn.close()
def main():
obj = MyDb()
print(obj.get_one())
print(obj.get_one())
if __name__ == '__main__':
main()
1.5.2 练习2
'''
练习2:
使用面向对象完成商品查询
• 输入1:查询所有商品
• 输入2:所有商品种类
• 输入3:查询所有品牌
• 输入4:退出
• 输入5:插入数据
'''
import pymysql
class MyDb(object):
def __init__(self):
# 1.连接数据库
self.conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='111111',
db='day18'
)
# 2.获取游标
self.cs = self.conn.cursor()
# 3.定义run方法来进行输入值
def run(self):
while True:
# print('mb shop')
# print('1:查询所有商品')
# print('2:所有商品种类')
# print('3:查询所有品牌')
# print('4:退出')
# num = int(input('请输入功能对应的序号:'))
num = self.print_menu()
if num == 1:
self.show_all_goods()
elif num == 2:
self.show_all_cate()
elif num == 3:
self.show_all_brand()
elif num == 4:
break
elif num == 5:
self.add_cate()
else:
print('输入有误,请重新输入。')
@staticmethod
def print_menu():
print('mb shop')
print('1:查询所有商品')
print('2:所有商品种类')
print('3:查询所有品牌')
print('4:退出')
print('5:添加商品数据')
num = int(input('请输入功能对应的序号:'))
return num
def show_all_goods(self):
sql = 'select * from goods;'
# self.cs.execute(sql)
# res = self.cs.fetchall()
# for item in res:
# print(item)
self.execute_sql(sql)
def show_all_cate(self):
sql = 'select * from goods_cates;'
# self.cs.execute(sql)
# res = self.cs.fetchall()
# for item in res:
# print(item)
self.execute_sql(sql)
def execute_sql(self, sql):
self.cs.execute(sql)
res = self.cs.fetchall()
for item in res:
print(item)
def show_all_brand(self):
sql = 'select distinct brand_name from goods;'
self.execute_sql(sql)
def add_cate(self):
name = input('请输入新商品的名字:')
# sql = "insert into goods_cates (name) values ('%s');"%name
sql = "insert into goods_cates (name) values (%s);"
self.cs.execute(sql, (name,))
self.conn.commit()
def __del__(self):
self.cs.close()
self.conn.close()
def main():
obj = MyDb()
obj.run()
if __name__ == '__main__':
main()
1.5.3 练习3
import pymysql
def coperation_db():
'''
此时运行带,会发现数据没有插入表中,因为:修改数据需要提交事务
注意:当没有提交事务的时候,执行了插入语句,这个数据实际上内存中,没有提交。所以id是自增的
注意:修改数据需要提交事务
:return:
'''
# 1.连接数据库
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='111111',
db='day18'
)
# 2.创建游标
cs = conn.cursor()
# 3.定义sql语句
sql = "insert into students (name) values ('James');"
# 4.执行sql语句
cs.execute(sql)
# 5.通过连接对象调用 commit() 方法进行事务的提交
conn.commit()
# 6.关闭
cs.close()
conn.close()
if __name__ == '__main__':
coperation_db()
1.5.4 练习4
import pymysql
def coperation_db():
'''
通过一个commit可以插入多条数据吗?-->可以
:return:
'''
# 1.连接数据库
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='111111',
db='day18'
)
# 2.创建游标
cs = conn.cursor()
# 3.定义sql语句
sql = "insert into students (name) values ('Soft');"
# 4.执行sql语句
cs.execute(sql)
# 3.定义sql语句
sql = "insert into students (name) values ('Lucy');"
# 4.执行sql语句
cs.execute(sql)
# 5.通过连接对象调用 commit() 方法进行事务的提交
conn.commit()
# 6.关闭
cs.close()
conn.close()
if __name__ == '__main__':
coperation_db()
1.5.5 练习5
import pymysql
def coperation_db():
'''
需求:如果上面一条插入语句报错,数据还会被插入进去吗? -- 不会
解决: 异常捕获,数据没有插入到表中
:return:
'''
# 1.连接数据库
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='111111',
db='day18'
)
# 2.创建游标
cs = conn.cursor()
# 3.定义sql语句
sql = "insert into students (name) values ('Coco');"
# 4.执行sql语句
cs.execute(sql)
# 3.定义sql语句
sql = "insert into students (name) values ('Lily');"
# 4.执行sql语句
cs.execute(sql)
# 5.通过连接对象调用 commit() 方法进行事务的提交
conn.commit()
# 6.关闭
cs.close()
conn.close()
if __name__ == '__main__':
coperation_db()
1.5.6 练习6
import pymysql
def coperation_db():
'''
如果有一个报错(不管先后),都不用插入数据了。回滚。conn.rollback()
回滚的时候,虽然数据不会插入表中,但是实际上已经在内存中
:return:
'''
try:
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='111111',
db='day18'
)
cs = conn.cursor()
sql = "insert into students (name) values ('Marry');"
cs.execute(sql)
sql = "insert into students (name2) values ('Jojo');"
cs.execute(sql)
conn.commit()
cs.close()
conn.close()
except Exception as e:
print(e)
# 回滚,到内存中了
conn.rollback()
if __name__ == '__main__':
coperation_db()
'''
- 全部插入成功: 一个提交事务
- 执行失败:
- 失败全部: rollback() id+1
- 提交一部分:commit() id+1
- 前提: 第一条没问题的提交
'''