gpt4 book ai didi

python - 将大型 Pandas DataFrame 写入 SQL Server 数据库

转载 作者:太空狗 更新时间:2023-10-29 18:02:53 24 4
gpt4 key购买 nike

我有 74 个相对较大的 Pandas DataFrame(大约 34,600 行和 8 列),我试图尽快将它们插入到 SQL Server 数据库中。在做了一些研究之后,我了解到好的 ole pandas.to_sql 函数不适用于向 SQL Server 数据库中进行如此大的插入,这是我最初采用的方法(非常慢 - 将近一个小时应用程序完成与使用 mysql 数据库时大约 4 分钟。)

This article ,以及许多其他 StackOverflow 帖子都帮助我指明了正确的方向,但是我遇到了障碍:

出于上面链接中解释的原因,我正在尝试使用 SQLAlchemy 的核心而不是 ORM。因此,我使用 pandas.to_dict 将数据帧转换为字典,然后执行 execute()insert():

self._session_factory.engine.execute(
TimeSeriesResultValues.__table__.insert(),
data)
# 'data' is a list of dictionaries.

问题是插入没有得到任何值——它们显示为一堆空括号,我得到这个错误:

(pyodbc.IntegretyError) ('23000', "[23000] [FreeTDS][SQL Server]Cannot
insert the value NULL into the column...

我传入的字典列表中有值,所以我不明白为什么没有显示这些值。

编辑:

这是我要讲的例子:

def test_sqlalchemy_core(n=100000):
init_sqlalchemy()
t0 = time.time()
engine.execute(
Customer.__table__.insert(),
[{"name": 'NAME ' + str(i)} for i in range(n)]
)
print("SQLAlchemy Core: Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")

最佳答案

我有一些不幸的消息要告诉你,SQLAlchemy 实际上并没有为 SQL Server 实现批量导入,它实际上只是执行 to_sql 正在执行的同样缓慢的单个 INSERT 语句。我会说您最好的选择是尝试使用 bcp 命令行工具编写脚本。这是我过去使用过的脚本,但不能保证:

from subprocess import check_output, call
import pandas as pd
import numpy as np
import os

pad = 0.1
tablename = 'sandbox.max.pybcp_test'
overwrite=True
raise_exception = True
server = 'P01'
trusted_connection= True
username=None
password=None
delimiter='|'
df = pd.read_csv('D:/inputdata.csv', encoding='latin', error_bad_lines=False)



def get_column_def_sql(col):
if col.dtype == object:
width = col.str.len().max() * (1+pad)
return '[{}] varchar({})'.format(col.name, int(width))
elif np.issubdtype(col.dtype, float):
return'[{}] float'.format(col.name)
elif np.issubdtype(col.dtype, int):
return '[{}] int'.format(col.name)
else:
if raise_exception:
raise NotImplementedError('data type {} not implemented'.format(col.dtype))
else:
print('Warning: cast column {} as varchar; data type {} not implemented'.format(col, col.dtype))
width = col.str.len().max() * (1+pad)
return '[{}] varchar({})'.format(col.name, int(width))

def create_table(df, tablename, server, trusted_connection, username, password, pad):
if trusted_connection:
login_string = '-E'
else:
login_string = '-U {} -P {}'.format(username, password)

col_defs = []
for col in df:
col_defs += [get_column_def_sql(df[col])]

query_string = 'CREATE TABLE {}\n({})\nGO\nQUIT'.format(tablename, ',\n'.join(col_defs))
if overwrite == True:
query_string = "IF OBJECT_ID('{}', 'U') IS NOT NULL DROP TABLE {};".format(tablename, tablename) + query_string


query_file = 'c:\\pybcp_tempqueryfile.sql'
with open (query_file,'w') as f:
f.write(query_string)

if trusted_connection:
login_string = '-E'
else:
login_string = '-U {} -P {}'.format(username, password)

o = call('sqlcmd -S {} {} -i {}'.format(server, login_string, query_file), shell=True)
if o != 0:
raise BaseException("Failed to create table")
# o = call('del {}'.format(query_file), shell=True)


def call_bcp(df, tablename):
if trusted_connection:
login_string = '-T'
else:
login_string = '-U {} -P {}'.format(username, password)
temp_file = 'c:\\pybcp_tempqueryfile.csv'

#remove the delimiter and change the encoding of the data frame to latin so sql server can read it
df.loc[:,df.dtypes == object] = df.loc[:,df.dtypes == object].apply(lambda col: col.str.replace(delimiter,'').str.encode('latin'))
df.to_csv(temp_file, index = False, sep = '|', errors='ignore')
o = call('bcp sandbox.max.pybcp_test2 in c:\pybcp_tempqueryfile.csv -S "localhost" -T -t^| -r\n -c')

关于python - 将大型 Pandas DataFrame 写入 SQL Server 数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33816918/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com