python读取mysql_python读写mysql

读取mysql数据

# -*- coding: utf-8 -*-

# 导入必要模块

import pandas as pd

from sqlalchemy import create_engine

# 初始化数据库连接，使用pymysql模块

# MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/student')

# 查询语句，选出employee表中的所有数据

sql = '''select * from data;'''

# read_sql_query的两个参数: sql语句， 数据库连接

df = pd.read_sql_query(sql, engine,index_col='index')

# 输出employee表的查询结果

print(df.shape)

pandas写入数据库

# coding:utf-8

import pandas as pd

from sqlalchemy import create_engine

# 初始化数据库连接，使用pymysql模块

# MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/student')

import datetime

start = datetime.datetime.now().strftime('%Y-%m-%d')

end = (datetime.datetime.now()+datetime.timedelta(days=100)).strftime('%Y-%m-%d')

# 新建pandas中的DataFrame, 只有id,num两列

# df = pd.DataFrame(data=np.random.randint(-100,100,(100,100)),index=pd.date_range('2018-1-1',periods=100,dtype='datetime64[ns]', freq='D'),columns=None,dtype=int)

# print(df.shape)

# 将新建的DataFrame储存为MySQL中的数据表，不储存index列

# df.to_sql('data', engine, if_exists='append',index= True)

def write_sql(name,data=pd.DataFrame()):

global engine

data.to_sql(name,engine,if_exists='fail',index=True)

调用上面封装方法：

# coding:utf-8

from mysql操作.write_SQL import write_sql

import os

import pandas as pd

path = r'your data path'

name_list = os.listdir(path)[0]

for item in [name_list]:

name = item.split('.')[0]

path_ = os.path.join(path,item)

print(path_)

with open(path_, 'rb') as f:

data = pd.read_csv(f, sep=',', header=None, index_col=None, na_values=['NULL'], na_filter=True, encoding='utf8')

write_sql(name,data)

print('打完收工！')