#!/usr/bin/python

# -*- coding: UTF-8 -*-

import MySQLdb

import tushare as ts

from sqlalchemy import create_engine

code = "002312"

#数据库链接参数

host = ‘192.168.0.165‘

port = 3306

user = ‘root‘

password = ‘qweqwe‘

database = ‘stock‘

charset = "utf8"

#建立数据库连接

conn = MySQLdb.connect(

host=host,

port=port,

user=user,

passwd=password,

db=database,

)

#获取游标

cur = conn.cursor()

#创建表的sql语句

create_table_sql = "create table if not exists code" + code + "(id int auto_increment,code int(6) zerofill,date date not null,open decimal(10,2) not null,high decimal(10,2) not null,close decimal(10,2) not null,low decimal(10,2) not null,volume decimal(10,2),turnover decimal(10,2),primary key (id))"

#执行sql语句

cur.execute(create_table_sql)

#关闭游标

cur.close()

#提交连接

conn.commit()

#断开连接

conn.close()

#获取股票历史k线数据

df = ts.get_hist_data(code)

#筛选数据，只获取open high close low volume turnover列，并到倒序排列

data = df.iloc[::-1, [0, 1, 2, 3, 4, 13]]

#为dataframe添加code列，因为数据库中需要这一列建立索引

data["code"] = code

# 创建数据库引擎

engine = create_engine(‘mysql://‘ + user + ‘:‘ + password + ‘@‘ + host + ‘/‘ + database + ‘?charset=‘ + charset)

#将数据存入数据库，如果表存在增量存储

data.to_sql(‘code‘+code, engine, if_exists=‘append‘)