Hi,
I'm a newbie to Python coding and I'd love some help with my scenario.
I have two DBs , one is MS SQL & the other is PostgreSQL.
There are 30+ tables that I need to delete / truncate and then re-insert them from one DB to the other.
I'm using checksums to find out whether or not the table need this process to occur, delete the old content but I'm stuck on the INSERT section of the code.
Listed below is my code, since each of these tables has a totally different structure (names & amount of columns) - I need a dynamic solution for this mapping.
I'm a newbie to Python coding and I'd love some help with my scenario.
I have two DBs , one is MS SQL & the other is PostgreSQL.
There are 30+ tables that I need to delete / truncate and then re-insert them from one DB to the other.
I'm using checksums to find out whether or not the table need this process to occur, delete the old content but I'm stuck on the INSERT section of the code.
Listed below is my code, since each of these tables has a totally different structure (names & amount of columns) - I need a dynamic solution for this mapping.
import pyodbc import psycopg2 conn = pyodbc.connect('Driver={SQL Server};' 'Server=blabla;' 'Database=dbname;' 'Trusted_Connection=no;' 'UID=user;' 'PWD=password;' ) conn2 = psycopg2.connect(user = "user", password = "password", host = "hostname", port = "5432", database = "dbname") IotTable = conn.cursor() GetChecksum = conn.cursor() Postgre = conn2.cursor() IotTable.execute('SELECT TABLE_Name, Checksum_value FROM SYNC_CHECK order by TABLE_Name;') def A(): for row in IotTable: TName = row[0] CSum = row[1] results = GetChecksum.execute('SELECT CHECKSUM_AGG(CHECKSUM(*)) FROM dbname.dbo.'+TName+';') res = results.fetchone()[0] if CSum != res: query = 'delete from public.'+TName+';' Postgre.execute(query) conn2.commit() # so far all of this works - I can detect if there is a new version of the source table and if so delete the current table in the destination query2 = 'select * from dbo.'+TName+';' #my source data which is dynamic in nature GetData = GetChecksum.execute(query2) record2 = GetChecksum.fetchall() query3 = 'insert into public.'+TName+' values '+ record2[0] #this doesn't work ... conn3.commit() A() if(conn): IotTable.close() GetChecksum.close() conn.close() if(conn3): Postgre.close() conn3.close()