def connect_relatorio(dataInicial, dataFinal):
parametros = estadualOuMunicipal('estadual')
try:
con = fdb.connect(host=parametros[0],
database='/nQuestor/base/QUESTOR.FDB',
user='******',
password='******')
except:
#con = fdb.connect(dsn=host + '/nQuestor/base/QUESTOR.FDB', user='******', password='******')
con = fdb.connect(dsn='D:/nQuestor/base/QUESTOR_EMPRESA_1117.FDB',
user='******',
password='******')
cur = con.cursor()
cur.execute(
'SELECT l.CODIGOEMPRESA, l.DATAHORALCTOFIS, e.NOMEESTAB, l.ESPECIENF FROM lctofissai l JOIN estab e on l.CODIGOEMPRESA = e.CODIGOEMPRESA WHERE l.datalctofis BETWEEN '
+ "'" + str(dataInicial) + "'" + 'AND' + "'" + str(dataFinal) +
"'" + 'ORDER BY l.CODIGOEMPRESA')
lista = []
lista1 = []
fieldIndices = range(len(cur.description))
x = 0
for row in cur:
for fieldIndex in fieldIndices:
fieldValue = str(row[fieldIndex])
fieldMaxWidth = cur.description[fieldIndex][
fdb.DESCRIPTION_DISPLAY_SIZE]
lista.append(fieldValue.ljust(fieldMaxWidth))
x = x + 1
lista1.append([lista[x - 4], lista[x - 3], lista[x - 2], lista[x - 1]])
return lista1
def connect_FDB(tipo):
parametros = estadualOuMunicipal(tipo)
try:
con = fdb.connect(host=parametros[0],
database='/nQuestor/base/QUESTOR.FDB',
user='******',
password='******')
except:
#con = fdb.connect(dsn=host + '/nQuestor/base/QUESTOR.FDB', user='******', password='******')
con = fdb.connect(dsn='D:/nQuestor/base/QUESTOR_EMPRESA_1117.FDB',
user='******',
password='******')
cur = con.cursor()
cur.execute(parametros[1])
lista = []
lista1 = []
fieldIndices = range(len(cur.description))
x = 0
for row in cur:
for fieldIndex in fieldIndices:
fieldValue = str(row[fieldIndex])
fieldMaxWidth = cur.description[fieldIndex][
fdb.DESCRIPTION_DISPLAY_SIZE]
lista.append(fieldValue.ljust(fieldMaxWidth))
x = x + 1
lista1.append([lista[x - 4], lista[x - 3], lista[x - 2], lista[x - 1]])
return lista1
def cliente_importado(self):
con = fdb.connect(
dsn=self.configuracao.db_host + ':' + self.configuracao.db_path,
user=self.configuracao.db_user, password=self.configuracao.db_password, fb_library_name=self.configuracao.db_lib)
cur = con.cursor()
cur.execute("select codigo_cliente, codigo_empresa from clientes where id_web = {id_web}".format(id_web=self.salao_cliente_id))
row = cur.fetchall()
print(row)
if not(row):
cliente = helper.get_cliente(self.salao_cliente_id)['data']['salonClient']
con = fdb.connect(
dsn=self.configuracao.db_host + ':' + self.configuracao.db_path,
user=self.configuracao.db_user, password=self.configuracao.db_password, fb_library_name=self.configuracao.db_lib)
cur = con.cursor()
qry_cliente=("SELECT CODIGO_CLIENTE FROM CLIENTES "
" WHERE CODIGO_EMPRESA = {codigo_empresa} "
" AND STATUS = 'A' "
" AND ( (ID_WEB IS NULL) OR (ID_WEB = 0) ) "
" AND ( (EMAIL = '{email}') "
" OR (UDF_DIGITS(TELEFONE1) = '{celular}' )"
" OR (UDF_DIGITS(TELEFONE2) = '{celular}' )"
" OR (UDF_DIGITS(TELEFONE3) = '{celular}' ) )").format(codigo_empresa=self.configuracao.emp_padrao, email=cliente["email"], celular= cliente["celular"] )
cur.execute(qry_cliente)
row = cur.fetchone()
if row:
self.update_cliente(row[0], cliente["id"])
else:
self.insere_cliente(cliente)
def try_reconnect(self):
try:
self.cds_db_project.close()
self.cds_db_project = fdb.connect(host=CDS_HOST,
database=CDS_DB_PROJECTS_PATH,
user=CDS_USER,
password=CDS_PASS,
charset='WIN1251')
self.cds_db_project.default_tpb = fdb.ISOLATION_LEVEL_READ_COMMITED_RO
self.logger.info(
f"Success connect to {CDS_HOST} {CDS_DB_PROJECTS_PATH}")
except Exception as general_error:
self.logger.error(general_error)
try:
self.cds_db_data.close()
self.cds_db_data = fdb.connect(host=CDS_HOST,
database=CDS_DB_DATA_PATH,
user=CDS_USER,
password=CDS_PASS,
charset='WIN1251')
self.cds_db_data.default_tpb = fdb.ISOLATION_LEVEL_READ_COMMITED_RO
self.logger.info(
f"Success connect to {CDS_HOST} {CDS_DB_DATA_PATH}")
except Exception as general_error:
self.logger.error(general_error)
def ret_cursor(tipo):
if (tipo == 'fb'):
conn = fdb.connect(host='192.168.0.20',
database='/home/bd2/Bco_dados/CONTESP.GDB',
user='******',
password='******')
cursor = conn.cursor()
if (tipo == 'fin'):
conn = fdb.connect(host='192.168.0.20',
database='/home/bd2/Bco_dados/CONTROLECONTAS.GDB',
user='******',
password='******')
cursor = conn.cursor()
if (tipo == 'do'):
import pyodbc
conn = pyodbc.connect("DSN=Contabil;UID=root;PWD=server")
conn.timeout = 60
cursor = conn.cursor()
if (tipo == 'doproducao'):
import pyodbc
conn = pyodbc.connect("DSN=serverbd1;UID=root;PWD=server")
cursor = conn.cursor()
if (tipo == 'dotemporario'):
import pyodbc
conn = pyodbc.connect("DSN=espelho;UID=root;PWD=server")
cursor = conn.cursor()
return cursor
def conectafdb(database):
fdb.connect(
dsn=database,
user='******',
password='******'
)
return print("oi")
def main():
print len(sys.argv)
if len(sys.argv) <2:
print "Для запуска набери: ./processing.py loadrbd|process|get"
print ' loadrbd - Загрузка новых данных из РБД и очистка таблицы от предыдущей версии'
print ' process - Поиск соответвий реестров из ГИБДД с данными из РБД'
print ' get - Выгрузка реестров для загрузки в подразделениях'
sys.exit(2)
logging.basicConfig(format = u'%(levelname)-8s [%(asctime)s] %(message)s',level = logging.DEBUG, filename = './processing.log')
fileconfig=file('./config.xml')
xml=etree.parse(fileconfig)
xmlroot=xml.getroot()
main_database=xmlroot.find('main_database')
main_dbname=main_database.find('dbname').text
main_user=main_database.find('user').text
main_password=main_database.find('password').text
main_host=main_database.find('hostname').text
rbd_database=xmlroot.find('rbd')
rbd_dbname=rbd_database.find('dbname').text
rbd_user=rbd_database.find('user').text
rbd_password=rbd_database.find('password').text
rbd_host=rbd_database.find('hostname').text
nd=xmlroot.find('output_path')
output_path=nd.text
nd=xmlroot.find('output_path2')
output_path2=nd.text
clm=' , '
cm=';'
fileconfig.close()
sql2="SELECT doc_ip_doc.id, document.doc_number, doc_ip_doc.id_dbtr_name,(REPLACE (doc_ip_doc.id_docno,' ','')) as NUMDOC ,doc_ip.id_debtsum,document.docstatusid FROM DOC_IP_DOC DOC_IP_DOC JOIN DOC_IP ON DOC_IP_DOC.ID=DOC_IP.ID JOIN DOCUMENT ON DOC_IP.ID=DOCUMENT.ID " # where upper (REPLACE (doc_ip_doc.id_docno,' ','')) ="
sql='SELECT * FROM reestrs where status=0'
sql4="select 'update reestrs set reestrs.num_ip='''||doc_ip_doc.doc_number|| ''', reestrs.osp='''|| substring (doc_ip_doc.id from 1 for 4)|| ''' whrere reestrs.id='|| reestrs.id ||';' from reestrs reestrs join doc_ip_doc on reestrs.num_id=doc_ip_doc.numdoc and doc_ip_doc.docstatusid=9"
sql5=u"SELECT cast('INSERT INTO docipdoc (ID, DOC_NUMBER, ID_DBTR_NAME, NUMDOC, ID_DEBTSUM, DOCSTATUSID) VALUES ('|| doc_ip_doc.id ||', ''' || document.doc_number||''', '''|| doc_ip_doc.id_dbtr_name||''',''' ||(REPLACE (doc_ip_doc.id_docno,' ','')) ||''','|| doc_ip.id_debtsum||' , '|| document.docstatusid||');' as varchar(1000)) FROM DOC_IP_DOC DOC_IP_DOC JOIN DOC_IP ON DOC_IP_DOC.ID=DOC_IP.ID JOIN DOCUMENT ON DOC_IP.ID=DOCUMENT.ID where "# (doc_ip.ip_risedate<'01.01.2013' and document.docstatusid=9) or (doc_ip.ip_risedate>='01.01.2013')"
sql6="SELECT doc_ip_doc.id , document.doc_number, doc_ip_doc.id_dbtr_name,(REPLACE (doc_ip_doc.id_docno,' ','')) ,doc_ip.id_debtsum, document.docstatusid,doc_ip.ip_exec_prist_name FROM DOC_IP_DOC DOC_IP_DOC JOIN DOC_IP ON DOC_IP_DOC.ID=DOC_IP.ID JOIN DOCUMENT ON DOC_IP.ID=DOCUMENT.ID where"
sql7="INSERT INTO docipdoc (ID, DOC_NUMBER, ID_DBTR_NAME, NUMDOC, ID_DEBTSUM, DOCSTATUSID,ip_exec_prist_name) VALUES (?,?,?,?,?,?,?)"
sql8="select docipdoc.doc_number, substring (docipdoc.id from 1 for 4), reestrs.id, docipdoc.ip_exec_prist_name from reestrs reestrs join docipdoc on (reestrs.num_id=docipdoc.numdoc and docipdoc.docstatusid=9 and reestrs.status=0)"
sql9="select docipdoc.doc_number, substring (docipdoc.id from 1 for 4), reestrs.id, docipdoc.ip_exec_prist_name from reestrs reestrs join docipdoc on (reestrs.num_id=docipdoc.numdoc and docipdoc.docstatusid<>9 and reestrs.status=0)"
#cur.execute(sql)
start_date='01.01.2013'
if sys.argv[1]=='loadrbd':
try:
con = fdb.connect (host=main_host, database=main_dbname, user=main_user, password=main_password,charset='WIN1251')
except Exception, e:
print("Ошибка при открытии базы данных:\n"+str(e))
sys.exit(2)
try:
con2 = fdb.connect (host=rbd_host, database=rbd_dbname, user=rbd_user, password=rbd_password,charset='WIN1251')
except Exception, e:
print("Ошибка при открытии базы данных:\n"+str(e))
sys.exit(2)
def main():
print len(sys.argv)
if len(sys.argv) <2:
print "Для запуска набери: ./processing.py loadrbd|process|get"
print ' loadrbd - Загрузка новых данных из РБД'
print ' group - Группировка'
print ' get <КОД ОТДЕЛА> <КОЛ-ВО> - Выгрузка Файлов для ЗАГС'
print ' delete Очистка '
print ' upload - Загрузка ответов'
print ' process - Поиск соответвий реестров из ЗАГС с данными из РБД'
print ' download Выгрузка ответов в ОСП'
print sys.argv
sys.exit(2)
logging.basicConfig(format = u'%(levelname)-8s [%(asctime)s] %(message)s',level = logging.DEBUG, filename = './processing.log')
fileconfig=file('./config.xml')
xml=etree.parse(fileconfig)
xmlroot=xml.getroot()
main_database=xmlroot.find('main_database')
main_dbname=main_database.find('dbname').text
main_user=main_database.find('user').text
main_password=main_database.find('password').text
main_host=main_database.find('hostname').text
rbd_database=xmlroot.find('rbd')
rbd_dbname=rbd_database.find('dbname').text
rbd_user=rbd_database.find('user').text
rbd_password=rbd_database.find('password').text
rbd_host=rbd_database.find('hostname').text
output_scheme=xmlroot.find('output_scheme');
nd=xmlroot.find('output_path')
output_path=nd.text
nd=xmlroot.find('output_path2')
output_type=xmlroot.find('output_type').text
output_path2=nd.text
nd=xmlroot.find('output_path3')
output_path3=nd.text
nd=xmlroot.find('input_path')
input_path=nd.text
nd=xmlroot.find('input_arc_path')
input_arc_path =nd.text
sq1="SELECT doc_ip_doc.id , document.doc_number, trim(doc_ip_doc.id_dbtr_name),entity.entt_firstname,entity.entt_patronymic, entity.entt_surname,doc_ip_doc.id_dbtr_born, doc_ip.id_debtsum, document.docstatusid, doc_ip.ip_exec_prist_name FROM DOC_IP_DOC DOC_IP_DOC JOIN DOC_IP ON DOC_IP_DOC.ID=DOC_IP.ID JOIN DOCUMENT ON DOC_IP.ID=DOCUMENT.ID join entity on doc_ip.id_dbtr=entity.entt_id where document.docstatusid=9 and DOC_IP_DOC.ID_DBTR_ENTID IN (2,71,95,96,97,666) and doc_ip_doc.id_dbtr_born is not null and doc_ip_doc.id_dbtr_born >='01.01.1900' and doc_ip.ip_risedate<'14.10.2011' "# and doc_ip.id_debtsum>=3000"
if sys.argv[1]=='loadrbd':
try:
con = fdb.connect (host=main_host, database=main_dbname, user=main_user, password=main_password,charset='WIN1251')
except Exception, e:
print("Ошибка при открытии базы данных:\n"+str(e))
sys.exit(2)
try:
con2 = fdb.connect (host=rbd_host, database=rbd_dbname, user=rbd_user, password=rbd_password,charset='WIN1251')
except Exception, e:
print("Ошибка при открытии базы данных:\n"+str(e))
sys.exit(2)
def __init__(self, logger):
self.logger = logger
self.cds_db_project = fdb.connect(host=CDS_HOST,
database=CDS_DB_PROJECTS_PATH,
user=CDS_USER,
password=CDS_PASS,
charset='WIN1251')
self.cds_db_data = fdb.connect(host=CDS_HOST,
database=CDS_DB_DATA_PATH,
user=CDS_USER,
password=CDS_PASS,
charset='WIN1251')
self.cds_db_project.default_tpb = fdb.ISOLATION_LEVEL_READ_COMMITED_RO
self.cds_db_data.default_tpb = fdb.ISOLATION_LEVEL_READ_COMMITED_RO
def list(self, request):
"""
Metodo para mostrar la lista
"""
name = request.GET.get('nombre')
result = []
if name:
if len(name) < 3:
return Response(
{'result': 'La cantidad minima de caracteres es 3'},
status=400)
name = name.upper()
con = fdb.connect(host='localhost',
port=3050,
database='/home/michel/Downloads/EXCEL.FDB',
user='******',
password='******')
cursor = con.cursor()
cursor.execute(self.QUERY_CLIENTES +
"where CLIENTES.NOMBRE like '%{}%'".format(name))
records = cursor.fetchall()
columns = [column[0] for column in cursor.description]
cursor.close()
result = []
for row in records:
result.append(dict(zip(columns, row)))
if not result:
return Response({'result': 'Cliente no encontrado'},
status=404)
return Response({'data': result}, status=200)
return Response({'result': 'Error en parametros recibidos'},
status=400)
def etl():
f_con = fdb.connect(dsn="localhost:/var/lib/firebird/3.0/data/cplus.fdb",
user="******",
password="******",
charset="iso8859_1")
vendedor_cplus = pd.read_sql_query(
"select CODVENDED, CODIGO, NOMEVENDED as NOME from VENDEDOR", f_con)
vendedor = VendedorModel()
dim = VendedorDimension()
#cadastra vendedor indefinido
vendedor.codigo = '000000'
vendedor.codvended = '00000000'
vendedor.nome = 'indefinido'
dim.insere(vendedor)
dim.commit()
total = len(vendedor_cplus.index)
for index in range(0, total):
vendedor.codigo = vendedor_cplus.iloc[index]['CODIGO']
vendedor.codvended = vendedor_cplus.iloc[index]['CODVENDED']
vendedor.nome = vendedor_cplus.iloc[index]['NOME']
dim.insere(vendedor)
dim.commit()
def get_db():
if not hasattr(g, "fb_db"):
g.fb_db = fdb.connect(dsn='db/TIMETABLE.fdb',
user='******',
password='******',
charset=u'UTF8')
return g.fb_db
def conecta():
con = fdb.connect(
host='localhost',
database='/Users/Administrator/Desktop/VisionQuarta1259/SDFSDF.FDB',
user='******',
password='******')
return con.cursor()
def checkIfApplyValid(self):
if not os.path.exists(self.leDBFile.text()):
self.show_error("Caminho do arquivo do banco de dados invalido")
return
if not os.path.exists(self.leOutuput.text()):
self.show_error("Caminho de saida do arquivo invalido")
return
if not self.leLogin.text():
self.show_error("O login do banco de dados não pode estar vazio")
return
if not self.lePassword.text():
self.show_error("A senha do banco de dados não pode estar vazia")
return
try:
con = fdb.connect(self.leDBFile.text(), self.leLogin.text(),
self.lePassword.text())
cur = con.cursor()
except BaseException as e:
self.show_error(str(e))
return
self.db.save('leDBFile', self.leDBFile.text())
self.db.save('leLogin', self.leLogin.text())
self.db.save('lePassword', self.lePassword.text())
self.db.save('leOutuput', (self.leOutuput.text()))
self.close()
async def update_driver_terminal_oper(term_id, term_opertime,
term_pay_system_id, term_summ, comment,
oper_id):
UPDATE = 'update driver_oper set term_operation=1, term_id=%s, term_opertime=\'%s\'' % (
term_id, term_opertime)
if term_pay_system_id:
# id платёжной системы
UPDATE += ', term_pay_system_id=%s' % term_pay_system_id
if term_summ:
# Внесённая сумма
UPDATE += ', term_summ=%s' % term_summ
if comment:
UPDATE += ', comment=\'%s\'' % comment
UPDATE += ' where id=%s' % oper_id
with (await db_semaphore), fdb.connect(**settings.FDB) as db:
try:
with db.cursor() as c:
c.execute(UPDATE)
db.commit()
logger.info(f'driver_opers.id={oper_id} updated')
updated = True
except Exception as e:
db.rollback()
logger.info(f'driver_opers.id={oper_id} updating error {e}')
updated = True
return updated
def conn_open(self):
"""The connection is open here."""
self.ensure_one()
# Get dbsource record
# Build the full connection string
connStr = self.conn_string
if self.password:
if '%s' not in self.conn_string:
connStr += ';PWD=%s'
connStr = connStr % self.password
# Try to connect
if self.connector == 'cx_Oracle':
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
conn = cx_Oracle.connect(connStr)
elif self.connector == 'pyodbc':
conn = pyodbc.connect(connStr)
elif self.connector in ('sqlite', 'mysql', 'mssql'):
conn = sqlalchemy.create_engine(connStr).connect()
elif self.connector == 'fdb':
kwargs = dict([x.split('=') for x in connStr.split(';')])
conn = fdb.connect(**kwargs)
elif self.connector == 'postgresql':
conn = psycopg2.connect(connStr)
return conn
def __init__(self, hos, por, db, usr, passw):
self.conn = fdb.connect(host=hos,
port=por,
database=db,
user=usr,
password=passw)
self.cur = self.conn.cursor()
def getIDs(id_list):
"""
La función getIDs permite obtener los identificadores reales de los informes.
Utiliza una query que apunta a una tabla de la base de datos de la cual se obtienen los ID reales.
La query requiere de una dirección dsn, nombre de usuario y password. No indicados por seguridad.
El parámetro id_list es la lista de PDF mapeados.
El programa devuelve una lista de identificadores correctos para cada informe/paciente.
"""
r_id = []
select = """
select
p.PETICIONCB
from
PETICION p
where
p.IDPETICION = '%s'
"""
for number in id_list:
query = select % number
con = fdb.connect(dsn='XXX', user='******', password='******')
cur = con.cursor()
cur.execute(query)
rows = cur.fetchall()
r_id.append(rows)
return r_id
def check_condition(host, destport, alias, username, password):
"""
Tries to connect to the given firebird database and execute a SQL statement
which should return a list of available tables. Be aware this doesn't
work if your database has no tables.
@param host : hostname/ip
@param alias : database alias
@param username: username
@param password: password
"""
try :
dsnstring = host + "/" + destport + ":" + alias
connection = fdb.connect(dsn=dsnstring,
user=username,
password=password)
connection.begin()
version = connection.server_version
# Execute an sql on the connection
cur = connection.cursor()
cur.execute('SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS'
' WHERE RDB$SYSTEM_FLAG=0;')
if len(cur.fetchall()) < 1:
connection.close()
return {'code': 'CRITICAL',
'message': dsnstring + ' problem with the database,'
' maybe corrupted!'}
connection.close()
except fdb.OperationalError, msg:
return {'code': 'CRITICAL',
'message': dsnstring + ' ' + msg[1].rstrip('\n')}
def main():
logging.basicConfig(format = u'%(levelname)-8s [%(asctime)s] %(message)s',level = logging.DEBUG, filename = './upload.log')
fileconfig=file('./config.xml')
xml=etree.parse(fileconfig)
xmlroot=xml.getroot()
nd=xmlroot.find('input_path')
input_path=nd.text
nd=xmlroot.find('input_arc_path')
input_arc_path=nd.text
main_database=xmlroot.find('main_database')
main_dbname=main_database.find('dbname').text
main_user=main_database.find('user').text
main_password=main_database.find('password').text
main_host=main_database.find('hostname').text
rbd_database=xmlroot.find('rbd')
rbd_dbname=rbd_database.find('dbname').text
rbd_user=rbd_database.find('user').text
rbd_password=rbd_database.find('password').text
rbd_host=rbd_database.find('hostname').text
clm=' , '
try:
con = fdb.connect (host=main_host, database=main_dbname, user=main_user, password=main_password,charset='WIN1251')
except Exception, e:
print("Ошибка при открытии базы данных:\n"+str(e))
sys.exit(2)
def pesquisa_reserva(controle):
configuracao = Configuracao()
con = fdb.connect(dsn=configuracao.db_host + ':' +
configuracao.db_path,
user=configuracao.db_user,
password=configuracao.db_password,
fb_library_name=configuracao.db_lib)
cur = con.cursor()
qry_sync = (
"SELECT AGENDA.CONTROLE, AGENDA.ID_WEB "
", PROFISSIONAIS.ID_WEB AS PROFISSIONAL_IDWEB "
" , AGENDA.HORARIO, AGENDA.FIM "
" , AGENDA.DATA, AGENDA.SERVICOS, AGENDA.STATUS, AGENDA.FIXO "
" FROM AGENDA "
" LEFT JOIN PROFISSIONAIS ON PROFISSIONAIS.codigo_profissional = AGENDA.codigo_profissional "
" AND PROFISSIONAIS.codigo_empresa = AGENDA.emp_profissional "
" WHERE AGENDA.CONTROLE = %s ")
cur.execute(qry_sync % controle)
columns = [column[0] for column in cur.description]
results = []
for row in cur.fetchall():
results.append(dict(zip(columns, row)))
return results
def UpdateMainTable(MainTable,StagingTable):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="INSERT INTO "+MainTable+"(DATERECEIVED,PRODUCT,SUBPRODUCT,ISSUE,SUBISSUE,CONSUMERCOMPLAINT,COMPANYPUBLICRESPONSE,"
SQLComment +="COMPANY,STATE,ZIPCODE,SUBMITTEDVIA,DATESENTCOMPANY,COMPANYRESPONSECONSUMER,TIMELYRESPONSESTS,CONSUMERDISPUTEDSTS,COMPLAINTID)"
SQLComment +=' SELECT '
SQLComment +='CASE a.DATERECEIVED WHEN \'nan\' THEN NULL ELSE CAST(a.DATERECEIVED AS DATE) END DATERECEIVED,'
SQLComment +='CASE a.PRODUCT WHEN \'nan\' THEN NULL ELSE a.PRODUCT END PRODUCT,'
SQLComment +='CASE a.SUBPRODUCT WHEN \'nan\' THEN NULL ELSE a.SUBPRODUCT END SUBPRODUCT, '
SQLComment +='CASE a.ISSUE WHEN \'nan\' THEN NULL ELSE a.ISSUE END ISSUE,'
SQLComment +='CASE a.SUBISSUE WHEN \'nan\' THEN NULL ELSE a.SUBISSUE END SUBISSUE,'
SQLComment +='CASE a.CONSUMERCOMPLAINT WHEN \'nan\' THEN NULL ELSE a.CONSUMERCOMPLAINT END CONSUMERCOMPLAINT,'
SQLComment +='CASE a.COMPANYPUBLICRESPONSE WHEN \'nan\' THEN NULL ELSE a.COMPANYPUBLICRESPONSE END COMPANYPUBLICRESPONSE,'
SQLComment +='CASE a.COMPANY WHEN \'nan\' THEN NULL ELSE a.COMPANY END COMPANY,'
SQLComment +='CASE a.STATE WHEN \'nan\' THEN NULL ELSE a.STATE END STATE,'
SQLComment +='CASE a.ZIPCODE WHEN \'nan\' THEN NULL ELSE a.ZIPCODE END ZIPCODE,'
SQLComment +='CASE a.SUBMITTEDVIA WHEN \'nan\' THEN NULL ELSE a.SUBMITTEDVIA END SUBMITTEDVIA,'
SQLComment +='CASE a.DATESENTCOMPANY WHEN \'nan\' THEN NULL ELSE CAST(a.DATESENTCOMPANY AS DATE) END DATESENTCOMPANY,'
SQLComment +='CASE a.COMPANYRESPONSECONSUMER WHEN \'nan\' THEN NULL ELSE a.COMPANYRESPONSECONSUMER END COMPANYRESPONSECONSUMER,'
SQLComment +='CASE a.TIMELYRESPONSESTS WHEN \'nan\' THEN NULL ELSE a.TIMELYRESPONSESTS END TIMELYRESPONSESTS,'
SQLComment +='CASE a.CONSUMERDISPUTEDSTS WHEN \'nan\' THEN NULL ELSE a.CONSUMERDISPUTEDSTS END CONSUMERDISPUTEDSTS,'
SQLComment +='a.COMPLAINTID '
SQLComment +='FROM '+ StagingTable+" a "
SQLComment +="WHERE a.COMPLAINTID NOT IN (SELECT COMPLAINTID FROM "+MainTable+")"
fbInsMain = cur.prep(SQLComment)
cur.execute(fbInsMain)
con.commit()
def retrieve(self, request, pk=None):
"""
Get
"""
result = []
if not pk.isdigit():
return Response({'result': 'El ID tiene que ser un numérico'},
status=404)
if pk:
con = fdb.connect(host='localhost',
port=3050,
database='/home/michel/Downloads/EXCEL.FDB',
user='******',
password='******')
cursor = con.cursor()
cursor.execute(self.QUERY_ARTICULOS +
"where ARTICULOS.ARTICULO_ID like '{}'".format(pk))
record = cursor.fetchone()
if record:
columns = [column[0] for column in cursor.description]
cursor.close()
result = dict(zip(columns, record))
else:
return Response({'result': 'ID no encontrado'}, status=404)
return Response(result)
def _cursor(self):
if self.connection is None:
settings_dict = self.settings_dict
if settings_dict['NAME'] == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = {'charset': 'UTF8'}
conn_params['dsn'] = settings_dict['NAME']
if settings_dict['HOST']:
conn_params['dsn'] = ('%s:%s') % (settings_dict['HOST'],
conn_params['dsn'])
if settings_dict['PORT']:
conn_params['port'] = settings_dict['PORT']
if settings_dict['USER']:
conn_params['user'] = settings_dict['USER']
if settings_dict['PASSWORD']:
conn_params['password'] = settings_dict['PASSWORD']
options = settings_dict['OPTIONS'].copy()
conn_params.update(options)
self._db_charset = conn_params.get('charset')
self.encoding = charset_map.get(self._db_charset, 'utf_8')
self.connection = Database.connect(**conn_params)
connection_created.send(sender=self.__class__)
return FirebirdCursorWrapper(self.connection.cursor(), self.encoding)
def getreq (dbm,sql): rez=[] pp=dbm try: con = fdb.connect (host=pp['host'], database=pp['db'], user='******', password=pp['password'],charset='WIN1251') except Exception, e: print pp['host'],pp['db'],pp['password'],'FAIL',e
def pesquida_agenda(controle):
configuracao = Configuracao()
con = fdb.connect(dsn=configuracao.db_host + ':' +
configuracao.db_path,
user=configuracao.db_user,
password=configuracao.db_password,
fb_library_name=configuracao.db_lib)
cur = con.cursor()
qry_sync = (
"SELECT AGENDA.CONTROLE, AGENDA.ID_WEB "
", PROFISSIONAIS.ID_WEB AS PROFISSIONAL_IDWEB, CLIENTES.ID_WEB AS CLIENTE_IDWEB "
" , AGENDA.HORARIO, AGENDA.FIM, AGENDA.CODIGO_CLIENTE, AGENDA.EMP_CLIENTE "
" , AGENDA.DATA, AGENDA.SERVICOS, AGENDA.STATUS, AGENDA.FIXO, UDF_COLLATEBR(AGENDA.NOME_CLIENTE) AS NOME_CLIENTE "
" FROM AGENDA "
" LEFT JOIN CLIENTES ON CLIENTES.codigo_cliente = AGENDA.codigo_cliente "
" AND CLIENTES.codigo_empresa = AGENDA.emp_cliente "
" LEFT JOIN PROFISSIONAIS ON PROFISSIONAIS.codigo_profissional = AGENDA.codigo_profissional "
" AND PROFISSIONAIS.codigo_empresa = AGENDA.emp_profissional "
" WHERE AGENDA.CONTROLE = {controle} ".format(controle=controle))
cur.execute(qry_sync)
columns = [column[0] for column in cur.description]
results = []
for row in cur.fetchall():
results.append(dict(zip(columns, row)))
return results
def run():
connection_args = data_processing.read_config("connection_config.txt")
query = data_processing.render_query(False)
# query = "SELECT * FROM VIEW_OKNO_ZAKU;"
# query = "select * from VIEW_OKNO_FAKT;"
# query = "SELECT RDB$RELATION_NAME, RDB$DESCRIPTION FROM RDB$RELATIONS;"
# header, data = data_processing.read_database(connection_args, query)
con = fdb.connect(**connection_args)
cur = con.cursor()
cur.execute(query)
# Print a header.
for fieldDesc in cur.description:
print(fieldDesc[fdb.DESCRIPTION_NAME].ljust(
fieldDesc[fdb.DESCRIPTION_DISPLAY_SIZE]),
end=" ")
print() # Finish the header with a newline.
print('-' * 78)
# For each row, print the value of each field left-justified within
# the maximum possible width of that field.
fieldIndices = range(len(cur.description))
for row in cur:
for fieldIndex in fieldIndices:
fieldValue = str(row[fieldIndex])
fieldMaxWidth = cur.description[fieldIndex][
fdb.DESCRIPTION_DISPLAY_SIZE]
print(fieldValue.ljust(fieldMaxWidth), end=" ")
print() # Finish the row with a newline.
def connectar_unifiltro():
return fdb.connect(
host='localhost',
database='DATABASE.FDB',
user='******',
password='******'
)
def __init__(self, name):
self._conn = fdb.connect(
dsn = name,
user ='******', password='******',
charset='UTF8'
)
self._cursor = self._conn.cursor()
def _cursor(self):
if self.connection is None:
settings_dict = self.settings_dict
if settings_dict['NAME'] == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = {'charset': 'UTF8'}
conn_params['dsn'] = settings_dict['NAME']
if settings_dict['HOST']:
conn_params['dsn'] = ('%s:%s') % (settings_dict['HOST'], conn_params['dsn'])
if settings_dict['PORT']:
conn_params['port'] = settings_dict['PORT']
if settings_dict['USER']:
conn_params['user'] = settings_dict['USER']
if settings_dict['PASSWORD']:
conn_params['password'] = settings_dict['PASSWORD']
options = settings_dict['OPTIONS'].copy()
conn_params.update(options)
self._db_charset = conn_params.get('charset')
self.encoding = charset_map.get(self._db_charset, 'utf_8')
self.connection = Database.connect(**conn_params)
connection_created.send(sender=self.__class__)
return FirebirdCursorWrapper(self.connection.cursor(), self.encoding)
def qe(query, traOpt=ISOLATION_LEVEL_READ_COMMITED):
conn = fdb.connect(dsn=database,
user='******',
password='******',
fb_library_name=fbClient)
conn.begin(traOpt)
cursor = conn.cursor()
tr = conn.transaction_info(isc_info_tra_id, 'i')
try:
print(f'T{tr} EXECUTING: {query}')
cursor.execute(query)
if query.startswith('select'):
res = []
try:
res = cursor.fetchall()
print(f'T{tr} RESULT OF: {query}')
for r in res:
print(r)
except Exception as e:
print(f'T{tr} FETCH ERROR: {e}')
else:
print(f'T{tr} EXECUTED: {query}')
except Exception as e:
print(f'T{tr} EXECUTE ERROR: {e}')
return conn
def is_available(self):
codigo_profissional, emp_profissional = self.get_id_profissional()
con1 = fdb.connect(
dsn=self.configuracao.db_host + ':' + self.configuracao.db_path,
user=self.configuracao.db_user, password=self.configuracao.db_password, fb_library_name=self.configuracao.db_lib)
cursor = con1.cursor()
qry = ( "SELECT CONTROLE FROM AGENDA "
"WHERE DATA = '%s' "
" AND CODIGO_PROFISSIONAL = %d "
" AND EMP_PROFISSIONAL = %d "
" AND (((HORARIO <= " + self.inicio + ") AND (FIM > " + self.inicio + ")) OR "
" ((HORARIO < " + self.fim + ") AND (FIM >= " + self.fim + ")) OR "
" ((HORARIO >= " + self.inicio + ") AND (FIM <= " + self.fim + "))) ")
cursor.execute(qry % ( self.data, codigo_profissional, emp_profissional))
#row = cursor.fetchall()
#return row
#cursor.close()
#con1.close()
columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
results.append(dict(zip(columns, row)))
return results
def run(self) -> None:
"""Task execution.
"""
def recompute(idx):
if self.verbose:
self.info("Rebuilding index '%s'" % idx.name)
try:
# deactivate
sqlcmd = idx.get_sql_for('deactivate')
con.begin()
cursor.execute(sqlcmd)
con.commit()
# activate
sqlcmd = idx.get_sql_for('activate')
con.begin()
cursor.execute(sqlcmd)
con.commit()
except Exception as exc:
self.exception(str(exc))
self.logger.debug(
"Connecting (database:%s, host:%s, user:%s, password:%s)" %
(self.config.database, self.config.host, self.config.user,
self.config.password))
con = fdb.connect(database=self.config.database,
user=self.config.user,
password=self.config.password,
host=self.config.host)
cursor = con.cursor()
try:
for idx in con.schema.indices:
recompute(idx)
finally:
con.close()
self.logger.debug("Database connection closed")
def doRun(s):
print "doRun, s=", s
f = open(thispath + '/mostoftenpattern_' + s + '.job', 'w')
f.write("")
f.close()
f = open(thispath + '/mostoftenpattern_' + s + '.txt', 'w')
con = fdb.connect(host='127.0.0.1',
database='/opt/firebird/lotto.fdb',
user='******',
password='******')
#con = kinterbasdb.connect(dsn='localhost:/opt/firebird/lotto.fdb',user='******',password='******')
cur = con.cursor()
cur.execute("select * from pattern_pair_" + s)
rs = cur.fetchallmap()
t1 = doCalc(rs)
showCalc2("pair", s, t1, f)
cur.execute("select * from pattern_triple_" + s)
rs = cur.fetchallmap()
t2 = doCalc(rs)
f.write("-----\n")
f.flush()
showCalc2("triple", s, t2, f)
f.close()
con.close()
os.remove(thispath + '/mostoftenpattern_' + s + '.job')
print "Done"
def insere_cliente(self, cliente):
cliente_nome = cliente["nome"].replace("'", "")
con = fdb.connect(
dsn=self.configuracao.db_host + ':' + self.configuracao.db_path,
user=self.configuracao.db_user, password=self.configuracao.db_password, fb_library_name=self.configuracao.db_lib)
cur = con.cursor()
add_cliente = ("insert into clientes (codigo_cliente, codigo_empresa, data_cadastro "
", data_cliente, data_nascimento, apelido "
", nome, telefone1, email, nao_tem_email "
", grupo , emp_grupo, veiculo, emp_veiculo "
" , d_produtos, p_produtos, d_servicos "
" , p_servicos, mala_direta, status "
" , debitos, creditos "
" , gasto_total, gasto_medio, cartao_fidelidade "
" , estado_civil, atacado, pontuacao, emissao_rps "
" , id_web) "
" values ( GEN_ID(GEN_CLIENTE,1), {codigo_empresa}, CURRENT_DATE "
" , CURRENT_DATE, null, UDF_UPPER(UDF_COLLATEBR('{apelido}')) "
" , UDF_UPPER(UDF_COLLATEBR('{nome}')), '{telefone}', '{email}', 'N' "
" , 1, 1, 1, 1 "
" , 'N', 0, 'N' "
" , 0, 'N', 'A' "
" , 0, 0 "
" , 0, 0, 'N' "
" , 'SOLTEIRO(A)', 'N', 0, 'N' "
" , {id_web}) ").format(codigo_empresa=self.configuracao.emp_padrao, apelido=cliente_nome[:15], nome=cliente_nome[:40],telefone= cliente["celular"], email=cliente["email"], id_web=cliente["id"])
cur.execute(add_cliente)
con.commit()
cur.close()
con.close()
def generate_keypair():
"""
Generates the repositories public/private keypair and stores it in the
database.
"""
print ("Generating keypair...")
# generate keypair
key = RSA.generate(1024, os.urandom)
pub = key.publickey().exportKey()
priv = key.exportKey()
try:
# establish database connection
connection = fdb.connect(
host=config["db.ip"], database=config["db.path"], user=config["db.user"], password=config["db.password"]
)
# save public key
cursor = connection.cursor()
cursor.execute("INSERT INTO CONFIG (PARAM, VAL) VALUES (?,?);", ("publickey", pub))
connection.commit()
# save private key
cursor = connection.cursor()
cursor.execute("INSERT INTO CONFIG (PARAM, VAL) VALUES (?,?);", ("privatekey", priv))
connection.commit()
connection.close()
except fdb.fbcore.DatabaseError, e:
print "Failed to access database!"
sys.exit(1)
def main():
arr = []
for i in range(0, 49):
arr.append(i + 1)
t49 = makePairsAndTriplesArr(arr)
nd2_49 = t49['D2']
nd3_49 = t49['D3']
writeOutMatches(nd2_49, 'matches_2_49.txt')
writeOutMatches(nd3_49, 'matches_3_49.txt')
arr.append(50)
t50 = makePairsAndTriplesArr(arr)
nd2_50 = t50['D2']
nd3_50 = t50['D3']
writeOutMatches(nd2_50, 'matches_2_50.txt')
writeOutMatches(nd3_50, 'matches_3_50.txt')
con = fdb.connect(host='127.0.0.1',
database='/opt/firebird/lotto.fdb',
user='******',
password='******')
#con = kinterbasdb.connect(dsn='localhost:/opt/firebird/lotto.fdb',user='******',password='******')
cur = con.cursor()
dbs = ['lotto649', 'lottomax', 'bc49']
for db in dbs:
print "doing db=", db
try:
cur.execute("delete from pattern_pair_" + db)
except:
continue # it is because this table doesn't exist yet, as this program was changed over time to accomodate new lottery types
cur.execute("delete from pattern_triple_" + db)
con.commit()
cur.execute("select * from " + db + " order by drawdate asc")
rs = cur.fetchallmap()
lc = 0
for r in rs:
arr = []
cn = 7
if db == 'lottomax':
cn = 8
for i in range(1, cn):
nf = 'NUM' + str(i)
nv = r[nf]
arr.append(nv)
dt = r['DRAWDATE']
print r['DRAWDATE'], ': ', str(arr)
t = makePairsAndTriplesArr(arr)
d2arr = t['D2']
d3arr = t['D3']
for d in d2arr:
sql = "insert into pattern_pair_" + db + " (pattern,drawdate) values ('" + d + "','" + dt + "')"
print lc, sql
lc += 1
cur.execute(sql)
for d in d3arr:
sql = "insert into pattern_triple_" + db + " (pattern,drawdate) values ('" + d + "','" + dt + "')"
print lc, sql
lc += 1
cur.execute(sql)
con.commit()
con.close()
print "Done"
def __init__(self,
path='baza/Baza.fdb',
user='******',
password='******'): # standard firebird password
self.con = fdb.connect(database=path, user=user, password=password)
self.cur = self.con.cursor()
self.table_format = 'FORMATKI'
self.table_group = 'ZESTAWY'
self.table_format_group = 'ZESTAWY_FORMATKI'
self.table_boards = 'PLYTY'
self.table_boards_columns = 'ID_PLYTY, OPIS'
self.sheet = 'Plyty'
self.format_columns = translate.get_table_headers(
self.table_format, self.con, self.cur
) # check for available table headers, this fixes version issues
self.group_columns = translate.get_table_headers(
self.table_group, self.con, self.cur)
self.format_group_columns = translate.get_table_headers(
self.table_format_group, self.con, self.cur)
self.boards_values = translate.get_columns_values(
self.table_boards, self.table_boards_columns, self.con, self.cur)
try:
Config().remove('db_boards')
for i in range(len(self.boards_values)):
Config().update('db_boards', self.boards_values[i][1],
self.boards_values[i][0])
except:
print('Updating settings.ini for existing boards failed')
def UpdateMainLog(LogTable,LoadIndex,LoadOrder,LoadStep,StepName,Status,RecordCount,LoadStartDTTM,LoadEndDTTM):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="insert into "+LogTable+" (LoadIndex,LoadOrder,LoadStep,StepName,Status,RecordCount,LoadStartDTTM,LoadEndDTTM) values (?,?,?,?,?,?,?,?)"
fbLog=cur.prep(SQLComment)
cur.execute(fbLog, (LoadIndex,LoadOrder,LoadStep,StepName,Status,RecordCount,LoadStartDTTM,LoadEndDTTM))
con.commit()
def UpdateCopyTable(MainTable,CopyTable):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="INSERT INTO "+CopyTable+" SELECT * FROM "+MainTable
fbInsCopy = cur.prep(SQLComment)
cur.execute(fbInsCopy)
con.commit()
def _destroy_test_db(self, test_database_name, verbosity):
"""
Internal implementation - remove the test db tables.
"""
self._check_active_connection(verbosity)
connection = Database.connect(**self._get_connection_params(database=test_database_name))
connection.drop_database()
connection.close()
def DeleteData(TableName):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="delete from "+TableName
fbDel = cur.prep(SQLComment)
cur.execute(fbDel)
con.commit()
def connect(database, user, password, host, port, encoding):
if not encoding:
encoding = None
if not port:
port = None
else:
port = int(port)
return fdb.connect(database=database, user=user, password=password, charset=encoding, host=host, port=port)
def __init__(self, clinic_id = 22, mis_user = MIS_USER, mis_user_pwd = MIS_USER_PWD, mis_host = HOST, mis_db = DB):
s_dsn = "%s:%s" % (mis_host, mis_db)
try:
self.con = fdb.connect(dsn=s_dsn, user=DB_USER, password=DB_PWD, role = DB_ROLE, charset='WIN1251')
self.cur = self.con.cursor()
except Exception, e:
exctype, value = sys.exc_info()[:2]
log.warn( 'DBMIS init error: {0}'.format(e) )
raise
def GetLoadIndex(LogTable):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
IdSelComment="SELECT COALESCE(max(LoadIndex),0) FROM "+LogTable
idcur=con.cursor()
idcur.execute(IdSelComment)
for LoadIndex in idcur:
LoadIndexMax=LoadIndex[0]
return LoadIndexMax
def GetTableCount(TableName):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
RowCntComment="SELECT COUNT(*) FROM "+TableName
rowcur=con.cursor()
rowcur.execute(RowCntComment)
for count in rowcur:
RowCount=count[0]
return RowCount
def GetRecordCount(TableName,FilterValue):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
RowCntComment="SELECT COUNT(*) FROM "+TableName+" a WHERE a.PRODUCT =\'"+FilterValue+"\'"
rowcur=con.cursor()
rowcur.execute(RowCntComment)
for count in rowcur:
RowCount=count[0]
return RowCount
def GetMainRecordCount(TableName,StageTableName):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
RowCntComment="SELECT COUNT(*) FROM "+StageTableName+" a WHERE a.COMPLAINTID NOT IN (SELECT COMPLAINTID FROM "+TableName+")"
rowcur=con.cursor()
rowcur.execute(RowCntComment)
for count in rowcur:
RowCount=count[0]
return RowCount
def fdbembedded_connect(dsn, charset):
fbembed_path = findlib('fbembed')
if not fbembed_path:
print("You must have the fbembed shared object to run this script.")
sys.exit(255)
else:
return fdb.connect(dsn=dsn,
charset=charset,
fb_library_name=fbembed_path)
def clean(self):
cleaned_data = self.cleaned_data
conexion_db = cleaned_data.get("conexion_db")
username = cleaned_data.get("username")
password = cleaned_data.get("password")
if conexion_db == None and username != 'SYSDBA':
raise forms.ValidationError(u'Por favor selecciona una conexion')
#Si se seleciona una conexion comprueba usuario y password de firebird
else:
try:
db = fdb.connect(host='localhost', user=username ,password=str(password) , database= RUTA_PROYECTO + "data\LOGIN.FDB")
except fdb.DatabaseError:
raise forms.ValidationError(u'nombre de usuario o password invalidos')
if conexion_db:
try:
db = fdb.connect(host=conexion_db.servidor ,user=conexion_db.usuario ,password=conexion_db.password , database="%s\System\CONFIG.FDB"% conexion_db.carpeta_datos)
except fdb.DatabaseError:
raise forms.ValidationError(u'Error en la conexion selecionada')
#Crea o modifica usuario
try:
usuario = User.objects.get(username__exact=username)
except ObjectDoesNotExist:
usuario = User.objects.create_user(username = username, password=str(password))
if username == 'SYSDBA':
User.objects.filter(username = '******').update(is_superuser=True, is_staff=True)
else:
usuario.set_password(str(password))
usuario.save()
#Se crea o se modifica perfil de usuario con conexion
# user_profile = UserProfile.objects.filter(usuario = usuario)
# if conexion_db:
# if user_profile.exists():
# user_profile.update(conexion_activa = conexion_db, basedatos_activa='')
# else:
# UserProfile.objects.create(usuario= usuario, basedatos_activa='', conexion_activa= conexion_db)
# elif usuario.username == 'SYSDBA' and not user_profile.exists():
# UserProfile.objects.create(usuario= usuario, basedatos_activa='', conexion_activa=None)
return cleaned_data
def syncTM(session, x):
con = fdb.connect(dsn='tm', user='******', password='******', charset='WIN1251')
a = cur.execute('select id, operation, table_name, data, action_time from dashboard_log').fetchall()
for record in a:
print record
id, operation, table_name, data, action_time = record
record_local = taxi_master_sync(sync_id=id, sync_status=0)
session.add(record_local)
con.close()
def __init__(self):
try:
self.db_connect = fdb.connect(user=constants.DB_USER,
password=constants.DB_PASSWORD,
host=constants.DB_HOST,
database=constants.DATABASE
)
except fdb.fbcore.DatabaseError:
print "Database Error"
self.db_connect = False
def __init__(self, cfg):
self._conn = fdb.connect(host=cfg["database"]["host"],
port=cfg["database"]["port"],
database=cfg["database"]["dbname"],
user=cfg["database"]["user"],
password=cfg["database"]["password"],
charset="UTF8")
if not self._check_schema():
self._create_schema()
def Run(self):
print "Sync Jurnal Summary"
print "==================="
try:
sqlcofig = self.config['db_sql']
fbconfig = self.config['db_fb']
# conn = {
# 'sql': pymssql.connect(sqlcofig['host'], sqlcofig['user'], sqlcofig['password'], sqlcofig['dbname']),
# 'fb': fdb.connect(dsn=fbconfig['dsn'], user=fbconfig['user'], password=fbconfig['password'], role=fbconfig['role'] )
# }
# file_temp = '/home/agung/Documents/output/temp/~jurnalsummarydata.csv'
# file_final = '/home/agung/Documents/output/temp/jurnalsummarydata-2015-1.csv'
#
# fp = open(file_temp, 'w')
#
# #for i in range(1, self.params['M']):
# for i in range(1, 7):
# for region in [region for region in self.regions if region["execute"]==True]:
# self.processRegion(region, self.params['Y'], i, conn, fp)
#
# fp.close()
# os.rename(file_temp, file_final)
# self.processData(self.params['Y'], self.params['M'], conn, file_final)
# year = self.params['Y']
for y in range(2017, 2018):
year = y
for i in range(1, 2):
conn = {
'sql': pymssql.connect(sqlcofig['host'], sqlcofig['user'], sqlcofig['password'], sqlcofig['dbname']),
'fb': fdb.connect(dsn=fbconfig['dsn'], user=fbconfig['user'], password=fbconfig['password'], role=fbconfig['role'] )
}
month=i
file_temp = '/home/agung/Documents/output/temp/~jurnalsummarydata.csv'
file_final = '/home/agung/Documents/output/temp/jurnalsummarydata-%(year)i-%(month)i.csv' % {'month': month, 'year':year}
fp = open(file_temp, 'w')
for region in [region for region in self.regions if region["execute"]==True]:
self.processRegion(region, year, month, conn, fp)
fp.close()
os.rename(file_temp, file_final)
print ""
print "Inserting Data..."
self.processData(year, month, conn, file_final)
conn['sql'].close
conn['fb'].close
except fdb.fbcore.DatabaseError, e:
print str(e)
sys.exit()
def DisplayLoadStepLog():
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="SELECT LOADINDEX,LOADORDER,LOADSTEP,STEPNAME,STATUS,RECORDCOUNT,CAST(LOADSTARTDTTM AS CHAR(24)) LOADSTARTDTTM,LOADENDDTTM,"\
+"DATEDIFF(SECOND FROM LOADSTARTDTTM TO LOADENDDTTM) AS DURATION FROM "+MainLogTable
APILog=cur.prep(SQLComment)
cur.execute(APILog)
df=pd.DataFrame(cur.fetchall(),columns=['LoadIndex','LoadOrder','LoadStep','StepName','Status','RecordCount','LoadStartDTTM','LoadEndDTTM','Duration'])
con.commit()
return df
def ViewSwap(TableName,ViewName):
global DBPath,UserName,Password
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
SQLComment="recreate view " + ViewName + " as select DateReceived,Product,SubProduct,Issue,SubIssue,ConsumerComplaint,CompanyPublicResponse,Company,State,ZIPCode,SubmittedVia,DateSentCompany,CompanyResponseConsumer,TimelyResponseSts,ConsumerDisputedSts,ComplaintID from "+TableName
fbSwap=cur.prep(SQLComment)
try:
cur.execute(fbSwap)
except Exception as e:
con.commit()
raise Exception(e)
con.commit()
def __init__(self):
self.__pathDB = os.path.abspath(os.path.join(os.getcwd(), "..", "Base", "AEnter.gdb"))
self.__pathCSV = ""
self.__Postav = Postav()
self.__Sklad = Store()
self.__PartFolder = PartFolder()
self.__Author = None
self.weID = 1
self.DocType = 1
self.__db = fdb.connect(dsn="localhost:"+self.__pathDB, user='******', password='******', charset='WIN1251')
def connect(self):
dic_conn = self.conf.ler()
HostName = dic_conn['HostName']
UserName = dic_conn['UserName']
Passwd = dic_conn['Passwd']
Port = dic_conn['Port']
DatabaseName = dic_conn['DatabaseName']
try:
self.conn = fdb.connect(host=HostName, user=UserName,
password=Passwd, port=int(Port),
database=DatabaseName, charset='WIN1252')
except fdb.DatabaseError as ex:
print("Erro ao conectar ao banco de dados\nErro: %s" % ex)
def insertar_inf_usuar(filesql): con = fdb.connect(dsn=RUTA_PROYECTO+'/database/puan.fdb', user='******', password='******',sql_dialect=3,charset='UTF8') trans=con.trans() trans.begin cur=trans.cursor() print "Limpiando tabla ..." sql_prep=cur.prep(r"delete from inf_usuar") cur.execute(sql_prep) try: trans.commit() except Exception as e3: print e3 trans.rollback() raise sql_prep=cur.prep(r"insert into inf_usuar (NRO_CUENTA,RUTA_VIEJO,VEREDA_VIEJO,NUMERO_VIEJO,SUBNRO_VIEJO,NRO_SOCIO,NRO_CONEX,NOMBRE,COD_CALLE,CALLE,NRO_PTA,PISO,DTO,COD_POSTAL,NRO_MEDIDOR,MEDID_REACT,FECHA_CONEX,TIPO_CONEX,ZONA,COD_ESTADO,EXCLUIDO_IB,COD_TARIFA,UBICACION,COD_FACTURA,ORDEN_FAC,CANT_COPIAS,CAT_IVA,NRO_PARTIDA,NRO_PARTIDO,MTS_CONSTRUIDOS,NRO_SUB,LINEA,FACE,LATITUD,LONGITUD,SOCIO_GAR,COD_ACTIV,INQUILINO,FECHA_INGRESO,PERIODO,RUTA_NUEVO,VEREDA_NUEVO,NUMERO_NUEVO,SUBNRO_NUEVO,NRO_SOCIO_NUEVO,NRO_CONEX_NUEVO,TRANSF_ACC,OPERACION,SERVICIO,NUEVA_CONEX,HORA,ESTADO,OPERADOR,NRO_SOLICITUD,FECHA_SOLICITUD,INFO_DGR,TITULAR,MONOFASE,CERTIFICADO,OBSERV,APLICACION,ULT_RECIBO,RUTA,VEREDA,NUMERO,SUBNRO) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") try: with open(RUTA_PROYECTO+'/energia.exp/'+filesql, 'rb') as csvfile: spamreader = csv.reader(csvfile, delimiter=':') i=1 contador=0 for row in spamreader: if len(str(row[50]).strip()) == 3: cur.execute(sql_prep,[isnulo(row[0]),isnulo(row[1]),isnulo(row[2]),isnulo(row[3]),isnulo(row[4]),isnulo(row[5]),isnulo(row[6]),isnulo(row[7]),isnulo(row[8]),isnulo(row[9]),isnulo(row[10]),isnulo(row[11]),isnulo(row[12]),isnulo(row[13]),isnulo(row[14]),isnulo(row[15]),isnulo(row[16],True),isnulo(row[17]),isnulo(row[18]),isnulo(row[19]),isnulo(row[20]),isnulo(row[21]),isnulo(row[22]),isnulo(row[23]),isnulo(row[24]),isnulo(row[25]),isnulo(row[26]),isnulo(row[27]),isnulo(row[28]),isnulo(row[29]),isnulo(row[30]),isnulo(row[31]),isnulo(row[32]),isnulo(row[33]),isnulo(row[34]),isnulo(row[35]),isnulo(row[36]),isnulo(row[37]),isnulo(row[38],True),isnulo(row[39]),isnulo(row[40]),isnulo(row[41]),isnulo(row[42]),isnulo(row[43]),isnulo(row[44]),isnulo(row[45]),isnulo(row[46]),isnulo(row[47]),isnulo(row[48]),isnulo(row[49]),None,isnulo(row[53]),isnulo(row[54]),isnulo(row[55]),isnulo(row[56],True),isnulo(row[57]),isnulo(row[58]),isnulo(row[59]),isnulo(row[60]),isnulo(row[61]),isnulo(row[62]),isnulo(row[63]),isnulo(row[64]),isnulo(row[65]),isnulo(row[66]),isnulo(row[67])]) else: cur.execute(sql_prep,[isnulo(row[0]),isnulo(row[1]),isnulo(row[2]),isnulo(row[3]),isnulo(row[4]),isnulo(row[5]),isnulo(row[6]),isnulo(row[7]),isnulo(row[8]),isnulo(row[9]),isnulo(row[10]),isnulo(row[11]),isnulo(row[12]),isnulo(row[13]),isnulo(row[14]),isnulo(row[15]),isnulo(row[16],True),isnulo(row[17]),isnulo(row[18]),isnulo(row[19]),isnulo(row[20]),isnulo(row[21]),isnulo(row[22]),isnulo(row[23]),isnulo(row[24]),isnulo(row[25]),isnulo(row[26]),isnulo(row[27]),isnulo(row[28]),isnulo(row[29]),isnulo(row[30]),isnulo(row[31]),isnulo(row[32]),isnulo(row[33]),isnulo(row[34]),isnulo(row[35]),isnulo(row[36]),isnulo(row[37]),isnulo(row[38],True),isnulo(row[39]),isnulo(row[40]),isnulo(row[41]),isnulo(row[42]),isnulo(row[43]),isnulo(row[44]),isnulo(row[45]),isnulo(row[46]),isnulo(row[47]),isnulo(row[48]),isnulo(row[49]),None,isnulo(row[51]),isnulo(row[52]),isnulo(row[53]),isnulo(row[54],True),isnulo(row[55]),isnulo(row[56]),isnulo(row[57]),isnulo(row[58]),isnulo(row[59]),isnulo(row[60]),isnulo(row[61]),isnulo(row[62]),isnulo(row[63]),isnulo(row[64]),isnulo(row[65])]) mensaje='Importando Tabla INF_USUAR Registro: ' print_there(mensaje, i) i=i+1 contador=contador+1 if contador==300: trans.commit() contador=0 trans.commit() cur.close() trans.close() except Exception as e3: print e3 trans.rollback() cur.close() trans.close()
def insertar_deuda_sit(filesql):
con = fdb.connect(dsn=RUTA_PROYECTO+'/database/puan.fdb', user='******', password='******',sql_dialect=3,charset='UTF8')
trans=con.trans()
trans.begin
cur=trans.cursor()
print "Limpiando tabla ..."
sql_prep=cur.prep(r"delete from deuda_sit")
cur.execute(sql_prep)
try:
trans.commit()
except Exception as e3:
print e3
trans.rollback()
raise
sql_prep=cur.prep(r"insert into deuda_sit (TIPO,NRO_FACTURA,GRUPO,TABLA,FECHA,HORA,ESTADO,NRO_PLAN,IMPORTE,PORCENTAJE) values (?,?,?,?,?,?,?,?,?,?)")
try:
with open(RUTA_PROYECTO+'/energia.exp/'+filesql, 'rb') as csvfile:
spamreader = csv.reader(csvfile, delimiter=':')
i=1
contador=0
for row in spamreader:
cur.execute(sql_prep,[isnulo(row[0]),isnulo(row[1]),isnulo(row[2]),isnulo(row[3]),isnulo(row[4],True),None,isnulo(row[8]),isnulo(row[9]),isnulo(row[10]),isnulo(row[11])])
mensaje='Importando Tabla DEUDA_SIT Registro: '
print_there(mensaje, i)
i=i+1
contador=contador+1
if contador==300:
trans.commit()
contador=0
trans.commit()
cur.close()
trans.close()
except Exception as e3:
print e3
trans.rollback()
cur.close()
trans.close()
