时间戳

首页 > 解决方案 > 如何处理从 Dataframe 到 SQL 的大整数

python - 如何处理从 Dataframe 到 SQL 的大整数

问题描述

数据帧 DF_JSON 中的 StaffIdent 和 FirmIdent 是大整数。例如。StaffIdent 15423992539793905091 我想将数据从dataframe转换为SQL，当我将StaffIndent和FirmIndent视为字符串并将新数据与SQL中TEST表中的当前数据合并时就可以了。这有效：

from sqlalchemy import create_engine, types
import os
import logging
import json
import pandas as pd
import sqlalchemy 
import pandasql as ps

def upload_data(SQLServer,SQLDatabase,JSON_file):
    Table_Name='TEST'
    engine = create_engine('mssql+pyodbc://{0}/{1}?driver=SQL Server?Trusted_Connection=yes'.format(SQLServer,SQLDatabase))
    i=0
    for JSON_file in os.listdir('Data/'+Table_Name):
        logging.info('Debug: Reading file JSON and inserting into database: {0}'.format(JSON_file))
        JSON = json.load(open('Data/'+Table_Name+'/'+JSON_file))
        DF_JSON=pd.DataFrame.from_dict(JSON,dtype=object)
        DF_JSON = DF_JSON.astype(str)

        try:
            if i==0:
                DF_JSON.to_sql(Table_Name,engine,if_exists='replace',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.String, 'FirmIdent':sqlalchemy.types.String})
            else:
                DF_JSON.to_sql(Table_Name,engine,if_exists='append',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.String, 'FirmIdent':sqlalchemy.types.String})
            print('Successfully')
        except Exception as ErrorMessage:
            logging.info(': Error occured when inserting records into DB: '+ErrorMessage+'.')

        return DF_JSON
        logging.info('Successfully uploaded JSON to SQL {}'.format(JSON_file))

问题是，将它们转换为字符串并不是一种有效的方法，因为 SQL 中 TEST 中的 StaffIndent 和 FirmIndent 最初是数字的。我的问题：如何在 to_sql 步骤中处理 BigInt 并将它们视为数字？

当用 BigInteger 替换 String 时：

        try:
            if i==0:
                DF_JSON.to_sql(Table_Name,engine,if_exists='replace',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.BigInteger, 'FirmIdent':sqlalchemy.types.BigInteger})
            else:
                DF_JSON.to_sql(Table_Name,engine,if_exists='append',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.BigInteger, 'FirmIdent':sqlalchemy.types.BigInteger})

一个错误：

DataError: (pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type bigint. (8115) (SQLExecDirectW); [22003] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)')

使用 Decimal 听起来不错，但不，当涉及到 SQL 时，dtype 必须是数字和整数

                DF_JSON.to_sql(Table_Name,engine,if_exists='replace',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.DECIMAL(38,0), 'FirmIdent':sqlalchemy.types.DECIMAL(38,0)})
            else:
                DF_JSON.to_sql(Table_Name,engine,if_exists='append',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.DECIMAL(38,0), 'FirmIdent':sqlalchemy.types.DECIMAL(38,0)})

标签： pythonsqlbigintegerbigint

解决方案

我的 StaffIdent 和 FirmIdent 包括 20 位/每个（例如 StaffIdent 15423992539793905091），它大于最大 BigInt（仅 19 位。8 字节中的 9,223,372,036,854,775,807）所以我必须找到另一个比 BigInt 更大的。NUMBERIC(38,0) 就是答案。

BigInt 在 8 个字节内上升 9,223,372,036,854,775,807

NUMBERIC(38,0) 上升 17 字节

文档https://docs.snowflake.com/en/sql-reference/data-types-numeric.html

我参考了此页面中的建议https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129388

毕竟，我们将拥有：

from sqlalchemy import create_engine, types
import os
import logging
import json
import pandas as pd
import sqlalchemy 
import pandasql as ps

def upload_data(SQLServer,SQLDatabase,JSON_file):
    print('Debug...')
    Table_Name='TEST'
    engine = create_engine('mssql+pyodbc://{0}/{1}?driver=SQL Server?Trusted_Connection=yes'.format(SQLServer,SQLDatabase))
    i=0
    for JSON_file in os.listdir('Data/'+Table_Name):
        logging.info('Debug: Reading file JSON and inserting into database: {0}'.format(JSON_file))
        JSON = json.load(open('Data/'+Table_Name+'/'+JSON_file))
        DF_JSON=pd.DataFrame.from_dict(JSON,dtype=object)
        DF_JSON = DF_JSON.astype(str)

        try:
            if i==0:
                DF_JSON.to_sql(Table_Name,engine,if_exists='replace',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.NUMERIC(38,0), 'FirmIdent':sqlalchemy.types.NUMERIC(38,0)})
            else:
                DF_JSON.to_sql(Table_Name,engine,if_exists='append',chunksize=50,index=False,dtype={'StaffIdent':sqlalchemy.types.NUMERIC(38,0), 'FirmIdent':sqlalchemy.types.NUMERIC(38,0)})
            print('Successfully')
        except Exception as ErrorMessage:
            print('Error')
            print(ErrorMessage)

        return DF_JSON

这将在 SQL 中以数字形式返回 StaffIdent 和 FirmIdent 的 dtype

推荐阅读