最近我一直试图连接到Microsoft上的SQL数据库。我正在尝试一种新的方法,可以从熊猫数据中上传多列数据。这是一种新的方法,因为这不是我以前做过的事情。
我得到以下错误:
Traceback (most recent call last):
File "src/pymssql/_pymssql.pyx", line 460, in pymssql._pymssql.Cursor.execute
File "src/pymssql/_mssql.pyx", line 1084, in pymssql._mssql.MSSQLConnection.execute_query
File "src/pymssql/_mssql.pyx", line 1115, in pymssql._mssql.MSSQLConnection.execute_query
File "src/pymssql/_mssql.pyx", line 1248, in pymssql._mssql.MSSQLConnection.format_and_run_query
File "src/pymssql/_mssql.pyx", line 1786, in pymssql._mssql.check_cancel_and_raise
File "src/pymssql/_mssql.pyx", line 1832, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (206, b'Operand type clash: int is incompatible with textDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "sql_import.py", line 109, in <module>
cursor.execute(sql_insert_clean[0], sql_tuple)
File "src/pymssql/_pymssql.pyx", line 478, in pymssql._pymssql.Cursor.execute
pymssql._pymssql.OperationalError: (206, b'Operand type clash: int is incompatible with textDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')下面是我正在使用的脚本:
import pymysql
#Set up SQL server
connection = pymssql.connect("xxx.database.windows.net", "xxx", "xxx", "coiner")
cursor = connection.cursor()
""" Some processing done here that I will skip"""
--- ... ---
#get data into tuples
sql_data = tuple(map(tuple, sql_dataframe.values))
#store into SQL database
for sql_tuple in sql_data:
print(len(sql_tuple))
cursor.execute(sql_insert_clean[0], sql_tuple)
connection.commit()sql_insert_clean[0]看起来如下所示:
插入StocksRealTime (change,changePercent,dayVolume,exchange,id,marketHours,price,priceHint,quoteType,time,lastSize,currency,circulatingSupply,dayHigh,dayLow,time,lastMarket,marketcap,shortName,volAllCurrencies,vol_24hr)值(%s,%s,%s,%s)
它上传至少420行数据,在421行上抛出错误。我检查过这一排,我看不出它可能出了什么问题。
另外,这个过程非常慢!上传400行花了大约1分钟。我的实际数据集是大约100万行数据,那么如何提高速度呢?
439:442行周围的数据集示例
439 440 441 442 types
change 5.279999 116.0298 7 39.59998 object
changePercent 2.06775 5.425476 3.660322 5.439557 object
dayVolume 54856566.0 7831598.0 40869414.0 56702940.0 float64
exchange NMS NMS NMS NMS object
id MSFT AMZN FB TSLA object
marketHours 1 1 1 1 int64
price 260.63 2254.64 198.24 767.6 object
priceHint 4 4 4 4 int64
quoteType 8 8 8 8 int64
time 3304942412000 3304942412000 3304942414000 3304942404000 int64
lastSize 200.0 0.0 0.0 0.0 float64
currency object
circulatingSupply 0.0 0.0 0.0 0.0 float64
dayHigh object
dayLow object
fromcurrency object
lastMarket object
marketcap 0.0 0.0 0.0 0.0 float64
shortName object
volAllCurrencies 0.0 0.0 0.0 0.0 float64
vol_24hr 0.0 0.0 0.0 0.0 float64发布于 2022-05-17 07:18:25
需要很长时间才能搜索到多种方法,才能使其快速、有效地工作。
我遇到了多个错误,下面是几个例子:
(SQLExecDirectW)').
(SQLDriverConnect)").
第一个原因是在pd.DataFrame.to_sql中有参数fast_executemany = True,而在SQLAlchemy引擎中也有fast_executemany = True。因此,删除已工作的method参数。
第二个错误是由'mysql+pyodbc://'而不是'mssql+pyodbc://'引起的。
第三个问题可能与第二个问题相同,但我已经转向了另一个解决方案,因此我没有检查它。
对我有用的是以下几点:
engine = create_engine(
'mssql+pyodbc://' +
user + ':' +
passw + '@' +
host + ':' +
str(port) + '/' +
database +f'?driver=ODBC+Driver+18+for+SQL+Server' ,
echo=False,
connect_args={"timeout":30},
pool_pre_ping=True, fast_executemany = True)
--- EXTRA ARGUMENTS HERE ---
conn = engine.connect()
sql_dataframe.to_sql('StocksRealTime', con=engine,index=False, if_exists='replace')这也比所选择的职位快很多倍,而且没有任何错误。
https://stackoverflow.com/questions/72264792
复制相似问题