def test(database, user='******', password='******', host='127.0.0.1', charset='UTF8', port=3050, pagesize=16384): dbmod.create_database( "create database '%(database)s' page_size %(pagesize)s user '%(user)s' " "password '%(password)s' DEFAULT CHARACTER SET %(charset)s" % { 'user': user, 'password': password, 'host': host, 'database': database, 'charset': charset, 'port': port, 'pagesize': pagesize }) create_table = ''' Create Table table1 ( ID Integer, sort Integer NOT Null ); ''' create_trigger = '''CREATE TRIGGER BIU_Trigger FOR table1 ACTIVE BEFORE INSERT OR UPDATE POSITION 0 as begin if (new.sort IS NULL) then begin new.sort = 1; end end ''' conn = dbmod.connect(host=host, database=database, user=user, password=password) cur = conn.cursor() cur.execute(create_table) cur.execute(create_trigger) conn.commit() # fails with fdb, passes with kinterbasdb cur.execute('insert into table1 (ID, sort) values(1, ?)', (None, ))
def _create_database(self, test_database_name, verbosity): self._check_active_connection(verbosity) params = self._get_creation_params(database=test_database_name) connection = Database.create_database(**params) connection.execute_immediate("CREATE EXCEPTION teste '';") connection.commit() connection.close()
def main(): con = fdb.create_database(dsn='U:/DiabloDataBase/diablodb.fdb', user='******') cur = con.cursor() for key in Tables: cur.execute("CREATE TABLE {}".format(Tables[key])) con.commit()
def _createDb(self): scriptFile = Path(self._dbScriptPath) script = '' with scriptFile.open() as f: script = f.read() script = script.replace(_DB_NAME_PLACEHOLDER, self._dbPath).split('@') for operation in script: if 'CREATE DATABASE' in operation.upper(): fdb.create_database(sql=operation) else: if not self._connected(): self._connect() self._connection.cursor().execute(operation) if self._connected(): if self._connection.main_transaction.active: self._connection.commit() self._disconnect()
def _create_database(self, test_database_name, verbosity): self._check_active_connection(verbosity) params = self._get_connection_params(database=test_database_name) connection = Database.create_database(""" CREATE DATABASE '%(database)s' USER '%(user)s' PASSWORD '%(password)s' DEFAULT CHARACTER SET %(charset)s;""" % params) connection.execute_immediate("CREATE EXCEPTION teste '';") connection.commit() connection.close()
def test(database, user='******', password='******', host='127.0.0.1', charset='UTF8', port=3050, pagesize=16384): dbmod.create_database( "create database '%(database)s' page_size %(pagesize)s user '%(user)s' " "password '%(password)s' DEFAULT CHARACTER SET %(charset)s" % { 'user': user, 'password': password, 'host': host, 'database': database, 'charset': charset, 'port': port, 'pagesize': pagesize }) create_table = ''' Create Table table1 ( ID Integer, d1 Date ); ''' conn = dbmod.connect(host=host, database=database, user=user, password=password) cur = conn.cursor() cur.execute(create_table) conn.commit() # fails with fdb, passes with kinterbasdb cur.execute('insert into table1 (ID, d1) values(1, ?)', ('2010-01-01', ))
def db_create(): if request.method == 'GET': _reg = { 'db_alias': '', 'db_server': '', 'db_path': '', 'db_user': '', 'db_pass': '', 'db_role': '', 'dialect': '', 'charset': '' } return render(tpl='db_register', reg=_reg, ftyp='create') else: prms = request.POST if prms['db_path'] in ['BASE_PATH', '', '.', './']: prms['db_path'] = appconf.basepath sql = """ create database '{db_server}:{db_path}/{db_name}' user '{db_user}' password '{db_pass}' page_size {db_page_size} DEFAULT CHARACTER SET {charset}""".format(**prms) appconf.con[prms.db_alias] = fdb.create_database( sql=sql, connection_class=fdb.ConnectionWithSchema, ) register_ddl(prms.db_alias) appconf.db_config[prms.db_alias] = { 'db_server': prms.db_server, 'db_path': prms.db_path + '/' + prms.db_name, 'db_user': prms.db_user, 'db_pass': prms.db_pass, 'db_role': prms.db_role, 'dialect': 3, #todo! prms.dialect, 'charset': prms.charset } with open('%s/dbconfig.ini' % appconf.basepath, 'w+', encoding='utf-8') as f: appconf.db_config.write(f) redirect('/db/list')
def check_database(): try: connect = fdb.connect(host=HOST, port=int(PORT), database=os.path.join(PATH, DATABASE), user=USER, password=PASSWORD) print('Connected in %s.' % (DATABASE)) except: connect = fdb.create_database(host=HOST, port=int(PORT), database=os.path.join(PATH, DATABASE), user=USER, password=PASSWORD) print('Create database %s.' % (DATABASE)) return connect
def __init__(self, fnam, create=False): """Return a jupiter object with file connection to *fnam* it assumes that any string that goes into the connection is utf8 formatted""" self.fnam = fnam if os.path.exists(fnam): self.con = fdb.connect(dsn=fnam, user='******', password='******', charset='utf8') else: if create: if os.path.exists(fnam): raise ('File: {} already exists!'.format(fnam)) print('Creating database: {}'.format(fnam)) self.con = fdb.create_database( "create database '{}' user 'sysdba' password 'masterkey'". format(fnam)) else: raise ('File: {} doent exist!'.format(fnam)) self.cur = self.con.cursor()
def export(self, **options): ''' Exports the data into a Firebird Embedded file-like stream. Arguments: options (dict): The exporting options Returns: io.BytesIO: A Firebird Embedded file-like stream Raises: ExportError: When data fails to export ''' sql_options = dict(options, dialect='firebird') sql_data = SqlExporter(self._data).export(**sql_options) fdb_data = io.BytesIO() fdb_file = tempfile.mktemp() fdb_con = fdb.create_database( "CREATE DATABASE '{}' USER '{}' PASSWORD '{}'" \ .format(fdb_file, 'sysdba', 'masterkey'), sql_dialect=3 ) fdb_cursor = fdb_con.cursor() in_trans = False for stmt in sql_data.read().rstrip(';').split(';'): if stmt.startswith('INSERT') and not in_trans: fdb_con.begin() in_trans = True else: fdb_con.commit() in_trans = False fdb_cursor.execute(stmt) fdb_data.write(File(fdb_file).readBytes()) fdb_data.seek(0) return fdb_data
def encode(self, data: dict, **options) -> BinaryFileStream: """ Encodes the data into a Firebird Embedded file-like stream. Arguments: data: The data to encode **options: The encoding options Returns: A Firebird Embedded file-like stream Raises: geodatabr.encoders.EncodeError: If data fails to encode """ try: sql_data = super().encode(data, **dict(self.options, **options)) fdb_file = tempfile.mktemp() fdb_con = fdb.create_database( "CREATE DATABASE '{}' USER 'sysdba' PASSWORD 'masterkey'". format(fdb_file), sql_dialect=3) fdb_cursor = fdb_con.cursor() in_trans = False for stmt in sql_data.read().rstrip(';').split(';'): if stmt.startswith('INSERT') and not in_trans: fdb_con.begin() in_trans = True else: fdb_con.commit() in_trans = False fdb_cursor.execute(stmt) return BinaryFileStream(File(fdb_file).readBytes()) except Exception: raise EncodeError
import fdb import Initialize InitialParameters=Initialize.getParam() DBLocation=InitialParameters[1] DBName=InitialParameters[6] UserName=InitialParameters[7] Password=InitialParameters[8] DBPath=DBLocation+DBName CreateComment="create database '"+str(DBPath)+"' user '"+str(UserName)+"' password '"+str(Password)+"'" print('DatabasePath :',DBPath) con = fdb.create_database(CreateComment) print("Database (",DBName,") created Sucessfully .") con = fdb.connect(database=DBPath, user=UserName, password=Password) cur = con.cursor() cur.execute("recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)") con.commit() print("Table ConsumerComplaint created Sucessfully .") cur.execute("create unique index unique_ComplaintID on consumercomplaint(ComplaintID)") con.commit() print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .") cur.execute("recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)") con.commit() print("Table consumercomplaint_staging created Sucessfully .") cur.execute("recreate view ConsumerComplaintView as select DateReceived,Product,SubProduct,Issue,SubIssue,ConsumerComplaint,CompanyPublicResponse,Company,State,ZIPCode,SubmittedVia,DateSentCompany,CompanyResponseConsumer,TimelyResponseSts,ConsumerDisputedSts,ComplaintID from consumercomplaint") con.commit() print("View ConsumerComplaintView created Sucessfully .") cur.execute("recreate table consumercomplaint_copy (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)") con.commit()
def prepare_db(): try: conn = fdb.connect(dsn=database, user='******', password='******', fb_library_name=fbClient) conn.drop_database() except Exception as e: print(e) conn = fdb.create_database("create database '" + database + "' user 'sysdba' password 'masterkey'", fb_library_name=fbClient) cursor = conn.cursor() metadata = ("create table t (i int, v int)", "create table log (i int, tn int, sn int, o int, n int)", "insert into t values (1,0)", "insert into t values (2,0)", "insert into t values (3,0)", "insert into t values (4,0)", "insert into t values (5,0)", ''' create trigger bu_t for t before update as declare cn integer; declare tn integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; in autonomous transaction do insert into log values(old.i, :tn, :cn, old.v, new.v); end ''', ''' create trigger bd_t for t before delete as declare cn integer; declare tn integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; in autonomous transaction do insert into log values(old.i, :tn, :cn, old.v, null); end ''', ''' create procedure p_upd () as declare cn integer; declare tn integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; in autonomous transaction do insert into log values(null, -:tn, :cn, null, null); update t set v=1 where v=-1 or i in (2, 3); end ''', ''' create procedure p_s returns (v int) as declare cn integer; declare tn integer; declare i integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; for select i, v from t where v=-1 or i in (2, 3) into :i, :v do begin in autonomous transaction do insert into log values(:i, -:tn, :cn, :v, null); suspend; end end ''', ''' create procedure p_slock returns (v int) as declare cn integer; declare tn integer; declare i integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; for select i, v from t where v=-1 or i in (2, 3) with lock into :i, :v do begin in autonomous transaction do insert into log values(:i, -:tn, :cn, :v, null); suspend; end end ''', ''' create procedure p_nos as declare cn integer; declare tn integer; declare i integer; declare v integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; for select i, v from t where v=-1 or i in (2, 3) with lock into :i, :v do begin in autonomous transaction do insert into log values(:i, -:tn, :cn, :v, null); end end ''', ''' create procedure p_cur as begin for select * from t where v=-1 or i in (2, 3) as cursor cur do begin update t set v=1 where current of cur; end end ''', 'create view vt as select * from t', 'create view trig_v as select distinct * from t where i>0', ''' create trigger bu_trig_v for trig_v before update as declare cn integer; declare tn integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; in autonomous transaction do insert into log values(old.i, -:tn, :cn, old.v, new.v); update t set v=new.v where i=old.i; end ''', ''' create trigger bd_trig_v for trig_v before delete as declare cn integer; declare tn integer; begin cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER'); tn = CURRENT_TRANSACTION; in autonomous transaction do insert into log values(old.i, -:tn, :cn, old.v, null); delete from t where i=old.i; end ''') for q in metadata: cursor.execute(q) conn.commit()
import fdb import Initialize InitialParameters = Initialize.getParam() DBLocation = InitialParameters[1] DBName = InitialParameters[6] UserName = InitialParameters[7] Password = InitialParameters[8] DBPath = DBLocation + DBName CreateComment = "create database '" + str(DBPath) + "' user '" + str( UserName) + "' password '" + str(Password) + "'" print('DatabasePath :', DBPath) con = fdb.create_database(CreateComment) print("Database (", DBName, ") created Sucessfully .") con = fdb.connect(database=DBPath, user=UserName, password=Password) cur = con.cursor() cur.execute( "recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)" ) con.commit() print("Table ConsumerComplaint created Sucessfully .") cur.execute( "create unique index unique_ComplaintID on consumercomplaint(ComplaintID)") con.commit() print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .") cur.execute( "recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)" ) con.commit()
full_path = os.getcwd() + os.sep + fb_path sql_select_all = "select * from CLIENTS" # FIELD LENGHT makes error -maybe because there is no entries in db #sql_select_struct = "SELECT RDB$FIELD_NAME, RDB$DESCRIPTION, RDB$DEFAULT_VALUE, RDB$NULL_FLAG, RDB$FIELD_LENGTH FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME='CLIENTS';" sql_select_struct = "SELECT RDB$FIELD_NAME, RDB$DESCRIPTION, RDB$DEFAULT_VALUE, RDB$NULL_FLAG FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME='CLIENTS';" sql_create_db = "create database '%s' user 'sysdba' password 'masterkey'" % fb_path sql_create_table_1 = "create table CLIENTS (ID_client INTEGER NOT NULL, COMPANY VARCHAR(20), NAME VARCHAR(20))" sql_create_table_2 = "create table ITEMS (ID_item INTEGER NOT NULL, NAME VARCHAR(50), PRIZE INTEGER NOT NULL, CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP)" # full_path is not seen because script is run as dos user - only connection to db is done as firebird user #if os.path.exists(full_path): con = None cur = None try: con = fdb.create_database(sql_create_db) #con = fdb.connect(dsn=fb_path, user='******', password='******') cur = con.cursor() cur.execute(sql_create_table_1) con.commit() # commit is a part of table creation, without it, table will not be created #cur.execute(sql_create_table_2) #con.commit() # commit is a part of table creation, without it, table will not be created data = cur.execute(sql_select_struct) print data.fetchall() except fdb.DatabaseError as e: print "[0] %s" % str(e.args[0]) print "[1] %s" % str(e.args[1]) print "[2] %s" % str(e.args[2])