pandas与数据库的操作

#-*- coding: utf-8 -*-
import pandas as pd
import numpy as np
import pymysql
import sqlalchemy
from sqlalchemy import create_engine

pd.set_option('display.max_columns',None)
pd.set_option('display.max_rows',None)
pd.set_option('display.unicode.east_asian_width',True)
pd.set_option('display.width',None)
#python操作数据库
def mysql():
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = '2385455226',            #密码在输入的时候一定是字符串，不加引号，计算机会自动将其处理为int数据类型处理
        db = 'laowang',
        charset = 'utf8'
    )
    cursor = conn.cursor()
    sql = 'select * from scores;'
    cursor.execute(sql)
    data = list(cursor.fetchall())
    for i in data:
        print(i,type(i))
    print(data,type(data))
    print(data[2])
#pandas处理数据
def get_data():

    df1 = pd.read_excel(r'C:\Users\lovel\Desktop\数据分析软件应用\同花顺数据.xlsx')
    df2 = pd.read_excel(r'C:\Users\lovel\Desktop\数据分析软件应用\同花顺数据.xlsx')
    df = pd.concat([df1,df2],axis=0).reset_index(drop=True)
    # print(df,type(df))
    df = df.iloc[:len(df),1:len(df.columns)]
    num = pd.DataFrame(np.arange(1,101))
    print(num)
    df = pd.concat([df,num],axis=1).rename(columns={0:'序号'})
    print(df)
    df.to_sql()
    # print(df['板块'],type(df['板块']))
    # print(df.iloc[0:1,0:1])
    # print(df.iloc[0:1, 1:2])
    # print(df.iloc[0:1, 2:3])
    # print(df.iloc[0:1, 3:4])
    # print(df.iloc[0:1, 4:5])
    # for i in df:
    #     print(i,type(i))
#利用pandas操作数据库
def get_sql_data():
    conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        password = '2385455226',
        db = 'laowang',
        charset = 'utf8'
    )
    sql = 'select * from scores;'
    df = pd.read_sql(sql,conn)
    print(df)
#利用pandas将数据写入mysql中
def post_sql_data():
    df = pd.read_excel(r'C:\Users\lovel\Desktop\数据分析软件应用\同花顺数据.xlsx',sheet_name='Sheet1')
    print(df)
    engine = create_engine('mysql+pymysql://root:2385455226@localhost/laowang?charset=utf8')
    df.to_sql('stock_info',engine)
    print('完成')


if __name__ == '__main__':
    # mysql()
    # get_data()
    # get_sql_data()
    post_sql_data()