脚本功能
1 利用python 读取csv文件数据 输出 并写入到mssql sqlserver 数据库
2 利用python 从mssql sqlserver 数据库读取数据并写入到csv文件中
3 用到的三方库
pandas 安装
pip install pandas
sqlalchemy 安装
pip install sqlalchemy
pymssql 安装
pip install pymssql
4 pandas.read_csv 分段读取方法
pandas.read_csv() 文件 chunksize=5000 逐步执行 防止python 报 out of memory错误
5 python脚本 源码
import pandas as pd
from sqlalchemy import create_engine
def imtomssql():
engine = create_engine('mssql+pymssql://sa:xxxxx@127.0.0.1/rujia', connect_args={'charset': 'utf8'},
echo=True)
path = open(unicode('E:/泄露互联网/rujia2000w/1-200W.csv', 'UTF8'), 'r')
df = pd.read_csv(path, sep=',', encoding='utf-8', chunksize=5000, iterator=True, low_memory=False)
for chunk in df:
chunk.to_sql('rujia-python', engine, if_exists='append', index=False)
print(" running Write to sqlserver...")
print("Write to sqlserver successfully!")
def readfrommysql():
engine = create_engine('mssql+pymssql://sa:xxxxx@127.0.0.1/rujia', echo=True)
sql = '''
select * from [1000W-1200W] where Name='王X伟'
'''
df = pd.read_sql_query(sql, engine)
print(df)
df.to_csv('export.csv', encoding='utf-8')
print('Read from and write to mssql sqlserver table successfully!')
if __name__ == "__main__":
imtomssql()