使用 Python 将数据从 MSSQL 数据库复制到 Postgresql 数据库 [英] copy data from MSSQL database to Postgresql database with Python

问题描述

我有两个 2 数据库.一个使用 MSSQL，另一个使用 Postgresql.我希望我的 python 脚本每天都在运行(为此，我在 linux 上使用了 cron-job).MSSQL 数据库中的数据应复制到 Postgresql 数据库.我有一个想法，但它行不通.你可以帮帮我吗???也许我的解决方案是完全错误的...

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...

这是我的代码:

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:request_proc 工作正常.

PS: the request_proc works fine.

推荐答案

如果代码没有出错，但是插入的记录没有出现在 Postgresql 数据库中，你很可能需要添加 self.conn2.commit() 执行 INSERT 语句后.这会将每个事务提交到数据库.

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.

这篇关于使用 Python 将数据从 MSSQL 数据库复制到 Postgresql 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！