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
Exemple #3
0
    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)
Exemple #4
0
    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
Exemple #6
0
def conectafdb(database):
    fdb.connect(
        dsn=database,
        user='******',
        password='******'
    )
    return print("oi")
Exemple #7
0
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)
Exemple #8
0
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)
Exemple #9
0
 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
Exemple #10
0
 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)
Exemple #11
0
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()
Exemple #12
0
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
Exemple #13
0
def conecta():
    con = fdb.connect(
        host='localhost',
        database='/Users/Administrator/Desktop/VisionQuarta1259/SDFSDF.FDB',
        user='******',
        password='******')
    return con.cursor()
Exemple #14
0
    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()
Exemple #15
0
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
Exemple #16
0
    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
Exemple #17
0
 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()
Exemple #18
0
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')}
Exemple #20
0
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)
Exemple #21
0
    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()
Exemple #23
0
 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)
Exemple #24
0
    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)
Exemple #25
0
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
Exemple #26
0
    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
Exemple #27
0
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='******'
    )
Exemple #29
0
 def __init__(self, name):
     self._conn = fdb.connect(
                 dsn = name,
                 user ='******', password='******', 
                 charset='UTF8' 
                 )
     self._cursor = self._conn.cursor()
Exemple #30
0
    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)
Exemple #31
0
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
Exemple #32
0
    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
Exemple #33
0
    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"
Exemple #35
0
    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()
Exemple #36
0
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"
Exemple #38
0
 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()
Exemple #43
0
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)
Exemple #44
0
 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
Exemple #49
0
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)
Exemple #50
0
    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
Exemple #51
0
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()
Exemple #52
0
 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
Exemple #53
0
    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()
Exemple #54
0
	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()
Exemple #57
0
    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)
Exemple #59
0
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()
Exemple #60
0
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()