如何从oracle数据库中读取数据并用Python转换数据帧查询结果

Question

我创建了一个从oracle数据库读取数据的方法：

def read_data_oracle(server, database, username, password, query):
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute(query)
query_results = cursor.fetchall()
return cursor, query_results

然后，我连接DB并读取数据如下：

server = 'server_id'
database = 'database_name' 
username = 'UID' 
password = 'PWD' 
query =  """SELECT TOP 5 [Id], [Date], [Order_Sum] FROM [DB_Name].[dbo].[Table_Name] order by [Date] desc"""
cursor, query_results = read_data_oracle(server, database, username, password, query)
cursor.description

输出为：

(('Id', int, None, 10, 10, 0, False),  ('CreatedOnUtc', datetime.datetime, None, 23, 23, 3, False),  ('OrderTotal', decimal.Decimal, None, 18, 18, 4, False))
query_results

输出为：

[(611020, datetime.datetime(2021, 6, 7, 8, 43, 57, 467000), Decimal('520.3100')),
(611019, datetime.datetime(2021, 6, 7, 8, 43, 41, 967000), Decimal('281.1200')),
(611018, datetime.datetime(2021, 6, 7, 8, 38, 40, 33000), Decimal('774.4900')),
(611017, datetime.datetime(2021, 6, 7, 8, 38, 32, 210000), Decimal('774.4900')),
(611016, datetime.datetime(2021, 6, 7, 8, 37, 53, 233000), Decimal('299.7000'))]

我想将查询结果作为数据帧获取。如下所示：

Id         Date                         Order_Sum
611020     2021-06-07 08:43:57.467      520.3100
611019     2021-06-07 08:43:41.967      281.1200
611018     2021-06-07 08:38:40.330      774.4900
611017     2021-06-07 08:38:32.210      774.4900
611016     2021-06-07 08:37:53.233      299.7000

如何根据查询结果创建数据帧？

Answer 1

尝试pd.read_sql函数：

pd.read_sql(query, cnxn)