Inserting rows into another database-CodePudding

I need to execute a SQL query in one database - Vertica, and insert the result into another - Oracle, without creating an intermediate table. Can you tell me how to do this in python? There is Pycharm and configured connections to databases through the database navigator. Thanks for any help

CodePudding user response：

Since you have not shared your work or rather approach- I am sharing few steps which can be helpful

i. Create a connection to Vertica using python ii. When the connection is done, execute the query and fetch the result

from vertica_python import connect 
connection = connect({ 
    'host': '127.0.0.1', 
    'port': 5433, 
    'user': 'user', 
    'password': 'abcd1234' 
 
    }) 
cur = connection.cursor() 
cur.execute("SELECT * FROM TABLE") 
result = cur.fetchall() 
connection.close()

iii. Create another connection with Oracle and write the data from step ii. #result

Insert values to the oracle table using python cx_oracle Session Pool

CodePudding user response：

why not try somthing simple: open db connection to db1 execute an SQL select close db connection to db1 open db connection to db2 execute an insert close the connection

if that's exactly the part you are struggling with you can replace the db1 and db2 and enter each step i wrote prefixed with "python" to google, this should yield the exact code you should use for each step.

next approach is I'm assuming your problem is more complex as the SQL table query simply wont fit to ram . you can try following approach:

use a for loop and select a subset of the query each interaction until the full query is executed.

lets say if you have a datetime field you can do some operations with timedelta and if statements to create a smaller subset of the indented query.

use dataframes and SQL alchemy to ease code development as working with table in dataframes is much easier

create a db engine using SQL alchemy use dataframe.read_sql() and .to_sql() statements using the created engine

you ask a high level question you get a high level answer i can tell you are new here, next time please post things you have tried or approaches that you know don't work with an explanation as to why . do this so the community could understand your problem better! :)