Python 连接MySQL
数据库技术(例如MySQL)在气象业务和其他商业行业中都有着广泛的应用，气象与电网结合的大项目甚至都用上了hadoop分布式存储，Hadoop中的Hive组件和数据库在语法上高度相似。
传统运行mysql脚本的方法是在一些流行的IDE中运行，比如Mysql8.0自带的 workbench,还有DBeaver等。
PyMySQL是在Python3.x版本中用于连接MySQL服务器的一个库，Python2中使用mysqldb。在此文中以PyMysql为例，在python中运行mysql代码， 可以充分结合python的灵活性和mysql的强大查询能力提高生产力。比如mysql中很难循环遍历，我们可以利用python对某个表名列表进行遍历。又比如，一些指标计算用pandas包和自己写的模块非常高效，若用mysql则晦涩难懂了。
MySQL 8.0自带的 Workbench 界面
pymysql连接MySQL
import pymysql
import pymysql.cursors
cnn= pymysql.connect(host='localhost', #IP
user='root', #用户名
password='xxxx', #密码
port=3306, #端口号
charset='utf8')#注意是utf8不是utf-8
# 使用cursor()方法获取操作游标
cursor = cnn.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
result = cursor.fetchone()
print ("Database version : %s " % result)Database version : 8.0.15
pymysql创建表格
#https://www.runoob.com/python3/python3-mysql.html
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","deepwind","demo" ) #,"TESTDB"
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql_1 = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
result1=cursor.fetchall()
sql_2="SHOW TABLES"
cursor.execute(sql_2)
result2=cursor.fetchall()
print('result1:',result1)
print('result2:',result2)
# 关闭数据库连接
db.close()result1: () result2: (('demo',), ('employee',))
单次插入数据
#!/usr/bin/python3
import pymysql
# 打开数据库连接,xxx是你的密码
db = pymysql.connect("localhost","root","xxx","demo" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql_1 = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES ('%s', '%s', %s, '%s', %s)" % \
('Mac', 'Mohan', 20, 'M', 2000)
sql_2 = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES ('%s', '%s', %s, '%s', %s)" % \
('Johon', 'Snow', 28, 'M', 9000)
try:
cursor.execute(sql_1)
cursor.execute(sql_2)
# 执行sql语句
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
#https://www.runoob.com/python3/python3-mysql.html批量插入
# https://blog.csdn.net/ATOOHOO/article/details/88173151 pymysql单条插入数据和批量插入数据：
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","xxx","demo")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES (%s,%s,%s,%s,%s)"
# 区别与单条插入数据，VALUES ('%s', '%s', %s, '%s', %s) 里面不用引号
val = (('li', 'si', 16, 'F', 1000),
('Bruse', 'Jerry', 30, 'F', 3000),
('Lee', 'Tomcat', 40, 'M', 4000),
('zhang', 'san', 18, 'M', 1500))
try:
# 执行sql语句
cursor.executemany(sql,val)
# 提交到数据库执行
db.commit()
except:
# 如果发生错误则回滚
db.rollback()
# 关闭数据库连接
db.close()筛选出收入大于1000的人
#!/usr/bin/python3
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","xxx","demo" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
# 关闭数据库连接
db.close()fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0 fname=Johon,lname=Snow,age=28,sex=M,income=9000.0
配合sqlalchemy和pandas 输出dataframe并保存
from sqlalchemy import create_engine
import pandas as pd
engine=create_engine('mysql+pymysql://root:xxxx@localhost:3306/demo')
# 查询语句，选出employee表中的所有数据
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df)
df.to_csv('employees.csv',encoding='utf-8') #保存
有了datafram格式的数据，业务人员就可以利用pandas 对数据进行各种骚操作了。
商业实战
注：本部分偏商业，考虑转行数据分析的同学可以看看，不转行的请忽略（特别是那些不想考研又不想去偏远气象局或者觉得个人在气象行业发展受限的本科生同学。小编作为一个江苏人，个人表示很难在江苏各个气象局呆下去吧，当然这只是对我个人而言，气象子女除外咯）
一、 了解某公司某数据库内含有的表（表名为杜撰）
import pymysql
# 打开数据库连接
db = pymysql.connect("ip地址","账户名","密码","库名" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
sql_1="SHOW TABLES"
cursor.execute(sql_1)
result1=cursor.fetchall()
print('result1:',result1)
# 关闭数据库连接
db.close()result1: (('dim_category',), ('dim_date',), ('dim_goods',), ('dim_member',), ('dim_shop',), ('fct_sales',), ('fct_sales_item',))
从上述结果中我们可以知道Market数据库中含有七张表：商品种类表，日期表，商品表，会员表，店铺表，销售订单表，销售订单明细表。
二 查看销售订单表和销售订单明细
可以看出两种表有共同的列 salesNo订单号，但是明细表多了goodsID 列 。好比我在NUIST零食店一共买了鸭肠、鸭血、鸭肝三种物品，但是这三样货是一起付款的，这只是一笔订单。所以我买它们的时候生成了一个salesID(3956756)，也就是我们付款后收银员给我们的小票。
三 实践任务：分析单品促销
大家一般都了解一些超市的套路，比如某几日该超市的大米特别特别便宜，甚至亏本，商家的目的是为了吸引人流量，导引顾客购买其他商品。大爷大妈辛辛苦苦来趟超市，不会只买大米吧。假如大米的商品ID号为4130085，接下来我就要找到 哪些订单中包含了大米， 也就是 哪些订单买大米的同时，还顺带买了其他商品，这些商品带来了多少销售额，这些都是领导最关心的问题了。
from sqlalchemy import create_engine
import pandas as pd
engine=create_engine('mysql+pymysql://<账户名>:<密码>$@<ip地址>:3306/<数据库名称>')
#第一步：取包括这个商品的订单号 ，将fct_sales_item 另取名为t1
sql ="""select distinct(t1.salesNo)
from dw.fct_sales_item t1
where dimDateID between '20170703' and '20170709'
and goodsID ='4130085';
"""
df= pd.read_sql_query(sql, engine)
print(df)
# 关闭数据库连接
db.close()有了包含大米的订单号（从明细表fct_sales_ item（别名t1）中得到），这些订单号取名为t2 ,与fct_sales(别名t3)表连接，筛选出这些订单号, 得到了新表，对新表进行订单号分组统计。
