Python：在将数据导入Access表时，使用pyodbc以外的方法或解决方法

Question

我创建了一个Python脚本，它从政府网站提取数据，格式化数据，然后将数据转储到访问表中

我正在使用Sqlalchemy和pyodbc导入数据；但是，如果我的数据有一个整数列，我会收到可怕的“pyodbc.Error:（'HYC00'，'[HYC00][Microsoft][ODBC Access Database Driver]可选功能未实现（0）（SQLBindParameter）'）”错误消息

是否有人知道有什么方法可以避免这个错误，即使列已格式化为整数，也可以导入已格式化的数据？我知道解决这个问题的方法是将列格式化为float，但我不想要float。还有其他选择吗

以下是我的测试代码：

import pandas as pd
from pandas import DataFrame

import numpy as np
import re as re
import pyodbc
from sqlalchemy import create_engine

# Download zipfile from BOEM website, extract and save file to temp folder
from io import BytesIO
from urllib.request import urlopen
from zipfile import ZipFile
zipurl = 'https://www.data.boem.gov/Well/Files/5010.zip'
with urlopen(zipurl) as zipresp:
    with ZipFile(BytesIO(zipresp.read())) as zfile:
        zfile.extractall('/temp/leasedata')


# Import fixed field file to Pandas for formatting

# Define column spacing of fixed field file
colspecs = [(0, 12), (13, 17), (18, 26), (27, 31), (31, 37), (39, 47), (47, 53), (57, 62), (62, 67),
            (67, 73), (84, 86), (86, 92), (104, 106), (106, 112), (112, 120), (120, 128), (131, 134), (134, 139),
            (140, 155), (156, 171), (172, 187), (188, 203), (203, 213)]

df = pd.read_fwf('/temp/leasedata/5010.DAT', colspecs=colspecs, header=None)
# Add column headers
df.columns = ['API', 'WellName', 'Suffix', 'OprNo', 'BHFldName', 'SpudDate', 'BtmOCSLse', 'RKBElev', 'TotalMD',
              'TVD', 'SurfArea', 'SurfBlock', 'BHArea', 'BHBlock', 'TDDate', 'StatusDate', 'StatusCode', 'WaterDepth',
              'SurfLon', 'SurfLat', 'BHLon', 'BHLat', 'SurfOCSLse']

# Load dataframe into new temp table in database

# Connect to OOSA Access database. Make sure to create a User DSN directly to OOSA database before running script

conn = create_engine("access+pyodbc://@OOSA")

print(df)

df.to_sql('borehole_temp_table', conn, if_exists='replace')

谢谢你的帮助

Answer 1

I understand the way around this is to format the column to float, but I don't want float. Are there any other options?

从sqlalchemy-access wiki开始：

解决方法包括将列另存为短文本

import sqlalchemy_access as sa_a

# …

df = pd.DataFrame(
    [
        (12345678901,),
        (-12345678901,),
    ],
    columns=["column1"],
)
df["column1"] = df["column1"].astype(str)
dtype_dict = {'column1': sa_a.ShortText(20)}
df.to_sql("my_table", engine, index=False, if_exists="replace", dtype=dtype_dict)

…还是十进制

df = pd.DataFrame(
    [
        (12345678901,),
        (-12345678901,),
    ],
    columns=["column1"],
)
df["column1"] = df["column1"].astype(str)  # still need to convert the column to string!
dtype_dict = {'column1': sa_a.Decimal(19, 0)}
df.to_sql("my_table", engine, index=False, if_exists="replace", dtype=dtype_dict)