python - pandas read_sql_query 将32位数据转换为64位

Question

我正在使用 pandas.read_sql_query 从 Sql 服务器读取一些数据。我读取的数据类型是Sql server中的int和real，即32位整数和32位浮点值。但在生成的数据框中，数据类型是 int64 和 float64。将数据放入 Pandas 数据帧后，我可以将其转换回 32 位，但我正在读取大量数据，因此这会产生内存问题。有没有办法在读入数据帧时将来自 Sql server 的数据保持为 32 位？

这里是一个数据库连接的例子:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

query = ("SELECT  "
"MId, "
"SomeInt, "
"SomeReal "
"From dbo.Example;")

df = pd.read_sql_query(query, cnxn)

Answer 1

查看当前的 Pandas 代码，我认为唯一的选择是使用循环方法开发自己的解决方案。使用“read_sql_query”或“read_sql”最终会从 pyodbc 行列表中调用“pandas.DataFrame.from_records”。 “DataFrame.from_record”不接受指定任何数据类型。

“pandas.DataFrame”可能无济于事，因为它只接受指定单一数据类型。相反，您可以使用 numpy 以循环方式帮助构建 DataFrame。

import pandas as pd
import numpy as np
import pyodbc

server_name = 'localhost'
database_name = 'AdventureWorks2019'
driver = 'ODBC Driver 17 for SQL Server'

connection = pyodbc.connect(driver='{'+driver+'}', server=server_name, database=database_name, trusted_connection='yes') 

cursor = connection.cursor()


# sample MSSQL AdventureWorks database
results = cursor.execute('SELECT BusinessEntityID, NationalIDNumber FROM HumanResources.Employee')

# preallocate empty DataFrame with known data types
data = pd.DataFrame(columns=['BusinessEntityID','NationalIDNumber'])
data = data.astype({'BusinessEntityID': 'int32', 'NationalIDNumber': 'object'})


while True:

    # load results into memory until there are no more to process
    # this is ultimately what "read_sql_query" would build a DataFrame from
    loop = results.fetchmany(50)
    if len(loop)==0:
        break

    # convert to tuple for numpy
    loop = [tuple(x) for x in loop]

    # convert to numpy array with specific type
    rows = np.array(loop, dtype=[('BusinessEntityID', np.int32), ('NationalIDNumber', '<S15')])

    # convert to dataframe and append
    rows = pd.DataFrame.from_records(rows)

    data = data.append(rows)

print(data.dtypes)

BusinessEntityID     int32
NationalIDNumber    object
dtype: object

有关 numpy 中字符串的更多信息，请参阅此链接:

What is the difference between the types <type 'numpy.string_'> and <type 'str'>?