python to read the data from oracle

[melinda]

hi ,

i am new to python and i have below requirement.

i have 5 tables which i need to fetch from oracle from same db daily. insert into snowflake tables .the job should run on hourly basis.

i have two text files , one is maping text file which shows correspondence oracle-snowflake table mapping, the another text files which should have last insert date for each snowflake tables.

mapping text file:

oracle snowflake
or_tbl_v1 snw_tbl_v1
or_tbl_v2 snw_tbl_v2
or_tbl_v3 snw_tbl_v3
or_tbl_v4 snw_tbl_v4
or_tbl_v5 snw_tbl_v5

text file that shows last max date the record is inserted for each table.

table date
snw_tbl_v1 2025-08-17 12:33:44 55:66:00
snw_tbl_v2 2025-08-17 12:33:44 56:66:00
snw_tbl_v3 2025-08-17 12:33:44 57:66:00
snw_tbl_v4 2025-08-17 12:33:44 58:66:00
snw_tbl_v5 2025-08-17 12:33:44 59:66:00

so every hour when the job runs it should update above text file with max date from table based on the column insert_date.
and then it should pick the date from oracle which is max then the file date for each table. and insert into snowflake table from that date.

oracle : select max(date) from table (oracle) where date > select date from table(snoflake) --- it should come from file .
query : insert into snowflake.table
select * from oracle where date >= maxdate) from above query.

thanks

[Larz60+]

Here's a tutorial from Oracle on how to extract Data using python.

[Pedroski55]

I tried on my laptop. I don't have oracle, I have MySQL, but oracle will be similar.

You need to import datetime to make sense of the date you get from the database.

import pymysql
from datetime import datetime

def mysqlFetch(table_name): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='127.0.0.1',
        # listening on the default port, no need to set the server access port
        #port=22000,
        user='me',  
        password = 'secret', 
        db='first_db', 
        ) 
      
    cur = conn.cursor()

    # Select query     
    sql = f"SELECT * FROM {table_name} WHERE timestamp = (SELECT MAX(timestamp) FROM {table_name});"
    cur.execute(sql)
    #output = cur.fetchall()
    output = cur.fetchone() 
    #for i in output: 
        #print(i) 
      
    # To close the connection 
    conn.close()
    return output

# the tuple will be the whole row
# you just want the date, which in my case is the 4th entry in the tuple
table_name = 'users'
max_date = mysqlFetch(table_name)
latest_entry = datetime.strftime(max_date[4], "%Y-%m-%d, %H:%M:%S") 

I get for latest_entry:

Output:
'2025-08-18, 15:24:17'

I presume you know how to add that to the relevant text file. If not, ask.

I recommend you try out your SQL query on your database first, to make sure that it works, then try it using Python.

[Larz60+]

You might also try pandas which simplifies queries a lot. Here's an example