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.