Python操作mysql记录

 mysql的下载和配置很麻烦，windows下下载安装版比较好，很多时候下载的解压包都自己安装不好，只能下载安装版。

mysql的基本操作小结，防止自己以后忘记，还要百度：

1、use databases，show databases，show tables

2、无则创建，有则跳过的if not。这里逗号，空格都不能用大写，很容易插入不进去，标题可以不用双引号引起来，sql语句举例。

create table  if not exists data1(id int(15) not null auto_increment,direction char(4) not null comment '资金流向，SHHK沪股通',SH_amount bigint(15) not null comment '限额，单位元',SH_balance bigint(15) not null comment '余额，单位元',SH_inflow bigint(15) not null comment '资金流入，负数表示资金流出，单位元',direction1 char(4) not null comment '资金流向,SZHK深股通',SZ_amount bigint(15) not null comment '限额，单位元',SZ_balance bigint(15) not null comment '余额，单位元',SZ_inflow bigint(15) not null comment '资金流入，负数表示资金流出，单位元',timestamp bigint(15) not null comment '爬数据的时间戳，单位秒',publish_timestamp char(25) not null comment '用于展示的时间戳，取60的整数，单位秒',primary key(id))"

3、插入语句用。

cursor.execute("insert into data1(direction,SH_amount,SH_balance,SH_inflow,direction1,SZ_amount,SZ_balance,SZ_inflow,timestamp,publish_timestamp) values ('%s','%d','%d','%d','%s','%d','%d','%d','%d','%s')"%(SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1))

4、全文索引以后再写

import requests
import random
import mysql.connector
import time
#使用mysql数据库表格格式 id，资金流向，沪股通限额，沪股通余额，沪股通资金流入，资金流向，深股通限额，深股通余额，深股通资金流入，爬数据的时间戳，展示时间格式
def insert(SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1):
    conn = mysql.connector.connect(user='root', password='xjxin2010', database='data')
    cursor = conn.cursor()
    #创建表格，如果表格存在就跳过继续向下执行
    cursor.execute("create table if not exists data1(id int(15) not null auto_increment,direction char(4) not null comment '资金流向，SHHK沪股通',SH_amount bigint(15) not null comment '限额，单位元',SH_balance bigint(15) not null comment '余额，单位元',SH_inflow bigint(15) not null comment '资金流入，负数表示资金流出，单位元',direction1 char(4) not null comment '资金流向,SZHK深股通',SZ_amount bigint(15) not null comment '限额，单位元',SZ_balance bigint(15) not null comment '余额，单位元',SZ_inflow bigint(15) not null comment '资金流入，负数表示资金流出，单位元',timestamp bigint(15) not null comment '爬数据的时间戳，单位秒',publish_timestamp char(25) not null comment '用于展示的时间戳，取60的整数，单位秒',primary key(id))")
    print(SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1)
    #向数据库插入数据
    try:
        cursor.execute("insert into data1(direction,SH_amount,SH_balance,SH_inflow,direction1,SZ_amount,SZ_balance,SZ_inflow,timestamp,publish_timestamp) values ('%s','%d','%d','%d','%s','%d','%d','%d','%d','%s')"%(SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1))
    except:
        print("插入失败，检查数据库")
    conn.commit()
    cursor.close()
def getdata():
    params={
    'randnum':random.random(),
    'ACTIONID':'sgtSsedQuery'
    }
    url_SH="http://yunhq.sse.com.cn:32041/v1/hkp/status/amount_status"
    url_SZ="http://www.szse.cn/szseWeb/FrontController.szse?"
    #获取沪股通数据
    res=requests.get(url=url_SH)
    SH_amount,SH_balance=res.json()['status'][3],res.json()['status'][4]
    SH_inflow=SH_amount-SH_balance
    #获取深股通数据、网站反馈时间
    r=requests.post(url=url_SZ,params=params)
    SZ_list=r.text.split('||')
    time1,SZ_amount,SZ_balance,SZ_inflow=SZ_list[0],int(float(SZ_list[1])*100000000),int(float(SZ_list[3])*1000000),int(float(SZ_list[2])*1000000)
    #获取爬虫运行时间戳
    a=int(time.time())
    SH,SZ="SHHK","SZHK"
    return SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1 
if __name__ == '__main__':
#获取数据
    SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1=getdata()
#插入数据
    insert(SH,SH_amount,SH_balance,SH_inflow,SZ,SZ_amount,SZ_balance,SZ_inflow,a,time1)