Python Integration with Multiple Data Sources and Tables (Working with Oracle & SQL Server Database )

Python queries to work with multiple instances of Oracle and SQL Server. In this challenge, I had to query Oracle and SQL Server to merge their tables
to generate a new SQL Server table.

##0 Import required Python Modules
import os
import pandas as pd
import pyodbc
import cx_Oracle
import datetime

##1 Setup Oracle Connection
dsn_tns = cx_Oracle.makedsn(‘xxx.myoracledomain.com’, ‘1521’, service_name=’UAT’)
conn_oracleserver = cx_Oracle.connect(user=r’admin’, password=’whate#$@$12*vW’, dsn=dsn_tns)

##2 Setup SQL Server Connection
conn_sqlserver = pyodbc.connect(r’Driver={SQL Server};Server=mysqlserver.domain\test;Trusted_Connection=yes;’)

##3 Oracle & SQL Server cursor
oracle_cursor = conn_oracleserver.cursor()
sqlserver_cursor = conn_sqlserver.cursor()

##4 Example of joining the Oracle Tables
oracle_cursor.execute(‘SELECT DISTINCT W3.DEFAULT_FIELD FROM TABLE1 W1 INNER JOIN TABLE 2 W2 ON W1.FIELD_ID = W2.FIELD_ID INNER JOIN TABLE3 W3 ON W2.FIELD_ID = W3.FIELD_ID’)

##5 Inserting the records from Oracle to SQL Server
for site_id in oracle_cursor:

##6  Insert Oracle Data into temporary table TEMP_TABLE
sqlserver_cursor.execute(“INSERT INTO [[TEMP_TABLE](FIELD_REF,FIELD2) VALUES (?,?)”,(str(Value1),’Sample String DataPrj’))
sqlserver_cursor.commit()

##7 Merging and Grouping Tables
selectdistinct = sqlserver_cursor.execute (“SELECT FIELD3, Dcat = STUFF((SELECT N’, ‘ + FIELD4 FROM [TEMP_TABLE] AS P2 WHERE P2.FIELD_REF = P1.FIELD_REF ORDER BY FIELD5 FOR XML PATH(N”)), 1, 2, N”) FROM [TEMP_TABLE] AS P1 GROUP BY FIELD5 ORDER BY FIELD_REF;”)

##8 Close Connection
conn_oracleserver.close()
conn_sqlserver.close()

Hope you find this tutorial useful in your learning of Python and database integration.

Leave a Reply

Your email address will not be published. Required fields are marked *