Python对数据库操作（以拉取股票入库为例）

获取股票数据的时候我们采用的是baostack。您需要安装baostack的python包，除此之外我们采用的数据库驱动为pymysql，orm框架采用sqlalchemy。这里不讲解具体的安装过程，网上资料很多。

1.pymysql进行简单的增删改查操作

import pymysql.cursors


# 连接数据库
connect = pymysql.Connect(
    host='localhost',
    port=3307,
    user='root',
    passwd='tianjingle',
    db='pymysql',
    charset='utf8'
)
# 获取游标
cursor = connect.cursor()
# 插入数据
sql = "INSERT INTO test (id, name, age) VALUES ( '%s', '%s', %.2f )"
data = (7, 'zhangsan', 24)
cursor.execute(sql % data)
connect.commit()
print('成功插入', cursor.rowcount, '条数据')


# 修改数据
sql = "UPDATE test SET name = '%s' WHERE id = %i "
data = ('zhangsan', 1)
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')


# 查询数据
sql = "SELECT id,name,age FROM test WHERE id = '%i' "
data = (1)
cursor.execute(sql % data)
for row in cursor.fetchall():
    print(row)
    # print("Name:%s\tSaving:%.2f" % row)
print('共查找出', cursor.rowcount, '条数据')


# 删除数据
sql = "DELETE FROM test WHERE id = %i LIMIT %d"
data = (2, 1)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')


# 事务处理
sql_1 = "UPDATE test SET age = age + 1000 WHERE id = 1 "
sql_2 = "UPDATE test SET age = age + 1000 WHERE id = 1 "
sql_3 = "UPDATE test SET age = age + 2000 WHERE id = 1 "


try:
    cursor.execute(sql_1)  # 储蓄增加1000
    cursor.execute(sql_2)  # 支出增加1000
    cursor.execute(sql_3)  # 收入增加2000
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)


# 关闭连接
cursor.close()
connect.close()

2.使用sqlalchemy+baostack获取股票数据并保存到数据库中

import pandas as pd
from sqlalchemy import create_engine
import baostock as bs


lg = bs.login()
rs = bs.query_history_k_data_plus("sh.600567",
    "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST",
    start_date='1999-07-01', end_date='2020-10-18',
    frequency="d", adjustflag="3")


#### 打印结果集 ####
data_list = []
while (rs.error_code == '0') & rs.next():
    data_list.append(rs.get_row_data())
result = pd.DataFrame(data_list, columns=rs.fields)


##将数据写入mysql的数据库，但需要先通过sqlalchemy.create_engine建立连接,且字符编码设置为utf8
engine = create_engine('mysql+pymysql://root:tianjingle@localhost:3307/pymysql?charset=utf8')
#插入数据库
result.to_sql(name = 'sh600567',con = engine,if_exists = 'append',index = False,index_label = False)

3.使用pymysql读库并转成DataFrame

import pymysql.cursors
import pandas as pd
# 连接数据库
connect = pymysql.Connect(
    host='localhost',
    port=3307,
    user='root',
    passwd='tianjingle',
    db='pymysql',
    charset='utf8'
)
# 获取游标
cursor = connect.cursor()
# 查询数据
sql = "SELECT * FROM sh600567 "
data = (1)
cursor.execute(sql)
fs = cursor.description
filelds=[]
for field in fs:
    filelds.append(field[0])
rs=cursor.fetchall()
data_list = []
result = pd.DataFrame(list(rs),columns=filelds)
print(result)
# 关闭连接
cursor.close()
connect.close()

说明一下我为什么只是把orm框架当作存储作用的原因是：我喜欢写sql，使用orm框架的学习成本太大。为了避免数据返回的格式化不统一的问题可以使用第三小节转成DataFrame，这样就不存在这个问题了。