gistfile1.txt

# -*- coding: utf8 -*-
# Этот модуль нужен для синхронизации ДВУХ БД с заявками!
# PostgreSQL <==> FireBird 
# Не забыть поставить psycopg2 (stand alone installer, через PIP не ставится)

import os
import gvar
import psycopg2
import sys
import fdb
 
# Курсоры определяем до начала всей работы

postconn = psycopg2.connect( 'host={0} dbname={1} user={2} password={3}'.format(gvar.postdbhost, gvar.postdbname, gvar.postuser, gvar.postpass))
postcursor = postconn.cursor() # Курсор для Firebird


fireconn = fdb.connect(gvar.dbname, gvar.dbuser, gvar.dbpass)
firecursor = fireconn.cursor()


# Работаем с CUSTOMER Firebird --> PostgreSQL
# Берем из FireBird и отправляем в PostgreSQL
def SyncCustomer():
	print "SyncCustomer"
	try:
		count_customer = 0
		firecursor.execute('SELECT customer_id, customer_name, customer_data, customer_priority, customer_status FROM CUSTOMER WHERE fl_ready=0')
		fireselect_result = firecursor.fetchall()

		if not fireselect_result:
			print "[SYSTEM] \t Table CUSTOMER do not contain any flags=0"

		else:
			print "Trying to past in PostgreSQL:"	
		# Получаем нужные столбцы из PostgreSQL
		for customer_id, customer_name, customer_data, customer_priority, customer_status in fireselect_result:
			
			SQLInsertPostgreSQL = (("INSERT INTO CUSTOMER (CUSTOMER_NAME, CUSTOMER_DATA, CUSTOMER_PRIORITY, CUSTOMER_STATUS, FL_READY, FIREID) \
			VALUES ('{0}', '{1}', {2}, {3},{4},{5})")).format(customer_name, customer_data, customer_priority, customer_status, 1, customer_id) #fl_ready dscnfdkztv d 1
			# print "===================="
			print SQLInsertPostgreSQL
			# print "^^^^^^^^^^^ POST COMMIT ^^^^^^^^"
			postcursor.execute(SQLInsertPostgreSQL)
			print "===================="
			postconn.commit()

			# Нужно обновить флаг готовности в FireBird (откуда брали)
			firecursor.execute("UPDATE CUSTOMER SET FL_READY = 1 WHERE customer_id=%s" % (customer_id))
			fireconn.commit()

	# Берем (тоже что и ниже) работаем, но уже с FIREID
			postcursor.execute("SELECT CUSTOMER_ID FROM CUSTOMER WHERE fireid=%s" %(customer_id))
			print customer_id
			postselect_result = postcursor.fetchone() 
			# И обновляем FIREID в FireBird
			firecursor.execute("UPDATE CUSTOMER SET POSTID = %s WHERE customer_id=%s" % (str(postselect_result[0]), customer_id))
			fireconn.commit()
			count_customer +=1
		return count_customer

	except SystemError:
		print "Error in module SyncCustomer"



# Обратная операция. Берем из PostgreSQL и отправляем в FireBird
# Эта операция пока не требуется. Достаточно одно сторонней синхронизации Firebird --> PostgreSQL
# Код ниже рабочий и, если надо, может быть раскомментирован
	postcursor.execute('SELECT customer_id, customer_name, customer_data, customer_priority, customer_status FROM CUSTOMER WHERE fl_ready=0')
	postselect_result = postcursor.fetchall()

	for customer_id, customer_name, customer_data, customer_priority, customer_status in postselect_result:
		SQLInsertFirebirdSQL = (("INSERT INTO CUSTOMER (CUSTOMER_NAME, CUSTOMER_DATA, CUSTOMER_PRIORITY, CUSTOMER_STATUS, FL_READY, POSTID) \
		VALUES ('{0}', '{1}', {2}, {3},{4},{5})")).format(customer_name, customer_data, customer_priority, customer_status, 1, customer_id) # В FB пишем ID от PostgreSQL
		firecursor.execute(SQLInsertFirebirdSQL)
		fireconn.commit()

		postcursor.execute("UPDATE CUSTOMER SET FL_READY = 1 WHERE customer_id=%s" % (customer_id))
		postconn.commit()

# Берем из FireBird по ID-шнику от Postgres, который выше CUSTOMER_ID
		firecursor.execute("SELECT CUSTOMER_ID FROM CUSTOMER WHERE POSTID=%s" %(customer_id))
		print customer_id
		fireselect_result = firecursor.fetchone() 
		# И обновляем FIREID в PostgreSQL
		postcursor.execute("UPDATE CUSTOMER SET FIREID = %s WHERE customer_id=%s" % (str(fireselect_result[0]), customer_id))
		postconn.commit()
		count_customer +=1
	return count_customer
####################################################################		
####################################################################
def SyncProdType():
# Работаем с ProdType Firebird --> PostgreSQL
	firecursor.execute('SELECT prod_type_id, product_kind FROM prod_type WHERE fl_ready=0')
	fireselect_result = firecursor.fetchall()

	if not fireselect_result:
		print "[SYSTEM] \t Table PRODUCT_KIND do not contain any flags=0"
	
	count_prod=0
	for prod_type_id, product_kind in fireselect_result:
			
		print "Try to past in PostgreSQL:"
		
		SQLInsertPostgreSQL = (("INSERT INTO prod_type (prod_type_id, product_kind, FL_READY) VALUES ({0},'{1}',{2})")).format(prod_type_id, product_kind, 1) #FL_READY для синхронизированных
		print "===================="
		print SQLInsertPostgreSQL
		print "===================="
		count_prod +=1
		postcursor.execute(SQLInsertPostgreSQL)
		
		postconn.commit()
		fireconn.commit()

	# POSTGRES  ^^^^^^^^^
		firecursor.execute("""UPDATE prod_type SET FL_READY = 1 WHERE prod_type_id=%s""" % (prod_type_id))
		postconn.commit()
		fireconn.commit()


	print "======================================================"
