Python查询MySQL数据表记录
代码示例:
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='root',
db='database',
charset='utf8')
cur = conn.cursor() # 定义游标
strSQL = "select * from student"
cur.execute(strSQL) # 执行SQL语句
rows = cur.fetchall() # 获取游标所有行记录,返回元组变量
print("学号\t\t\t姓名\t\t年龄")
print("--------------------------------------------")
for r in rows: # 遍历元组
print("%s\t%s\t\t%s" % (r[0], r[1], r[2]))
# 用完,立即关闭对象,先关游标,再关连接
cur.close()
conn.close()
运行结果:
Python更新MySQL数据表记录
代码示例:
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='root',
db='database',
charset='utf8')
cursor = conn.cursor() # 获取一个光标
try:
sql = 'insert into student(id, name, age, sex, birthDate) values(%s,%s,%s,%s,%s);'
data = [('231124', '林恩', 23, '男', '1999-4-4')]
cursor.executemany(sql, data) # 拼接并执行sql语句
conn.commit() # 涉及写操作要注意提交
print("数据插入成功!影响行数:", cursor.rowcount)
except pymysql.Error as e:
conn.rollback()
print("插入错误!错误信息:", e.args[1])
cursor.close()
conn.close() # 关闭光标和连接
运行结果:
查询股票代码为sh600006(东风汽车)的交易日期、收盘价,显示3条记录
代码示例:
from sqlalchemy import create_engine # 主要用于建立数据库连接引擎
import pandas as pd # 这个库,可以对数据库进行读写操作
connStr = "mysql+pymysql://root:root@127.0.0.1:3306/stock1?charset=utf8"
engine = create_engine(connStr) # 数据连接引擎
sql = "select cDay,mClose from trDay where cStockNo ='sh600001' limit 6,3"
trDay = pd.read_sql(sql, con=engine) # 返回为DataFrame对象
print(trDay.values) # trDay.values 为二维数组
print("返回记录行数:", trDay.shape[0])
运行结果:
查询股票代码为sh600001的交易数据,查看DataFrame的属性
代码示例:
from sqlalchemy import create_engine
import pandas as pd
connStr = "mysql+pymysql://root:root@127.0.0.1:3306/stock1?"
engine = create_engine(connStr)
sql = "select cDay,mClose from trDay where cStockNo ='sh600001' limit 6,3"
trDay = pd.read_sql(sql, con=engine) # 返回 DataFrame 对象
i = 0
for row in trDay.values: # dataFrame.values 为二维数组
i = i + 1
print(i, row[0], row[1])
print(trDay['mClose']) # 单列所有行的访问
print(trDay['mClose'][0:2]) # 单列多行的访问
运行结果:
通过对象DataFrame,获取多列多行数据,如东风汽车日交易数据
代码示例:
from sqlalchemy import create_engine
import pandas as pd
connStr = "mysql+pymysql://root:root@127.0.0.1:3306/stock1?charset=utf8"
engine = create_engine(connStr)
sql = "select cDay,mOpen,mHigh,mLow,mClose from trDay where cStockNo='SH600006'"
trDay = pd.read_sql(sql, con=engine) # 东风汽车(SH600006) 日交易数据
trDay.to_excel('./trDay.xlsx') # 将查询结果直接保存为excel文件
print(trDay[['cDay', 'mOpen', 'mHigh', 'mLow', 'mClose']][:3]) # 前3行
运行结果:
利用DataFrame,直接绘制股票价格趋势图、移动平均
代码示例:
from sqlalchemy import create_engine
import matplotlib.pyplot as plt;
import pandas as pd
connStr = "mysql+pymysql://root:root@127.0.0.1:3306/stock1?charset=utf8"
engine = create_engine(connStr)
sql = "select cDay,mClose from trDay where cStockNo ='sz000561' and cDay>'20130101' and cDay<'20130830' order by cDay"
trDay = pd.read_sql(sql, con=engine) # 烽火电子日收盘价格
plt.figure(figsize=(8, 4), dpi=80)
trDay['mClose'].plot(title="sz000561", color='b')
trDay['mClose'].rolling(10).mean().plot(color='r') # 10天移动平均
rows = trDay.shape[0]
d = trDay['cDay'][0:rows:10] # 步长为10
plt.xticks(range(0, rows, 10), d, rotation=45, fontsize=12) # 旋转45度
plt.legend(['mClose', 'MA(10)']);
plt.show()
运行结果:
利用对象DataFrame,直接绘制股票成交量条形图
代码示例:
from sqlalchemy import create_engine
import matplotlib.pyplot as plt;
import pandas as pd
connStr = "mysql+pymysql://root:root@127.0.0.1:3306/stock1?charset=utf8"
engine = create_engine(connStr)
sql = "select cDay,mOpen,mClose,iVol from trDay where cStockNo ='sz000561' and cDay between '20130102' and '20130222' order by cDay"
trDay = pd.read_sql(sql, con=engine) # 烽火电子日收盘价格
plt.figure(figsize=(8, 4), dpi=80)
c = [] # 颜色列表
for index, row in trDay.iterrows():
if (row['mClose'] >= row['mOpen']):
c.append('red') # 收盘价不低于开盘价,则红色
else:
c.append('green') # 绿色
plt.bar(trDay['cDay'], trDay['iVol'], width=0.5, color=c)
rows = trDay.shape[0];
d = trDay['cDay'][0:rows:5]
plt.xticks(range(0, rows, 5), d, rotation=45, fontsize=12) # 旋转45度
plt.legend(['sz000561:valume']);
plt.show()
运行结果:
使用read_csv读取“深指日交易数据.csv”
代码示例:
import pandas as pd
data = pd.read_csv('深指日交易数据.csv', encoding='gbk')
print(type(data)) # <class 'pandas.core.frame.DataFrame'>
print('使用read_csv读取的交易数据表的长度为:', len(data)) # 输出:9
print(data.columns) # 返回所有的列名对象,相当于字典的键名
# 默认把第0行,作为列名
i = 0
for row in data.values: # 显示所有行记录
i = i + 1
print(i, row[0], row[1], row[2], row[3], row[4], row[5])
运行结果:
使用to_csv存储《深指日交易数据.csv》
代码示例:
import pandas as pd
import os
order = pd.read_csv('深指日交易数据.csv', encoding='gbk')
print('写入文本文件前目录内文件列表为:\n', os.listdir('./'))
# 将order以csv格式存储
order.to_csv('./file/orderInfo.csv', sep=';', index=False)
print('写入文本文件后目录内文件列表为:\n', os.listdir('./file'))
运行结果:
使用read_excel读取文件“人均国民收入.xls”
代码示例:
import pandas as pd
F = './人均国民收入.xls'
data = pd.read_excel(F, '收入与消费')
data = pd.read_excel(F, 0) # 也可sheet 索引
print('记录条数:', len(data))
# 返回所有的列名对象,相当于字典的键名,默认把第0行,作为列名
print(data.columns)
print(data.values) # 返回所有的行
运行结果:
Pandas读取数据,排序
代码示例:
import pandas as pd
wb = pd.read_excel('./各国GDP(2001-2011).xls')
d = wb.iloc[0:11, 0:4] # [行号,列号],d为'pandas.core.frame.DataFrame'
print(d)
print(d.columns)
s = d.sort_values(by='国家', ascending=True) # 对列名“国家”按升序
print(s)
s = d.sort_values(by='2001', ascending=False) # 对列名“2001”按降序
print(type(s))
运行结果:
通过条形图迭代,绘制2000-2011年,GDP最大的10个国家变化情况
代码示例:
import matplotlib.pyplot as plt
import pandas as pd
import imageio
plt.rcParams['font.sans-serif'] = 'SimHei' # 设置中文显示
wb = pd.read_excel('./各国GDP(2001-2011).xls')
d = wb.iloc[0:11, 0:12] # 行 0:11,列:0:12
plt.figure(figsize=(6, 4), dpi=120)
plt.ion() # 打开交互模式
image_list = []
for i in range(1, 12): # 年份:2001年->2011年
w = str(d.columns[i]) # w 为年份
s = d.sort_values(by=w, ascending=True) # 按年份数据排升序
x = s.iloc[:, i] # 第 i 列年份 ,前11个国家数据
y = s.iloc[:, 0] # 国家名称
c = list(map(lambda x: 'red' if x == '中国' else 'blue', y))
plt.clf() # 清除画布
plt.title('各国历年GDP动态条形图(单位:亿美元)', fontsize=12)
plt.barh(y, x, height=0.6, color=c) # 横放条形图函数 barh
for a, b in zip(x, y):
plt.text(a + 0.6, b, '%.0f' % a, fontsize=12)
plt.legend([w + '年'], fontsize=12)
plt.pause(1) # 隔1秒,停止一下
plt.savefig('temp.png')
image_list.append(imageio.imread('temp.png'))
plt.ioff() # 关闭交互模式
imageio.mimsave('./bar_test.gif', image_list, duration=1)
运行结果: