Python Forum
building a dynamic INSERT between two DBs with Python3
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
building a dynamic INSERT between two DBs with Python3
#1
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.

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()
Reply
#2
Then your syntax of insert is wrong,
maybe you forget "(" and ")":

https://en.wikipedia.org/wiki/Insert_(SQL)
MS-SQL:
https://docs.microsoft.com/en-US/sql/t-s...erver-2017

PostgreSQL:
https://www.postgresql.org/docs/9.2/sql-insert.html
Reply
#3
Hi,

The syntax that I'm looking for in Python is the equvialant to the SQL syntax of:

INSERT INTO T SELECT * FROM X;

If I do define each of the columns and avoid the wildcard then the INSERT works, however - since I have multiple tables that I'm looping through, and each of them has a different amount of columns, I really do not know how to create a dynamic list (e.g. [0] , [1] ... [N]) to use the strongly defined column list syntax.
Any suggestions ?
Reply
#4
Ok I understood. But your code says:

query3 = 'insert into public.' + TName + ' values ' + record2[0]
Reply
#5
Yes, I wrote it as an example - I don't know how to make the syntax dynamic to include all columns
Reply
#6
Ok,I understood.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  cursor.execute: How to insert dynamic number in a string? stoeberhai 2 3,447 Mar-18-2021, 12:55 PM
Last Post: stoeberhai
  Gnuradio python3 is not compatible python3 xmlrpc library How Can I Fix İt ? muratoznnnn 3 4,823 Nov-07-2019, 05:47 PM
Last Post: DeaD_EyE
  Is this a normal/benign make test error when building python3.6 sofuego 2 3,476 Feb-12-2018, 12:32 AM
Last Post: sofuego
  Insert using psycopg giving syntax error near "INSERT INTO" olgethorpe 4 15,497 Jul-21-2017, 07:39 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020