[FIXED] How to read data from oracle database and convert a dataframe the query result in Python?

Issue

I created a method to read data from oracle db:

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

Then, I connected DB and read data as follow:

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

Output is:

(('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

the output is:

[(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'))]

I want to get the query results as a dataframe. Like as follow:

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

How to create a dataframe from query results?

Solution

Try pd.read_sql function:

pd.read_sql(query, cnxn)

Answered By - thomask

Answer Checked By - Timothy Miller (PHPFixing Admin)