Select from and Insert into another database with Python

Question

I’m trying to select a DB from Azure and after that insert the data into a postgresql DB.

I tested the separate connections and both are working, but when running the full code nothing happens until accusing time out of DB.

Versions: Python 3, Postgresql 10.6, Microsoft SQL Azure (RTM) - 12.0.2000.8

Can you give me a light? Follow my code:

import pyodbc
import psycopg2

#conn azure
server_azure = 'server'
database_azure = 'database'
username_azure = 'username'
password_azure = 'password'
driver = '{ODBC Driver 17 for SQL Server}'
cnxn_azure = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

#conn postgresql
cnxn_postgre = psycopg2.connect("dbname='dbname' user='user' host='host' 
password='password'")
cursor_postgre = cnxn_postgre.cursor()

cursor_azure = cnxn_azure.cursor()
cursor_azure.execute(
"select col1, col2, col3, col4, col5, col6  from tabela"
row = cursor.fetchone()
for row in rows
    cursor_postgre.execute(""" INSERT INTO "tabela2" (col1, col2, col3, col4, col5, col6) VALUES ('%s', '%s', '%s', '%s', '%s', '%s') """)

cursor_azure.commit()
cursor_azure.close()
cursor_postgre.close()

Answer 1

Maursb, from what I saw in your code, there’s an error in the FOR

change line 20 so it looks like this

rows = cursor.fetchone()