def getLocation(msg):
confirm = 'n'
archiveID = None
while confirm not in ['y', 'Y', '']:
path = input(msg + "(C:\c) ")
if (path == ""):
path = "C:\c"
try:
archiveID = int(path)
#get path from DB
con = firebirdsql.connect(host='localhost',
database=DB,
user='******',
password='******')
cur = con.cursor()
cur.execute("SELECT ADDRESS FROM ARCHIVELOCATION WHERE ID = " +
path)
r = cur.fetchone()
path = r[0]
except ValueError:
#must be a file location
print("")
finally:
if os.path.isdir(path):
confirm = input("You have entered " + path +
". Is this correct? (Y/n) ")
else:
print("That directory does not exist! Please try again ")
confirm = 'n'
return path, archiveID
def _handle_event(self):
conn = firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
conduit = conn.event_conduit(['event_a', 'event_b', 'event_d'])
result = conduit.wait()
# 1 record is inserted
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._insert()
result = conduit.wait()
# 1 more record is inserted
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._delete()
result = conduit.wait()
# 2 records are deleted
self.assertEqual(result, {'event_b': 0, 'event_a': 0, 'event_d': 2})
conduit.close()
conn.close()
def __init__(self, dbname=None):
"""Constructor.
:param dbname: Pfad zur Firebird-Datenbankdatei.
# :type tabObject: String
"""
# Verbindung zur Datenbank herstellen
if os.path.exists(dbname):
try:
self.confb = firebirdsql.connect(database=dbname,
user='******',
password='******',
charset="latin1")
self.curfb = self.confb.cursor()
except:
iface.messageBar().pushMessage(
"Fehler",
u'Fehler beim Anbinden der ITWH-Datenbank {:s}!\nAbbruch!'.
format(dbname),
level=QgsMessageBar.CRITICAL)
self.confb = None
else:
iface.messageBar().pushMessage(
"Fehler",
u'ITWH-Datenbank {:s} wurde nicht gefunden!\nAbbruch!'.format(
dbname),
level=QgsMessageBar.CRITICAL)
self.confb = None
def connect(
self,
path='../EKP.FDB',
host='localhost',
port=3050,
username='******',
passwd='masterkey',
):
"""
connect to firebird and create a cursor
"""
log.debug(path)
while True:
try:
conn = firebirdsql.connect(
host=host,
database=path,
port=3050,
user='******',
password='******',
charset='gbk',
)
if conn:
break
except Exception as e:
log.error(e)
log.info(
"please check if database and trying to connect again!")
return conn
def __init__(self, user, password):
if not database_connection.is_connection():
self.__connect = firebirdsql.connect(
#TODO вынести в конфиг
dsn = 'class.mmcs.sfedu.ru:/fbdata/38mi/newtest_podgr3.fdb',
user = user,
password = password)
def _handle_event(self):
conn = firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
conduit = conn.event_conduit(['event_a', 'event_b', 'event_d'])
result = conduit.wait()
# 1 record is inserted
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._insert()
result = conduit.wait()
# 1 more record is inserted
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._delete()
result = conduit.wait()
# 2 records are deleted
self.assertEqual(result, {'event_b': 0, 'event_a': 0, 'event_d': 2})
conduit.close()
conn.close()
def get_list_phones_from_dc():
config = ConfigObj('call.cfg')
global server
global db_stat
global user
global userpass
server = config['ServerDB']['ServerName']
db_ib = config['ServerDB']['DB_IB']
user = config['ServerDB']['user']
userpass = config['ServerDB']['pass']
list_lot = config['WorkConfig']['Lot']
s_Lot = ""
i = 1
len_lot = len(list_lot)
for single_lot in list_lot:
if i !=len_lot:
s_Lot = s_Lot + "'" + str(single_lot) + "',"
i = i + 1
continue
else:
s_Lot = s_Lot + "'" + str(single_lot) + "'"
break
con_ib = fdb.connect(host=server, database=db_ib, user=user, password=userpass)
cur_ib = con_ib.cursor()
sql = "Select phone_id from PHONES where LOT_NUMB in ({}) order by phone_id".format(s_Lot)
with cur_ib.execute(sql):
return cur_ib.fetchall()
def __init__(self, dbname: str):
"""Constructor.
:param dbname: Pfad zur Firebird-Datenbankdatei.
"""
# Verbindung zur Datenbank herstellen
if os.path.exists(dbname):
try:
self.confb = firebirdsql.connect(
database=dbname,
user="******",
password="******",
charset="latin1",
)
self.curfb = self.confb.cursor()
except:
logger.debug(
"Fehler beim Anbinden der ITWH-Datenbank {:s}!\nAbbruch!".
format(dbname))
self.confb = None
else:
logger.debug(
"Fehler: ITWH-Datenbank {:s} wurde nicht gefunden!\nAbbruch!".
format(dbname))
self.confb = None
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 dbAcces(sql):
con = connect(database='C:\\Users\\guilh\\Desktop\\DKSOFT.FDB',
user='******',
password='******',
host='localhost')
cursor = con.cursor()
cursor.execute(sql)
return cursor.fetchall()
def __init__(self):
self.conn = firebirdsql.connect(
host='localhost',
database='/var/lib/firebird/2.5/data/pazha.fdb',
port=3050,
user='******',
password='******'
)
self.cur = self.conn.cursor()
def _produce(self):
conn = firebirdsql.connect(host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('insert into test_table values (1)')
conn.commit()
conn.close()
def connect_bd(self, path, server):
self.con = firebirdsql.connect(
host=server,
database=path,
port=3050,
user='******',
password='******',
charset='utf8'
)
self.cur = self.con.cursor()
def BD_is_open(self, path, server):
self.con = firebirdsql.connect(
host=server,
database=path,
port=3050,
user='******',
password='******',
charset='utf8'
)
self.set_status(status="Ок", color='#0b5506')
def _connect(self, src):
try:
self.cnxn = firebirdsql.connect(database=src, host='localhost',
user='******', password='******', charset='WIN1251')
except firebirdsql.OperationalError as error:
raise _InvalidDatabaseError(error.__str__())
except socket.error as error:
raise _DatabaseServerError(error.__str__())
return self.cnxn.cursor()
def _produce(self):
conn = firebirdsql.connect(
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('insert into test_table values (1)')
conn.commit()
conn.close()
def _delete(self):
conn = firebirdsql.connect(auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('delete from test_table')
conn.commit()
conn.close()
def _insert(self):
conn = firebirdsql.connect(auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('insert into test_table values (1)')
conn.commit()
conn.close()
def db_open(self, server, path_fbd):
self.fdb_conn = firebirdsql.connect(host=server,
database=path_fbd,
port=3050,
user='******',
password='******',
charset='utf8')
self.fdb_cur = self.fdb_conn.cursor()
# определение страниц кода и кадров
self.pages = PagesStruct(self.fdb_cur)
self.evklid = EvklassStruct(self.fdb_cur)
def _insert(self):
conn = firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('insert into test_table values (1)')
conn.commit()
conn.close()
def _delete(self):
conn = firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
cur = conn.cursor()
cur.execute('delete from test_table')
conn.commit()
conn.close()
def dataBaseConnect(self):
if self.dbConnect is None:
try:
self.dbConnect = firebirdsql.connect(
host=self.pBase['host'],
database=self.pBase['path'],
user=self.pBase['login'],
password=self.pBase['password'])
return True
except:
return False
else:
return True
def current_count():
con_ib1 = fdb.connect(host=server, database=db_ib, user=user, password=userpass)
cur_ib1 = con_ib1.cursor()
sql = "Select phone_id from PHONES where LOT_NUMB in (%s) order by phone_id" % s_Lot
with cur_ib1.execute(sql):
row = cur_ib1.fetchone()
count_current_otzvon = 0
while row:
var = checktaksofonotzvon(row[0])
count_current_otzvon = count_current_otzvon + var
row = cur_ib1.fetchone()
con_ib1.close()
return count_current_otzvon
def _connect(self, src):
try:
self.cnxn = firebirdsql.connect(database=src,
host='localhost',
user='******',
password='******',
charset='WIN1251')
except firebirdsql.OperationalError as error:
raise _InvalidDatabaseError(error.__str__())
except socket.error as error:
raise _DatabaseServerError(error.__str__())
return self.cnxn.cursor()
def connect(self):
"""
connect to firebird and create a cursor
"""
conn = firebirdsql.connect(
host='localhost',
database='D:\Work\Projects\GD_firebird\EKP.FDB',
port=3050,
user='******',
password='******',
charset='gbk',
)
cur = conn.cursor()
return cur
def test_backup(self):
"""
backup & restore
"""
BACKUP_FILE = self.database + '.fbk'
RESTORE_DATABASE = tempfile.mktemp()
# backup
svc = firebirdsql.services.connect(
auth_plugin_list=self.auth_plugin_list,
wire_crypt=self.wire_crypt,
host=self.host,
user=self.user,
password=self.password)
svc.backup_database(self.database,
BACKUP_FILE,
callback=test_callback)
svc.close()
self.assertEqual(True, os.access(BACKUP_FILE, os.F_OK))
# restore
svc = firebirdsql.services.connect(
auth_plugin_list=self.auth_plugin_list,
wire_crypt=self.wire_crypt,
host=self.host,
user=self.user,
password=self.password)
svc.restore_database(BACKUP_FILE,
RESTORE_DATABASE,
replace=True,
pageSize=4096,
callback=test_callback)
svc.close()
self.assertEqual(True, os.access(RESTORE_DATABASE, os.F_OK))
# drop database
conn = firebirdsql.connect(
auth_plugin_list=self.auth_plugin_list,
wire_crypt=self.wire_crypt,
host=self.host,
database=RESTORE_DATABASE,
port=self.port,
user=self.user,
password=self.password)
conn.drop_database()
conn.close()
self.assertEqual(False, os.access(RESTORE_DATABASE, os.F_OK))
def countotzvon(phone_id, date):
global server
global db_stat
global user
global userpass
con_stat = fdb.connect(host=server, database=db_stat, user=user, password=userpass)
cur_stat = con_stat.cursor()
d = datetime.strptime(date, "%Y-%m-%d").strftime("%d.%m.%Y")
sql = "select count(*) from STATCONNECTJOURNAL where phone_id = %s and (DATETIME > '%s 00:00' and DATETIME < '%s 23:59')" % (phone_id, d, d)
with cur_stat.execute(sql):
r = cur_stat.fetchone()
if r != None:
logging.debug('Код отзвона - %s' % r[0])
return r[0]
else:
return 0
def codotzvona(phone_id, date):
global server
global db_stat
global user
global userpass
con_stat = fdb.connect(host=server, database=db_stat, user=user, password=userpass)
cur_stat = con_stat.cursor()
d = datetime.strptime(date, "%Y-%m-%d").strftime("%d.%m.%Y")
sql = "select * from allfails where phone_id = %s and (DATE_TIME > '%s 00:00' and DATE_TIME < '%s 23:59') order by DATE_TIME desc" % (phone_id, d, d)
with cur_stat.execute(sql):
r = cur_stat.fetchone()
if r != None:
logging.debug('Код отзвона - %s' % r[2])
return r[2]
else:
return 1009
def open_database(self):
self.mainwin.con = None
try:
con = firebirdsql.connect(host=self.line_server.text(),
database=self.line_text.text(),
port=3050,
user='******',
password='******',
charset='utf8')
self.my_signal.emit(self.line_text.text(), self.line_server.text())
except Exception as e:
QMessageBox().warning(
self, "Ошибка",
str(e.__str__()) + '\n Ошибка подлючения к базе',
QMessageBox.Ok, QMessageBox.Ok)
def extractDB(database,ipAddr):
con = firebirdsql.connect(
host=ipAddr, database=database,
user='******', password='******'
)
cur = con.cursor()
cur.execute("select rdb$relation_name from rdb$relations where rdb$view_blr is null and (rdb$system_flag is null or rdb$system_flag = 0);")
results = cur.fetchall()
print "\n- Found the below tables"
for x in results:
print x[0]
for x in results:
print "\n- Extracing the contents from the table: "+x[0]
cur.execute("select * from "+x[0]+";")
results = cur.fetchall()
print results
def checktaksofonotzvon(phone_id, date):
global server
global db_stat
global user
global userpass
con_stat = fdb.connect(host=server, database=db_stat, user=user, password=userpass)
with con_stat:
cur_stat = con_stat.cursor()
d = datetime.strptime(date, "%Y-%m-%d").strftime("%d.%m.%Y")
sql = "select * from allfails where phone_id = %s and (DATE_TIME > '%s 00:00' and DATE_TIME < '%s 23:59') and ((fail_code in (0,32,128)) and (fail_code not in (1,2,4)))" % (phone_id, d, d)
cur_stat.execute(sql)
cur_stat.fetchone()
logging.debug('phone_id - %4s, count - %s' % (phone_id, cur_stat.rowcount))
if cur_stat.rowcount > 0:
return True
else:
return False
def __init__(self,path):
self.path = path
print "path",path
self.con = firebirdsql.connect(dsn="localhost:"+path, user='******', password='******')
self.cur = self.con.cursor()
self.item = ""
self.table = ""
self.where = ""
self.limit = ""
self.retlist = []
self.DB = DBA_Manu.DBA(path)
self.TM_INTreeOUT = [ ]
self.TM_IUTreeOUT = [ ]
self.TM_IMTreeOUT = [ ]
self.TM_ICTreeOUT = [ ]
self.MT_INTreeOUT = [ ]
self.MT_IUTreeOUT = [ ]
self.MT_IMTreeOUT = [ ]
self.MT_ICTreeOUT = [ ]
self.Items = [ ]
self.cur.execute("SELECT ITEMCODE FROM ITEM")
self.NoItems=0
for row in self.cur:
self.Items.append( str(row[0]) )
self.NoItems +=1
self.lastselect = ""
self.lastpreselect = ""
self.Jobs = [ ]
self.CJobs = [ ]
self.SO = [ ]
self.cur.execute("select JOBNO,JOBSTATS from JOBS")
self.NoJobs=0
self.NoCJobs=0
self.NoSales=0
for row in self.cur:
if str(row[1]) == "CLOSED":
self.CJobs.append( str(row[0]) )
self.NoCJobs +=1
else:
if row[0][0] == "S":
self.SO.append( str(row[0]) )
self.NoSales +=1
else:
self.Jobs.append( str(row[0]) )
self.NoJobs +=1
def test_backup(self):
"""
backup & restore
"""
BACKUP_FILE = self.database + '.fbk'
RESTORE_DATABASE = tempfile.mktemp()
# backup
svc = firebirdsql.services.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
user=self.user,
password=self.password)
svc.backup_database(self.database, BACKUP_FILE, callback=test_callback)
svc.close()
self.assertEqual(True, os.access(BACKUP_FILE, os.F_OK))
# restore
svc = firebirdsql.services.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
user=self.user,
password=self.password)
svc.restore_database(BACKUP_FILE,
RESTORE_DATABASE,
replace=True,
pageSize=4096,
callback=test_callback)
svc.close()
self.assertEqual(True, os.access(RESTORE_DATABASE, os.F_OK))
# drop database
conn = firebirdsql.connect(auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
database=RESTORE_DATABASE,
port=self.port,
user=self.user,
password=self.password)
conn.drop_database()
conn.close()
self.assertEqual(False, os.access(RESTORE_DATABASE, os.F_OK))
def get_name_order(path_str):
#print(str(str_))
path_db = 'BASE.fdb'
con = firebirdsql.connect(host='localhost',
database=path_db,
port=3050,
user='******',
password='******')
cur = con.cursor()
cur.execute("""select
a.OBJECT_LENGTH
from
object a
where a.PATH=""" + str(path_str))
str_ = ''
for obj_len in cur.fetchall():
str_ = obj_len
print(str_)
def main():
logging.info(u'Запустили скрипт')
config = ConfigObj('call.cfg')
global server
global db_stat
global user
global userpass
server = config['ServerDB']['ServerName']
db_stat = config['ServerDB']['DB_Stat']
user = config['ServerDB']['user']
userpass = config['ServerDB']['pass']
global con
con = sqlite3.connect('dc.db3')
con_stat = fdb.connect(host=server,
database=db_stat,
user=user,
password=userpass)
cur_stat = con_stat.cursor()
with con:
cur = con.cursor()
cur.execute(
"select * from result_day_dc where date > '2018-05-13' order by date, phone_id"
)
cur_d = ''
while True:
row = cur.fetchone()
if row == None:
break
if cur_d != row[2]:
cur_d = row[2]
logging.info(u'Обрабатываем {} дату'.format(cur_d))
cod_error = codotzvona(cur_stat, row[1], row[2])
if (cod_error == 32 or cod_error == 0 or cod_error == 128):
update_to_db(row[1], row[2], True, cod_error)
print(u"update phone {}, date {}, res {}, cod_error {}".format(
row[1], row[2], True, cod_error))
else:
update_to_db(row[1], row[2], False, cod_error)
print(u"skip update phone {}, date {}, res {}, cod_error {}".
format(row[1], row[2], False, cod_error))
con_stat.close()
logging.info(u'Закончили выполнять скрипт')
def _handle_event(self):
conn = firebirdsql.connect(host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
conduit = conn.event_conduit(['event_a', 'event_b', 'event_d'])
result = conduit.wait()
assert result == {'event_b': 1, 'event_a': 2, 'event_d': 0}
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._produce()
while result == {'event_b': 0, 'event_a': 0, 'event_d': 0}:
result = conduit.wait()
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
conduit.close()
conn.close()
def _handle_event(self):
conn = firebirdsql.connect(
host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
conduit = conn.event_conduit(['event_a', 'event_b', 'event_d'])
result = conduit.wait()
assert result == {'event_b': 1, 'event_a': 2, 'event_d': 0}
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
self._produce()
while result == {'event_b': 0, 'event_a': 0, 'event_d': 0}:
result = conduit.wait()
self.assertEqual(result, {'event_b': 1, 'event_a': 2, 'event_d': 0})
conduit.close()
conn.close()
def get_list_phones_from_dc():
config = ConfigObj('call.cfg')
global server
global db_stat
global user
global userpass
server = config['ServerDB']['ServerName']
db_ib = config['ServerDB']['DB_IB']
user = config['ServerDB']['user']
userpass = config['ServerDB']['pass']
list_lot = config['WorkConfig']['Lot']
s_Lot = ""
i = 1
len_lot = len(list_lot)
for single_lot in list_lot:
if i !=len_lot:
s_Lot = s_Lot + "'" + str(single_lot) + "',"
i = i + 1
continue
else:
s_Lot = s_Lot + "'" + str(single_lot) + "'"
break
con_ib = fdb.connect(host=server, database=db_ib, user=user, password=userpass)
cur_ib = con_ib.cursor()
sql = """Select PH.phone_id,
PH.address,
PH.WORK_SAM,
PH.VERSIONPROG,
PH.LOT_NUMB,
PH.COMPETITION_NUMB,
PH.ABC_NUMB,
PH.LINE_NUMB,
PH.SLOT_ID,
(SELECT TEXT_DISTRICT from district d where PH.DISTRICT_ID = d.DISTRICT_ID) as SERVICEMAN,
(SELECT NAME_SERVICEMAN from SERVICEMAN s where PH.SERVICEMAN_ID = s.SERVICEMAN_ID) as DISTRICT
from PHONES PH
where PH.LOT_NUMB in ({})
order by PH.phone_id""".format(s_Lot)
with cur_ib.execute(sql):
return cur_ib.fetchall()
def ins_2_firebird(sql_list,username='******',password='******',dbname='10.104.131.33:d:\opt\skyqx.fdb' ):
import firebirdsql
con = firebirdsql.connect(dsn=dbname, user=username, \
password=password,charset='gb2312')
con.autocommit=False
length1 = len(sql_list)
print('sql length = %d'%(length1))
j=0
str2=''
str3=''
starttime = time.clock()
for line in sql_list:
#print(line)
con.cursor().execute(line)
#------------------------------------
if(0==j%10 or j==length1-1 or j==length1):
endtime = time.clock()
res = endtime-starttime
precent1 = float(j)*100/float(length1)
if(0.0==precent1/100.0):
FullTime=0
EST=999
else:
FullTime= res/(precent1/100.0)
EST=FullTime-res
str2='%5.2f%%(%d) cost :%4.1f sec remain:%4.1f sec(%5.1f)'%(precent1,length1,res,EST,FullTime)
print(str2,end='')
print('\r',end='')
if(j==length1-1 or j==length1):
print(str2)
#for j in range(len(str2)):
# print("\b",end='')
#------------------------------------
j=j+1
#print(line,type(line))
con.commit()
con.close()
def main():
logging.info(u'Запустили скрипт' )
config = ConfigObj('call.cfg')
global server
global db_stat
global user
global userpass
server = config['ServerDB']['ServerName']
db_stat = config['ServerDB']['DB_Stat']
user = config['ServerDB']['user']
userpass = config['ServerDB']['pass']
global con
con = sqlite3.connect('dc.db3')
con_stat = fdb.connect(host=server, database=db_stat, user=user, password=userpass)
cur_stat = con_stat.cursor()
with con:
cur = con.cursor()
cur2 = con.cursor()
#cur.execute("SELECT * from result_day_dc where (date > '2018-01-18' and date < '2018-01-20') and statconnect = 0")
#cur.execute("SELECT * from result_day_dc where statconnect = 0 and phone_id=1536")
cur.execute("select * from result_day_dc where statconnect=0 and res='True' and date > '2018-08-20'order by date, phone_id")
cur_d=''
while True:
row = cur.fetchone()
if row == None:
break
if cur_d != row[2]:
cur_d = row[2]
logging.info(u'Обрабатываем {} дату'.format(cur_d))
kol = countotzvon(cur_stat, row[1], row[2])
if kol != 0:
update_to_db(row[1], row[2], kol)
print(u"update phone {}, date {}, count {}".format(row[1], row[2], kol))
else:
print(u"skip update phone {}, date {}, count {}".format(row[1], row[2], kol))
con_stat.close()
logging.info(u'Закончили выполнять скрипт' )
def connectFirebird(ipAddr,wordList):
defaultDB="C:\\PROGRAM FILES\\FIREBIRD\\FIREBIRD_2_5\\EXAMPLES\\EMPBUILD\\EMPLOYEE.FDB"
#Try getting list of connected databases without attempting any database name guess.
con = firebirdsql.services.connect(host=ipAddr, user='******', password='******')
results = con.getAttachedDatabaseNames()
dbList=[]
if len(results)>0:
for x in results:
if x!=defaultDB:
#Remove the default database
dbList.append(x)
print "\n- Found the below connected databases"
for x in dbList:
print x
for x in dbList:
print "\n- Extracting contents from Firebird database: "+x
extractDB(x,ipAddr)
else:
#Brutefoorce firebird databasess
print "\n- Bruteforcing Firebird database names"
dictList=[]
with open(wordList) as f:
dictList = f.read().splitlines()
currentWord=""
for word in dictList:
currentWord=word
try:
con = firebirdsql.connect(
host=ipAddr, database=word,
user='******', password='******'
)
print "Correct database name: "+word
break
except firebirdsql.OperationalError:
print "Incorrect database name: "+word
continue
extractDB(currentWord,ipAddr)
def test_basic(self):
conn = self.connection
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(cur.fetchone(), None)
cur.close()
cur = conn.cursor()
cur.execute("select a as alias_name from foo")
assert cur.description[0][0] == "ALIAS_NAME"
cur.close()
# 3 records insert
conn.cursor().execute("insert into foo(a, b, c,h) values (1, 'a', 'b','This is a memo')")
conn.cursor().execute(
"""insert into foo(a, b, c, e, g, i, j)
values (2, 'A', 'B', '1999-01-25', '00:00:01', 0.1, 0.1)"""
)
conn.cursor().execute(
"""insert into foo(a, b, c, e, g, i, j)
values (3, 'X', 'Y', '2001-07-05', '00:01:02', 0.2, 0.2)"""
)
# 1 record insert and rollback to savepoint
cur = conn.cursor()
conn.savepoint("abcdefghijklmnopqrstuvwxyz")
cur.execute(
"""insert into foo(a, b, c, e, g, i, j)
values (4, 'x', 'y', '1967-05-08', '00:01:03', 0.3, 0.3)"""
)
conn.rollback(savepoint="abcdefghijklmnopqrstuvwxyz")
conn.cursor().execute("update foo set c='Hajime' where a=1")
conn.cursor().execute("update foo set c=? where a=2", ("Nakagami",))
conn.commit()
# select rowcount
cur = conn.cursor()
cur.execute("select * from foo where c=?", ("Nakagami",))
self.assertEqual(len(cur.fetchall()), 1)
self.assertEqual(cur.rowcount, 1)
cur.close()
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(len(cur.fetchall()), 3)
self.assertEqual(cur.rowcount, 3)
cur.close()
# update rowcount
cur = conn.cursor()
cur.execute("update foo set c=? where a=2", ("Nakagami",))
self.assertEqual(cur.rowcount, 1)
conn.commit()
cur = conn.cursor()
cur.execute("select * from foo")
assert not cur.fetchone() is None
assert not cur.fetchone() is None
assert not cur.fetchone() is None
assert cur.fetchone() is None
cur.close()
cur = conn.cursor()
cur.execute("select * from foo order by a")
self.assertEqual(len(cur.fetchmany(2)), 2)
cur.close()
cur = conn.cursor()
cur.execute(
"""insert into foo(a, b, c, e, g, i, j)
values (5, 'c', 'c', '2014-02-19', '00:01:05', 0.5, 0.5)"""
)
self.assertEqual(cur.fetchone(), None)
cur.close()
cur = conn.cursor()
cur.execute(
"""insert into foo(a, b, c, e, g, i, j)
values (6, 'd', 'd', '2014-02-19', '00:01:06', 0.6, 0.6)"""
)
self.assertEqual(cur.fetchmany(), [])
cur.close()
cur = conn.cursor()
cur.execute("select * from foo")
conn.commit()
try:
cur.fetchall()
except firebirdsql.OperationalError as e:
pass
cur = conn.cursor()
try:
conn.cursor().execute("insert into foo(a, b, c) values (1, 'a', 'b')")
except firebirdsql.IntegrityError:
pass
try:
conn.cursor().execute("bad sql")
except firebirdsql.OperationalError:
e = sys.exc_info()[1]
self.assertEqual(e.sql_code, -104)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"], [d[0] for d in cur.description])
self.assertEqual(["a", "A", "X", "c", "d"], [r[1] for r in cur.fetchall()])
cur.execute("select * from foo")
self.assertEqual(["a", "A", "X", "c", "d"], [r["B"] for r in cur.fetchallmap()])
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(
{
"A": 1,
"B": "a",
"C": "Hajime",
"D": Decimal("-0.123"),
"E": datetime.date(1967, 8, 11),
"F": datetime.datetime(1967, 8, 11, 23, 45, 1),
"G": datetime.time(23, 45, 1),
"H": "This is a memo",
"I": 0.0,
"J": 0.0,
},
dict(cur.fetchonemap()),
)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(["a", "A", "X", "c", "d"], [r["B"] for r in cur.itermap()])
cur = conn.cursor()
cur.execute("select * from bar_empty")
self.assertEqual([], [r for r in cur.fetchonemap().items()])
cur = conn.cursor()
cur.execute("select * from foo")
rs = [r for r in cur]
self.assertEqual(rs[0][:3], (1, "a", "Hajime"))
self.assertEqual(rs[1][:3], (2, "A", "Nakagami"))
self.assertEqual(rs[2][:3], (3, "X", "Y"))
cur = conn.cursor()
cur.execute("select rdb$field_name from rdb$relation_fields where rdb$field_name='ABCDEFGHIJKLMNOPQRSTUVWXYZ'")
v = cur.fetchone()[0]
self.assertEqual(v.strip(), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
conn.close()
with firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
database=self.database,
port=self.port,
user=self.user,
password=self.password,
) as conn:
with conn.cursor() as cur:
cur.execute("update foo set c='Toshihide' where a=1")
conn = firebirdsql.connect(
auth_plugin_name=self.auth_plugin_name,
wire_crypt=self.wire_crypt,
host=self.host,
database=self.database,
port=self.port,
user=self.user,
password=self.password,
)
conn.begin()
requests = [
isc_info_ods_version,
isc_info_ods_minor_version,
isc_info_base_level,
isc_info_db_id,
isc_info_implementation,
isc_info_firebird_version,
isc_info_user_names,
isc_info_read_idx_count,
isc_info_creation_date,
]
self.assertEqual(9, len(conn.db_info(requests)))
requests = [
isc_info_tra_id,
isc_info_tra_oldest_interesting,
isc_info_tra_oldest_snapshot,
isc_info_tra_oldest_active,
isc_info_tra_isolation,
isc_info_tra_access,
isc_info_tra_lock_timeout,
]
self.assertEqual(7, len(conn.trans_info(requests)))
conn.set_isolation_level(firebirdsql.ISOLATION_LEVEL_SERIALIZABLE)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"], [d[0] for d in cur.description])
self.assertEqual(["a", "A", "X", "c", "d"], [r[1] for r in cur.fetchall()])
cur.close()
#Add the type based on the type of the element if elem['name'][0:2] != "__" : # Make sure it's actually a column name and not an extended parameter sql_command += "\"" + elem['name']+"\" " if elem['type'] == "xsd:boolean": sql_command += "varchar(5)" elif elem['type'] == "xsd:string": sql_command += "varchar(255)" else: sql_command += "int" sql_command += "," commandLength = len(sql_command) - 1 sql_command = sql_command[0:commandLength] sql_command += ")" # OPEN DATABASE AND CREATE TABLE con = firebirdsql.connect(host='localhost', database='/var/lib/firebird/2.1/data/sndump.fdb', user='******', password='******') cur = con.cursor() cur.execute(sql_command) con.commit() # DUMP DATA INTO TABLE # Open url with username password password_mgr = urllib2.HTTPPasswordMgrWithDefaultRealm() jsonurl = "https://" + instance + "/" + table_name + ".do?JSON" password_mgr.add_password(None, jsonurl, "", "") handler = urllib2.HTTPBasicAuthHandler(password_mgr) try: opener = urllib2.build_opener(handler) opener.open(jsonurl) urllib2.install_opener(opener) getRecs = urllib2.urlopen(jsonurl)
print('cursor iteration')
cur = conn.cursor()
cur.execute("select * from foo")
for (a, b, c, d, e, f, g, h, i, j) in cur:
print(a, b, c)
print('long field name from system table')
cur = conn.cursor()
cur.execute("select rdb$field_name from rdb$relation_fields where rdb$field_name='ABCDEFGHIJKLMNOPQRSTUVWXYZ'")
v = cur.fetchone()[0]
assert v.strip() == 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
conn.close()
with firebirdsql.connect(host=TEST_HOST, database=TEST_DATABASE,
port=TEST_PORT, user=TEST_USER, password=TEST_PASS) as conn:
with conn.cursor() as cur:
cur.execute("update foo set c='Toshihide' where a=1")
conn = firebirdsql.connect(host=TEST_HOST, database=TEST_DATABASE,
port=TEST_PORT, user=TEST_USER, password=TEST_PASS)
conn.begin()
print('db_info:')
requests = [isc_info_ods_version,
isc_info_ods_minor_version,
isc_info_base_level,
isc_info_db_id,
isc_info_implementation,
isc_info_firebird_version,
isc_info_user_names,
def from_fb_sql2insert(PgTableName,sql_str = 'select * from sky_station'):
import firebirdsql,string
#and rownum<5
#con = cx_Oracle.connect("cipas","cipas","orcl")
con = firebirdsql.connect(dsn=r'10.104.131.33:D:\opt\SKYQX.FDB', user='******',password='******',charset='gb2312')
# Create a Cursor object that operates in the context of Connection con:
#cur = con.cursor()
#print(con.dsn)
#print(con.version)
cursor = con.cursor()
#sql_str = 'select * from tab'u
cursor.execute(sql_str)
#con.commit()
sql_result=cursor.fetchall()
####################################################
fields=[]
for row in cursor.description:
fields.append(row[0])
#print(row)
varstr = string.join(fields,',');
##print(varstr)
#####################################################
##print(type(sql_result))
##print(sql_result[0])
#line1 = sql_result[0]
#print(line1,type(line1))
#####################################################
cols = len(cursor.description)
row_type_list=[]
for i in range(cols):
row=cursor.description[i]
print(row)
row_type_list.append(row[1])
#####################################################
AllSqlList=[]
starttime = time.clock()
length1 = len(sql_result)
j=0
str2=''
str3=''
for line1 in sql_result:
#------------------------------------
if(0==j%1000 or j==length1-2):
#print(j)
endtime = time.clock()
res = endtime-starttime
precent1 = float(j)*100/float(length1)
if(0.0==precent1/100.0):
FullTime=0
EST=999
else:
FullTime= res/(precent1/100.0)
EST=FullTime-res
str2='%5.2f%%(%d) cost :%4.1f sec remain:%4.1f sec(%5.1f)'%(precent1,length1,res,EST,FullTime)
print(str2,end='')
str3 = "\b"*len(str2)
print(str3,end='')
#for j in range(len(str2)):
# print("\b",end='')
#print(line,type(line))
#------------------------------------
j=j+1
Field=[]
for i in range(cols):
#row=cursor.description[i]
#print(row_type_list[i])
#continue
if(None == line1[i]):
Field.append('NULL')
continue
if(row_type_list[i]==496):
Field.append('%d'%line1[i])
continue
if(row_type_list[i]==448):
str1 = line1[i]
str1 = str1.decode('gb2312')
#Field.append("'"+line1[i]+"'")
Field.append("'"+str1+"'")
continue
#if(row_type_list[i]==cx_Oracle.DATETIME):
# Field.append("'"+datetime.datetime.strftime(line1[i],'%Y-%m-%d')+"'")
# continue
#print(line1[i],datetime.datetime.strftime(line1[i],'%Y-%m-%d'))
#print(line1[i],row,type(line1[i]))
#else:
# print(line1[i],row[1])
#sys.exit(0)
#sys.exit(0)
varstr2 = string.join(Field,',');
#print(len(line1),len(Field))
#print(varstr2)
sqlstr='insert into %s(%s) values(%s)'%(PgTableName,varstr,varstr2)
#print(sqlstr)
AllSqlList.append(sqlstr)
#break
#for line in sql_result:
# print(line,type(line))
print(str2)
cursor.close()
con.close()
return AllSqlList
def open(self):
self.conn = firebirdsql.connect(**self.conn_args)
self.conn.set_autocommit(True)
self.opened=DateTime()
def test_basic(self):
conn = self.connection
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(cur.fetchone(), None)
cur.close()
cur = conn.cursor()
self.assertEqual(None, cur.callproc("foo_proc"))
cur.close()
cur = conn.cursor()
try:
rs = cur.execute("select out1, out2 from foo_proc")
if rs is None:
# foo_proc not selectable with Firebird 1.5
pass
else:
pass
except firebirdsql.OperationalError:
# foo_proc not selectable with Firebird 2.x
pass
finally:
cur.close()
cur = conn.cursor()
cur.callproc("bar_proc", (1, "ABC"))
rs = cur.fetchallmap()
self.assertEqual(len(rs), 1)
self.assertEqual(rs[0]['OUT1'], 1)
self.assertEqual(rs[0]['OUT2'], 'ABC')
cur.close()
cur = conn.cursor()
cur.execute("select a as alias_name from foo")
assert cur.description[0][0] == 'ALIAS_NAME'
cur.close()
# 3 records insert
conn.cursor().execute("insert into foo(a, b, c,h) values (1, 'a', 'b','This is a memo')")
conn.cursor().execute("""insert into foo(a, b, c, e, g, i, j)
values (2, 'A', 'B', '1999-01-25', '00:00:01', 0.1, 0.1)""")
conn.cursor().execute("""insert into foo(a, b, c, e, g, i, j)
values (3, 'X', 'Y', '2001-07-05', '00:01:02', 0.2, 0.2)""")
cur = conn.cursor()
cur.execute("select out1, out2 from baz_proc(?)", (1, ))
rs = cur.fetchall()
self.assertEqual(len(rs), 1)
self.assertEqual([1, 'a'], rs[0])
cur.close()
# 1 record insert and rollback to savepoint
cur = conn.cursor()
conn.savepoint('abcdefghijklmnopqrstuvwxyz')
cur.execute("""insert into foo(a, b, c, e, g, i, j)
values (4, 'x', 'y', '1967-05-08', '00:01:03', 0.3, 0.3)""")
conn.rollback(savepoint='abcdefghijklmnopqrstuvwxyz')
conn.cursor().execute("update foo set c='Hajime' where a=1")
conn.cursor().execute("update foo set c=? where a=2", ('Nakagami', ))
conn.commit()
cur = conn.cursor()
cur.execute("select * from foo where c=?", ('Nakagami', ))
len(cur.fetchall()) == 1
cur.close()
cur = conn.cursor()
cur.execute("select * from foo")
assert not cur.fetchone() is None
assert not cur.fetchone() is None
assert not cur.fetchone() is None
assert cur.fetchone() is None
cur.close()
cur = conn.cursor()
cur.execute("select * from foo")
conn.commit()
try:
list(cur.fetchall())
except firebirdsql.OperationalError:
e = sys.exc_info()[1]
self.assertTrue(
e.sql_code == -504 # FB2.1 cursor is not open
or 335544332 in e.gds_codes) # FB2.5 invalid transaction handle
cur = conn.cursor()
try:
conn.cursor().execute("insert into foo(a, b, c) values (1, 'a', 'b')")
except firebirdsql.IntegrityError:
pass
try:
conn.cursor().execute("bad sql")
except firebirdsql.OperationalError:
e = sys.exc_info()[1]
self.assertEqual(e.sql_code, -104)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(['A','B','C','D','E','F','G','H','I','J'],
[d[0] for d in cur.description])
self.assertEqual(['a','A','X'], [r[1] for r in cur.fetchall()])
cur.execute("select * from foo")
self.assertEqual(['a','A','X'], [r['B'] for r in cur.fetchallmap()])
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual({
'A': 1,
'B': 'a',
'C': 'Hajime',
'D': Decimal('-0.123'),
'E': datetime.date(1967, 8, 11),
'F': datetime.datetime(1967, 8, 11, 23, 45, 1),
'G': datetime.time(23, 45, 1),
'H': b('This is a memo'),
'I': 0.0,
'J': 0.0},
dict(cur.fetchonemap())
)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(['a','A','X'], [r['B'] for r in cur.itermap()])
cur = conn.cursor()
cur.execute("select * from bar_empty")
self.assertEqual([], [r for r in cur.fetchonemap().items()])
cur = conn.cursor()
cur.execute("select * from foo")
rs = [r for r in cur]
self.assertEqual(rs[0][:3], [1, 'a', 'Hajime'])
self.assertEqual(rs[1][:3], [2, 'A', 'Nakagami'])
self.assertEqual(rs[2][:3], [3, 'X', 'Y'])
cur = conn.cursor()
cur.execute("select rdb$field_name from rdb$relation_fields where rdb$field_name='ABCDEFGHIJKLMNOPQRSTUVWXYZ'")
v = cur.fetchone()[0]
self.assertEqual(v.strip(), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
conn.close()
with firebirdsql.connect(host=self.host,
database=self.database,
port=self.port,
user=self.user,
password=self.password) as conn:
with conn.cursor() as cur:
cur.execute("update foo set c='Toshihide' where a=1")
conn = firebirdsql.connect(host=self.host,
database=self.database,
port=self.port,
user=self.user,
password=self.password)
conn.begin()
requests = [isc_info_ods_version,
isc_info_ods_minor_version,
isc_info_base_level,
isc_info_db_id,
isc_info_implementation,
isc_info_firebird_version,
isc_info_user_names,
isc_info_read_idx_count,
isc_info_creation_date,
]
self.assertEqual(9, len(conn.db_info(requests)))
requests = [isc_info_tra_id,
isc_info_tra_oldest_interesting,
isc_info_tra_oldest_snapshot,
isc_info_tra_oldest_active,
isc_info_tra_isolation,
isc_info_tra_access,
isc_info_tra_lock_timeout,
]
self.assertEqual(7, len(conn.trans_info(requests)))
conn.set_isolation_level(firebirdsql.ISOLATION_LEVEL_SERIALIZABLE)
cur = conn.cursor()
cur.execute("select * from foo")
self.assertEqual(['A','B','C','D','E','F','G','H','I','J'],
[d[0] for d in cur.description])
self.assertEqual(['a','A','X'], [r[1] for r in cur.fetchall()])
import firebirdsql
connection = firebirdsql.connect(
host='Hostname/IP',
database='C:/Path/To/DATABASE.FBD',
user='******',
password='******',
charset='ISO8859_1' #specify a character set for the connection
)
cur = connection.cursor()
cur.execute("select * from supercooltable where stuff='whatever';")
print cur.fetchall()
