Python insert oracle data into sql server-CodePudding

I'm currently looking for a python script that will extract data from oracle database and insert it into an SQL SERVER Database

Currently I have created a python script to extract data from oracle database, below is my code

import csv
import cx_Oracle

# establish connection
connection = cx_Oracle.connect(
user="username", 
password="password",
dsn="servername/PROD")

# create cursor object
cursor = connection.cursor()

# query to display all the data 
cursor.execute("""SELECT *
FROM LOTTO_BI.LOTO_DRAW_RESULTS 
ORDER BY DRAW_NUM""")

# print each row in the cursor
for i in cursor:
print(i)

What is missing in my code is the part to insert the oracle data into a SQL SERVER table

CodePudding user response：

import pyodbc
import csv
import cx_Oracle

# establish connection
connection = cx_Oracle.connect(
user="username", 
password="password",
dsn="servername/PROD")

# create cursor object
cursor = connection.cursor()

# query to display all the data 
cursor.execute("""SELECT *
FROM LOTTO_BI.LOTO_DRAW_RESULTS 
ORDER BY DRAW_NUM""")

# Fetch all rows
oracle_rows = cursor.fetchall()


# Establish a connection to the SQL Server database
sql_server_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=sql_server_host;DATABASE=sql_server_db;UID=user;PWD=password')

# create cursor object
sql_server_cursor = sql_server_conn.cursor()

# Insert the rows into the SQL Server database
for row in oracle_rows:
    sql_server_cursor.execute('INSERT INTO sql_server_table VALUES (?, ?, ?)', row)

# Commit changes
sql_server_conn.commit()


# Close connections
oracle_cursor.close()
oracle_conn.close()
sql_server_cursor.close()
sql_server_conn.close()

I have the added the code below to your code. This assumes that the table in Oracle and the table in SQL Server have the same structure, with the same number of columns and data types.

CodePudding user response：

Well, to make things simple, just use pandas library which can translate from one sql engine to another with its to_sql() method.

As I don't have Oracle database locally, this example shows you how to do the same thing and insert MYSQL data to SQLite database

import pandas as pd
import sqlalchemy
import mysql.connector

# Connect to the MYSQL Database using the mysql.connector library
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='storefront2')

# Create a SQLAlchemy engine for the SQLite database
engine = sqlalchemy.create_engine('sqlite:///storefront2.db')

# Read `store_product` data from the MYSQL database
df = pd.read_sql('SELECT * FROM store_product', connection)

# Write the data from the DataFrame to the SQLite database
df.to_sql('store_product', engine, if_exists='replace', index=False)

As for you, connection would be a connection to your Oracle database (you can keep your connection) and engine would be connection to SQLServer.

Edit: to create engine for SQLServer, use:

engine = sqlalchemy.create_engine("mssql pymssql://scott:tiger@hostname:port/dbname")

Or checkout SQLAlchemy documentation: https://docs.sqlalchemy.org/en/14/core/engines.html#microsoft-sql-server