MySQL操作

讀取數(shù)據(jù)
read_sql()

讀取csv插入MySQL
LOAD DATA LOCAL INFILE 'path' INTO TABLE XXX

Python鏈接

import pandas
from sqlalchemy import create_engine

engine =create_engine('mysql+mysqlconnector://root:host/database')

data= pandas.read_sql("""select * from table""",con=engine)

導出MySQL

to_sql導出DataFrame中的數(shù)據(jù)到數(shù)據(jù)庫
to_sql(tableName,con=數(shù)據(jù)庫鏈接)

import pandas
from sqlalchemy import create_engine

connection =create_engine('mysql+mysqlconnector://root:host/database')

data= pandas.read_sql("""select * from table""",con=connection)

data.to_sql("Table",index=False,con=engine,if_exists='append')

整合MySQL

import pandas
from sqlalchemy import create_engine
import os

connection =create_engine('mysql+mysqlconnector://root:host/database')

data= pandas.read_sql("""select * from table""",con=connection)

data.to_sql("Table",index=False,con=engine,if_exists='append')

# 讀取本地文檔文件夾
rootDir = "Users/yky/Desktop/123"

def exeSQL(sql):
        print("exeSQL:"+sql)
        connection.execute(sql)

for fileName in os.listdir(rootDir):
        path = os.path.join(rootDir,fileName)
        if ".txt" in fileName:
                print(path)
                path = path.replace("\\","\\\\")
                # 拿到文件名中的時間字段
                datetime = fileName[0:8]
                #建表、導入數(shù)據(jù)到表中
                sql = "drop table if exists %s" %(tableName)
                sql="""create table %s(訂單編號 INT,訂購日期 DATE,用戶ID INT,產(chǎn)品 CHAR(8),`單價(元)`,數(shù)量 INT,訂購金額 INT)""" %(tableName)
                exeSQL(sql)
                sql ="""LOAD DATA LOCAL INFILE '%s' 
                            INTO TABLE '%s' 
                            CHARACTER SET 'UTF8'
                            COLUMNS TERMINATED BY','
                            LINES TERMINATED BY '\\n'
                            IGNORE 1 LINES
                 """ %(path,tableName)
                  exeSQL(sql)
                 
                #建立日訂購統(tǒng)計表
                sql = """
                create table if not exists 日訂購統(tǒng)計表(
統(tǒng)計日期 DATE,訂購人數(shù) INT, 訂購次數(shù)INT,人均訂購金額 DOUBLE,訂購總額 DOUBLE)"""              
                exeSQL(sql)
                 
statDay =pandas.read_sql(
        'select * from  日訂購統(tǒng)計表',
        con = connection)
print (statDay)