# Работаем с REQUEST. Вставляем PostgreSQL <--> FireBird
# 1-2 и 2-й - подключения к базам, 1-й откуда-донор, 2-й - куда качаем
# 3-й и 4-й параметры - имена полей доп.ид-шников postid и fireid
def SyncRequest(connSrc, connDst, NamefieldIDSrc, NamefieldIDDst, ):
	print "SyncRequest"
	try:
		count_request = 0
		currSrc = connSrc.cursor()
		currDst = connDst.cursor()

		currSrc.execute("""SELECT request_id, time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape FROM "REQUESTS" WHERE fl_ready=0""")
		for request_id, time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape in currSrc.fetchall():
			# Проверяем наличие customer_id в принимающей базе
			# c = customer, p=prod_type.

			currDst.execute(("""SELECT customer_id FROM "CUSTOMER" WHERE customer_id=%s""") %customer_id)
			c = currDst.fetchone()
			print "--------------!"
			print "customer_id: ", c
			print "========--------------======="
			connDst.commit()

			print((("""SELECT prod_type_id FROM "PROD_TYPE" WHERE prod_type_id=%s""") %prod_type_id))
			currDst.execute(("""SELECT prod_type_id FROM "PROD_TYPE" WHERE prod_type_id=%s""") %prod_type_id)
			pt = currDst.fetchone()
			print "pt: ", pt # упорно возвращает pt:  None хотя заспрос 
			print "prod_type_id: ", prod_type_id

			connDst.commit()

			# c = customer, p=prod_type.
			if c and pt:
				print("""INSERT INTO "REQUESTS" (time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape, FL_READY, {0} )
				VALUES ('{1}', '{2}', {3}, {4}, {5}, {6}, {7}, {8}, '{9}', {10}, {11})""".format(NamefieldIDSrc, time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape, 1, request_id))
				
				
				currDst.execute("""INSERT INTO "REQUESTS" (time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape, FL_READY, {0} )
				VALUES ('{1}', '{2}', {3}, {4}, {5}, {6}, {7}, {8}, '{9}', {10}, {11})""".format(NamefieldIDSrc, time_beg, time_end, freq, prod_type_id, priority, customer_id, sol_angle_min, sol_angle_max, shape, 1, request_id))
				connDst.commit()

				# Вот тут берем ID источника и в приемник его отправляем. В начале делаем его SELECT, а ниже обновляем
				currDst.execute("""SELECT "REQUEST_ID" FROM "REQUESTS" WHERE %s=%s""" % (request_id, NamefieldIDSrc))
				rid = currDst.fetchone()[0]
				if rid:
					currSrc.execute("""UPDATE "REQUESTS" SET %s=%d WHERE REQUEST_ID=%d;""" % (NamefieldIDDst, rid, request_id))
					connSrc.commit()
				connDst.commit()
				# Теперь еще и флаг обновляем
				currSrc.execute("""UPDATE "REQUESTS" SET FL_READY=1 WHERE REQUEST_ID=%s;""" % (request_id))
				connSrc.commit()
				count_request += 1

		connSrc.commit()
		connDst.commit()
		return count_request

	except SystemError:
		print "Error in module SyncRequest"	

 # Синхронизируем FireBird (Instrument) --> в PostgreSQL (Instrument)
def SyncInstrument():
	print "SyncInstrument"
	firecursor.execute('SELECT instrument_id, instrument_name, instrument_data, proc_order FROM INSTRUMENT WHERE fl_ready=0')
	fireselect_result = firecursor.fetchall()
	for i in fireselect_result:
		print i

	if not fireselect_result:
		print "[SYSTEM] \t Table INSTRUMENT do not contain any flags=0"

	for instrument_id, instrument_name, instrument_data, proc_order in fireselect_result:
			
		print "Try to past in PostgreSQL:"
		# instrument_id, instrument_name, instrument_data, proc_order, fl_ready
		SQLInsertPostgreSQL = (("""INSERT INTO "INSTRUMENT" ("instrument_id", "instrument_name", "instrument_data", "proc_order", "fl_ready") VALUES ({0},'{1}','{2}','{3}',{4})""")).format(instrument_id, instrument_name, instrument_data, proc_order, 1) #FL_READY для синхронизированных
		print "===================="
		print SQLInsertPostgreSQL
		print "===================="
		# count_prod +=1
		postcursor.execute(SQLInsertPostgreSQL)
		
		postconn.commit()
		fireconn.commit()

	# POSTGRES  ^^^^^^^^^
		foo = firecursor.execute("UPDATE INSTRUMENT SET FL_READY = 1 WHERE instrument_id=%s" % (instrument_id))
		print "Sync Done"
		postconn.commit()
		fireconn.commit()


def main():

	print "Connected!\n"

	print('[Requests] PostgreSQL --> FireBird upload records: %d' % SyncRequest(postconn, fireconn, 'POSTID', 'FIREID'))
	#print('[Requests] FireBird --> PostgreSQL upload records: %d' % SyncRequest(fireconn, postconn, 'FIREID', 'POSTID'))
	# print('[Customers] FireBird --> PostgreSQL upload records:%d' % SyncCustomer())
	#SyncInstrument()

 
if __name__ == "__main__":
	main()