copy data from MSSQL database to Postgresql database with Python

I have two 2 databases. One with MSSQL and another with Postgresql. I want that my python script is running (for this I use a cron-job on linux), every day. The data from the MSSQL database should copied to the Postgresql database. I have an idea but it doesn't works. Could you help me??? Maybe my solution is totally wrong...

That's my code:

import pymssql, psycopg2

class DatabaseRequest:

    def __init__(self):
        self.conn1 = pymssql.connect(host='****', user='****', password='****', database='****')
        self.conn2 = psycopg2.connect("dbname='****' user='****' host='*****' password='****'")

        self.cur1 = self.conn1.cursor()
        self.cur2 = self.conn2.cursor()

    def request_proc(self, rows):
        self.cur1.execute("SELECT top 10  tag, site, plant, unit, line, ProcessID AS pid, Count(ReadTime) AS mods \
                        FROM ( \
                        select dateadd(dd, -1, convert(varchar, getDate(),111)) \
                        as tag, ReadTime, processID, subid, PR.Site, PR.Plant, PR.Unit, PR.Line \
                        from FactBarcodeReading BCR with(nolock) \
                        inner join DimProcess PR on BCR.ProcessKey = PR.ProcessKey 开发者_开发技巧\
                        where PR.ProcessID IN  (802, 1190, 1800, 3090, 3590, 4390, 4590, 4800, 5000, 5400, 4190) \
                        and ReadTime between dateadd(dd, -1, convert(varchar, getDate(),111)) and dateadd(dd, -0, convert(varchar, getDate(),111)) \
                        ) a \
                        GROUP BY tag, site, plant, unit, line, ProcessID \
                        ORDER BY site, plant, unit, line, ProcessID")

        rows = self.cur1.fetchone()

       # return rows   


    def insert_proc(self):
        self.cur2.execute("INSERT INTO 20091229global (proddate, site, plant, unit, line, pid, mods) \
                        VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
                        % self.cur1.fetchone())


        a = DatabaseRequest()
        print a.insert_proc()

PS: the request_proc works fine.

---

If the code produces no errors but the inserted records are not appearing in the Postgresql database, you most likely need to add self.conn2.commit() after executing the INSERT statement. This will commit each transaction to the database.

According to the documentation, it is also possible to enable automatic transactions.

---

you also need to pass the cursor (cur1) into insert_proc -->

def insert_proc(self, cur1):
  self.cur2.execute("INSERT INTO 20091229global (proddate, site, plant, unit, line, pid, mods) \
                        VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
                        % self.cur1.fetchone())

---

Just seeing this thread after having tried to find the answer myself. You can use a method from psycopg2.extras called execute_values().

In the documentation https://www.psycopg.org/docs/extras.html you can find it near the bottom. Just pass a single string variable, then define that placeholder as a values list.