批量导入CSV到数据库（mssql mysql plsql）

批量导入CSV到数据库（mssql mysql plsql）

需求分析：

1. 为文件夹中的csv 按照文件名称导入到每个表中，表名为csv的文件名（csv结构无要求）；
2. 为文件夹中的所有csv导入同一个表中（csv文件结构相同）；

1、环境准备：

语言平台：python3.7
使用的语言库：d6tstack、pandas
测试数据库：mssql (sql server)

2、库工具下载：

d6tstack-0.2.0-py3-none-any.whl ：传送门
安装库：

pip install d6tstack-0.2.0-py3-none-any.whl
pip install pandas 
pip install pymssql

3、csv批量导入数据库

• 1 ）为文件夹中的csv 按照文件名字导入到每个表中，表名为csv的文件名（csv结构无要求）；

# -*- coding: UTF-8 -*-

import d6tstack
import glob
import pymssql
import pandas as pd

# 数据库连接语句
uri_mssql='mssql+pymssql://sa:Wm@12345@192.168.1.128/wmgis'

# 读取数据文件（csv）
csv = d6tstack.combine_csv.CombinerCSV(glob.glob('data/*.csv'))
# 数据入库 
for cs in csv.fname_list:
    # 读取csv数据
    df=pd.read_csv(cs)
    # 数据入库    
    # :param cs.split("\\")[1].replace(".csv",""): csv文件名称
    # :param 'wm' 数据库对象的集合, "replace" 替换已存在的表, cs csv数据文件
    d6tstack.utils.pd_to_mssql(df, uri_mssql, cs.split("\\")[1].replace(".csv",""),'wm',"replace",cs)
    

扩展：

mysql plsql 导入核心代码（替换相关内容即可实现）：
（源码已实现，无需更改源码，可直接实现）

d6tstack.utils.pd_to_psql(df, 'postgresql+psycopg2://usr:pwd@localhost/db', 'tablename')
d6tstack.utils.pd_to_mysql(df, 'mysql+mysqlconnector://usr:pwd@localhost/db', 'tablename')

4、存在的问题

巨坑：这个库没有实现，处于测验中：

b’Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (3).DB-Lib error message 20018
你很难发现问题，因为源码有问题（哈哈）：

1）问题1

• 更改173为：

 df[:0].to_sql(table_name, sql_engine, schema=schema_name, if_exists=if_exists, index=False)

2）问题2

• 更改186行：（语法错误）（核心代码）

sql_load = "BULK INSERT {} FROM '{}';".format()(table_name, tmpfile)

这样的语法存在明显的问题，含泪更改

sql_load = "BULK INSERT {} FROM '{}';".format(table_name, tmpfile)

如果这样就成功了，那就不能叫巨坑

3）问题3

sql语句太长，有没有想吐的感觉：
解决方法：更sql语句

• 181 行：

sql_load = "BULK INSERT {} FROM '{}'  WITH ( FIELDTERMINATOR =',',ROWTERMINATOR ='\\n',KILOBYTES_PER_BATCH=5000);".format(table_name, tmpfile)

4）问题4：

数据转换错误，数据不能导入，表创建成功（哈哈，看到木，表还是创建成功的）

• 数据表创建成功
  
  革命还没有胜利呀！数据才是关键：

5）问题5

数据入库：（修改源码）

• 169行

 import sqlalchemy

解决方案：

• 源码居然用到了这个库，那么直接使用这个库进行数据插入（希望就在眼前）
  修改源码：
  
  修改后源码：

# 导入库 支持
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
# 创建连接
sql_engine = sqlalchemy.create_engine(uri)
SessionFactory = sessionmaker(bind=sql_engine)
session = scoped_session(SessionFactory)
# 根据csv表头创建数据库结构
df[:0].to_sql(table_name, sql_engine, schema=schema_name, if_exists=if_exists, index=False)

logger = PrintLogger()
logger.send_log('creating ' + tmpfile, 'ok')
df.to_csv(tmpfile, na_rep='\\N', index=False)
logger.send_log('loading ' + tmpfile, 'ok')
if schema_name is not None:
    table_name = '{}.{}'.format(schema_name,table_name)
# 数据库核心执行语句 BULK INSERT 导入数据库
sql_load = "BULK INSERT {} FROM '{}'  WITH ( FIELDTERMINATOR =',',ROWTERMINATOR ='\\n',KILOBYTES_PER_BATCH=5000);".format(table_name, tmpfile)
# sql_engine.execute(sql_load)
# 执行数据库语句
session.execute(sql_load)
# 提交执行结果
session.commit()
# 移除临时文件
os.remove(tmpfile)

6）运行结果

查看运行结果：

2) 为文件夹中的所有csv导入同一个表中（csv文件结构相同）；（方法与方案一相同，直接贴修改后的源码）

# -*- coding: UTF-8 -*-


import d6tstack
import glob
import pymssql
import pandas as pd


uri_mssql='mssql+pymssql://sa:Wm@12345@192.168.1.128/wmgis'
# 读取csv文件路径
csv = d6tstack.combine_csv.CombinerCSV(glob.glob('data/*.csv'))
# 批量导入
is_succeed= csv.to_mssql_combine(uri_mssql, 'txt','wm','replace','/tmp/mysql.csv')

• 源码修改：

    def to_mssql_combine(self, uri, table_name, schema_name=None, if_exists='fail', tmpfile='mysql.csv'):
        """
        Load all files into a sql table using native postgres LOAD DATA LOCAL INFILE. Chunks data load to reduce memory consumption

        Args:
            uri (str): mysql mysqlconnector sqlalchemy database uri
            table_name (str): table to store data in
            schema_name (str): name of schema to write to
            if_exists (str): {‘fail’, ‘replace’, ‘append’}, default ‘fail’. See `pandas.to_sql()` for details
            tmpfile (str): filename for temporary file to load from

        Returns:
            bool: True if loader finished

        """
        if not 'mssql+pymssql' in uri:
            raise ValueError('need to use mssql+pymssql uri (conda install -c prometeia pymssql)')

        self._combine_preview_available()

        import sqlalchemy
        from sqlalchemy.orm import sessionmaker
        from sqlalchemy.orm import scoped_session

        sql_engine = sqlalchemy.create_engine(uri)

        self.df_combine_preview[:0].to_sql(table_name, sql_engine, schema=schema_name, if_exists=if_exists, index=False)

        if self.logger:
            self.logger.send_log('creating ' + tmpfile, 'ok')
        self.to_csv_combine(tmpfile, write_params={'na_rep':'\\N'})
        if self.logger:
            self.logger.send_log('loading ' + tmpfile, 'ok')
        if schema_name is not None:
            table_name = '{}.{}'.format(schema_name,table_name)
        SessionFactory = sessionmaker(bind=sql_engine)
        session = scoped_session(SessionFactory)
        sql_load = "BULK INSERT {} FROM '{}'WITH ( FIELDTERMINATOR =',',ROWTERMINATOR ='\\n',KILOBYTES_PER_BATCH=5000);".format(table_name, tmpfile)
        session.execute(sql_load)
        session.commit()

        os.remove(tmpfile)

        return True

写在最后：数据库操作也可实现：
少量文件使用数据库语句操作（只实践了sqlserver）

BULK INSERT wm.txt FROM '/tmp/mysql.csv' WITH ( FIELDTERMINATOR =',',ROWTERMINATOR ='\n',KILOBYTES_PER_BATCH=5000);

5、声明

1）文章来源于实践，方法经测试和验证，存在任何疑问或问题，请留言！感谢您的阅读！
2）原创，转载请标注来源! 感谢您的阅读！