python mysql 简书_Python操作MySQL

MySQL操作

读取数据

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中的数据到数据库

to_sql(tableName,con=数据库链接)

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]

#建表、导入数据到表中

sql = "drop table if exists %s" %(tableName)

sql="""create table %s(订单编号 INT,订购日期 DATE,用户ID INT,产品 CHAR(8),`单价(元)`,数量 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)

#建立日订购统计表

sql = """

create table if not exists 日订购统计表(

统计日期 DATE,订购人数 INT, 订购次数INT,人均订购金额 DOUBLE,订购总额 DOUBLE)"""

exeSQL(sql)

statDay =pandas.read_sql(

'select * from 日订购统计表',

con = connection)

print (statDay)