def adapt_dtype_of_empty_db_columns(self): """ Changes the data types of empty columns in the SQL table defined in given instance of a PandasSpecialEngine. This should only happen in case of data type mismatches. This means with columns for which the sqlalchemy table model for df and the model for the SQL table have different data types. """ empty_db_columns = self.get_empty_columns() db_table = self.get_db_table_schema() # if column does not have value in db and there are values # in the frame then change the column type if needed for col in empty_db_columns: # check if the column also exists in df if col.name not in self.df.columns: continue # check same type orig_type = db_table.columns[col.name].type.compile(self.engine.dialect) dest_type = self.table.columns[col.name].type.compile(self.engine.dialect) # remove character count e.g. "VARCHAR(50)" -> "VARCHAR" orig_type = RE_CHARCOUNT_COL_TYPE.sub('', orig_type) dest_type = RE_CHARCOUNT_COL_TYPE.sub('', dest_type) # if same type or we want to insert TEXT instead of JSON continue # (JSON is not supported on some DBs so it's normal to have TEXT instead) if ((orig_type == dest_type) or ((orig_type == 'JSON') and (dest_type == 'TEXT'))): continue # grab the col/index from the df # so we can check if there are any values if col.name in self.df.index.names: df_col = self.df.index.get_level_values(col.name) else: df_col = self.df[col.name] if df_col.notna().any(): # raise error if we have to modify the dtype but we have a SQlite engine # (SQLite does not support data type alteration) if self._db_type == 'sqlite': raise ValueError('SQlite does not support column data type alteration!') with self.engine.connect() as con: ctx = MigrationContext.configure(con) op = Operations(ctx) new_col = self.table.columns[col.name] # check if postgres (in which case we have to use "using" syntax # to alter columns data types) if self._db_type == 'postgres': escaped_col = str(new_col.compile(dialect=self.engine.dialect)) compiled_type = new_col.type.compile(dialect=self.engine.dialect) alter_kwargs = {'postgresql_using':f'{escaped_col}::{compiled_type}'} else: alter_kwargs = {} op.alter_column(table_name=self.table.name, column_name=new_col.name, type_=new_col.type, schema=self.schema, **alter_kwargs) log(f"Changed type of column {new_col.name} " f"from {col.type} to {new_col.type} " f'in table {self.table.name} (schema="{self.schema}")')
def test_standalone_op(): eng, buf = capture_db() env = MigrationContext.configure(eng) op = Operations(env) op.alter_column("t", "c", nullable=True) eq_(buf, ['ALTER TABLE t ALTER COLUMN c DROP NOT NULL'])
def _alter_column_name(self, base, old_name, new_name): # NOTE: Create alembic connection and operation object! By John Doe db_conn = config.ENGINE.connect() alembic_ctx = MigrationContext.configure(db_conn) alembic_op = Operations(alembic_ctx) doc_table = get_doc_table(base.metadata.name, config.METADATA, **base.relational_fields) alembic_op.alter_column(doc_table.name, old_name, new_column_name=new_name) db_conn.close()
def change_column_nullable(table_oid, column_index, nullable, engine): table = tables.reflect_table_from_oid(table_oid, engine) column = table.columns[column_index] with engine.begin() as conn: ctx = MigrationContext.configure(conn) op = Operations(ctx) op.alter_column(table.name, column.name, nullable=nullable, schema=table.schema) return tables.reflect_table_from_oid(table_oid, engine).columns[column_index]
def _alter_column_name(self, base, old_name, new_name): # NOTE: Create alembic connection and operation object! By John Doe db_conn=config.ENGINE.connect() alembic_ctx=MigrationContext.configure(db_conn) alembic_op=Operations(alembic_ctx) doc_table=get_doc_table( base.metadata.name, config.METADATA, **base.relational_fields ) alembic_op.alter_column( doc_table.name, old_name, new_column_name=new_name ) db_conn.close()
def setup(context, drop): """ Initialize a database for openFRED data. Connect to the database specified in the `[openFRED]` section of oemof's configuration file and set the database up to hold openFRED data. This means that the configured schema is created if it doesn't already exists. The same holds for the tables necessary to store openFRED data inside the schema. """ section = context.obj["db"]["section"] schema = oemof.db.config.get(section, "schema") engine = oemof.db.engine(section) inspector = inspect(engine) metadata = MetaData(schema=schema, bind=engine, reflect=(not drop)) classes = mapped_classes(metadata) if drop == "schema": with engine.connect() as connection: connection.execute( "DROP SCHEMA IF EXISTS {} CASCADE".format(schema)) elif drop == "tables": classes["__Base__"].metadata.drop_all(engine) if schema not in inspector.get_schema_names(): engine.execute(CreateSchema(schema)) with engine.connect() as connection: connection.execute("CREATE EXTENSION IF NOT EXISTS postgis;") connection.execute("CREATE EXTENSION IF NOT EXISTS postgis_topology;") classes["__Base__"].metadata.create_all(engine) with db_session(engine) as session: timespan = classes["Timespan"] try: ts = (session.query(timespan).filter_by(start=None, stop=None).one_or_none()) except MRF: click.echo("Multiple timestamps found which have no `start` " "and/or `stop` values.\nAborting.") ts = ts or classes["Timespan"]() session.add(ts) session.flush() context = MigrationContext.configure(session.connection()) ops = Operations(context) ops.alter_column( table_name=str(classes["Series"].__table__.name), column_name="timespan_id", server_default=str(ts.id), schema=schema, ) constraint_name = "singular_null_timestamp_constraint" if not [ c for c in timespan.__table__.constraints if c.name == constraint_name ]: constraint = CheckConstraint( "(id = {}) OR ".format(ts.id) + "(start IS NOT NULL AND stop IS NOT NULL)", name=constraint_name, ) timespan.__table__.append_constraint(constraint) session.execute(AddConstraint(constraint)) return classes
def submitRunlistData(): """ Create/edit experimental run list layout. Insert/edit experimental parameters and parameter types. This request must send its parameters as a single JSON encoded string and the `content-type`_ header must be supplied as `application/json`_ .. _content-type: https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Type .. _application/json: https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types .. :quickref: Create/Edit run list; Create or edit the experimental run list layout :param: isNewRunlist : (Boolean) if runlist is new :param: clock : (Integer) creation timestamp :param: runlistType : (Integer) Run list type (unused) :param: runlistRemarks : Object containing global runlist information. Contains the following keys: | ``cols`` : List of experimental parameter objects. Experimental parameter objects contain the following keys: | ``name`` : (String) name of the parameter | ``type`` : (Integer) type of parameter | ``0`` : Regular parameter | ``3`` : Run Title identifier (optinal declaration) | ``4`` : Option parameter | ``parType`` : (Integer - optional) Type of regular parameter (only used if ``type=1``) | ``0`` : Number | ``1`` : Text | ``parUnits`` : (Integer - optional) Physical parameter units (only used if ``type=1``) | ``parOptions`` : (String - optional) JSON encoded string of parameter options (only used if ``type=1``) | ``action`` : (String) Action to be preformed on parameter | ``add`` : add parameter | ``edit`` : edit parameter | ``delete`` : delete parameter """ newRunList = False processRequest = request.get_json() if 'isNewRunlist' in processRequest: newRunList = processRequest["isNewRunlist"] else: newRunList = True try: Session = sessionmaker(bind=current_user.engineObj) session = Session() conn = current_user.engineObj.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) result = session.query( daqbrokerDatabase.runs).filter_by(clock=session.query( func.max(daqbrokerDatabase.runs.clock)).first()[0]).first() if not result: oldRemarks = {} else: oldRemarks = json.loads(result.runlistRemarks) #dbQuery=text("INSERT INTO runs VALUES(:clock,:clock,0,:linkRemarks,:runlistType,:runlistRemarks)") #startTableAlter="ALTER TABLE runlist " if newRunList: for i, col in enumerate(processRequest["runlistRemarks"]["cols"]): if ((not (int(col["type"]) == 1)) and (not (int(col["type"]) == 2)) and (not (int(col["type"]) == 3))): if int(col["type"]) == 4: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 1: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 0: newType = daqbrokerDatabase.Float newCol = daqbrokerDatabase.Column(col["name"], newType) op.add_column("runlist", newCol) processRequest["runlistRemarks"]["cols"][i][ "action"] = "addOld" else: for i, col in enumerate(processRequest["runlistRemarks"]["cols"]): extra = '' #print(col) if not int(col["type"]) == 3: if i >= len(oldRemarks["cols"]): column = col else: column = oldRemarks["cols"][i] if int(col["type"]) == 4: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 1: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 0: extra = "\"" + column["name"] + "\"::double precision" newType = daqbrokerDatabase.Float if col['action'] == 'add': newCol = daqbrokerDatabase.Column(col["name"], newType) op.add_column("runlist", newCol) processRequest["runlistRemarks"]["cols"][i][ "action"] = "addOld" elif col['action'] == 'edit': if col["name"] != column["name"]: op.alter_column("runlist", oldRemarks["cols"][i]["name"], new_column_name=col["name"], type_=newType, postgresql_using=extra) else: op.alter_column("runlist", oldRemarks["cols"][i]["name"], type_=newType, postgresql_using=extra) elif col['action'] == 'delete': op.drop_column("runlist", col["name"]) #print("done") daqbrokerDatabase.daqbroker_database.metadata.remove( daqbrokerDatabase.daqbroker_database.metadata.tables["runlist"] ) daqbrokerDatabase.daqbroker_database.metadata.reflect( current_user.engineObj, extend_existing=True) processRequest["runlistRemarks"]["cols"] = [ x for x in processRequest["runlistRemarks"]["cols"] if x['action'] != 'delete' ] newRuns = daqbrokerDatabase.runs(clock=processRequest["clock"], linkRemarks='', runlistRemarks=json.dumps( processRequest["runlistRemarks"])) session.add(newRuns) session.commit() conn.close() except Exception as e: session.rollback() traceback.print_exc() raise InvalidUsage(str(e), status_code=500) return jsonify('done')
def insertInstrument(): """ Insert a new instrument or edit an existing instrument on a DAQBroker database. Guest users are not allowed to create instruments. Created instruments are .. :quickref: Create/Edit instrument; Creates or edits a DAQBroker instrument instrument :param: Name : (String) unique instrument name :param: instid : (Integer) unique instrument identifier. Used to edit an existing instrument :param: description : (String) description of the instrument and its :param: email : (String) contact information for the instrument operator :param: Files : (Optional) JSON encoded list of instrument data source objects. Each Contains the following keys: | ``name`` : (String) name of the data source | ``metaid`` : (Integer) unique data source identifier. Only used to edit existing data sources | ``type`` : (Integer) type of instrument data source | ``node`` : (String) unique network node identifier | ``remarks`` : (String) JSON encoded object of extra data source information | ``channels`` : (Optional) JSON encoded list of data channel objects. Each contains the following keys: | ``Name`` : (String) data channel name | ``channelid`` : (Integer) unique channel identifier. -1 if the channel is new. Positive integer if the channel already exists | ``description`` : (String) data channel description | ``units`` : (String) data channel physical units | ``channeltype`` : (Integer) type of data channel | ``0`` : Number | ``1`` : Text | ``2`` : Custom | ``active`` : (Boolean) channel is shown on interface | ``fileorder`` : (Integer) Used to order channels in a data source | ``alias`` : (String) Original data channel name. Kept constant when name changes | ``remarks`` : (String) JSON encoded object with extra information | ``oldname`` : (String) Old channel name. Used to detect changes in the channel name | ``channeltypeOld`` : (Integer) Old channel type. Used to detect changes in the channel type """ processRequest = request.get_json() Session = sessionmaker(bind=current_user.engineObj) session = Session() conn = current_user.engineObj.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) try: if 'instid' in processRequest: newInst = False instid = processRequest['instid'] instrument = session.query(daqbrokerDatabase.instruments).filter_by(instid=instid).first() else: newInst = True maxInst = session.query(func.max(daqbrokerDatabase.instruments.instid)).one_or_none() # print(maxInst==None) if maxInst[0]: maxInstid = maxInst[0] else: maxInstid = 0 instid = maxInstid + 1 instrument = daqbrokerDatabase.instruments( Name=processRequest['Name'], instid=instid, active=False, description=processRequest['description'], username=current_user.username, email=processRequest['email'], insttype=0, log=None) # Now I have an object called "instrument" that I can use to add sources # and metadatas and to those metadatas I should be able to add channels. for file in processRequest['files']: if 'metaid' in file: metadata = session.query(daqbrokerDatabase.instmeta).filter_by(metaid=file["metaid"]).first() metadata.clock = time.time() * 1000 metadata.name= file['name'] metadata.type=file['type'] metadata.node=file['node'] metadata.remarks=json.dumps(file['remarks']) else: maxMeta = session.query(func.max(daqbrokerDatabase.instmeta.metaid)).first() if maxMeta[0]: maxMetaid = maxMeta[0] else: maxMetaid = 0 metaid = maxMetaid + 1 metadata = daqbrokerDatabase.instmeta( clock=time.time() * 1000, name=file['name'], metaid=metaid, type=file["type"], node=file["node"], remarks=json.dumps( file['remarks']), sentRequest=False, lastAction=0, lasterrortime=0, lasterror='', lockSync=False) instrument.sources.append(metadata) channelid = None if 'channels' in file: channelsInsert = [] for channel in file['channels']: if int(channel['channelid']) < 0: # New channel - have to insert maxChannel = session.query(func.max(daqbrokerDatabase.channels.channelid)).first() if not channelid: if maxChannel[0]: maxChannelid = maxChannel[0] else: maxChannelid = 0 channelid = maxChannelid + 1 else: channelid = channelid + 1 if 'remarks' in channel: if len(channel["remarks"].keys())>0: theRemarks = json.dumps(channel["remarks"]) else: theRemarks = json.dumps({}) else: theRemarks = json.dumps({}) theChannel = daqbrokerDatabase.channels( Name=channel["Name"], channelid=channelid, channeltype=int( channel["channeltype"]), valuetype=0, units=channel['units'], description=channel['description'], active=int( channel['active']) == 1, remarks=theRemarks, lastclock=0, lastValue=None, firstClock=0, fileorder=channel['fileorder'], alias=channel['alias']) metadata.channels.append(theChannel) channelsInsert.append({'name': channel["Name"], 'type': int(channel["channeltype"])}) if not newInst: extra = '' if int(channel['channeltype']) == 1: newType = daqbrokerDatabase.Float extra = "\"" + channel["Name"] + "\"::double precision" column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_data", column) elif int(channel['channeltype']) == 2: newType = daqbrokerDatabase.Text column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_data", column) elif int(channel['channeltype']) == 3: extra = "\"" + channel["Name"] + "\"::double precision" theType = daqbrokerDatabase.Float column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_custom", column) elif not newInst: theChannel = session.query( daqbrokerDatabase.channels).filter_by( channelid=channel['channelid']).first() theChannel.Name = channel["Name"] theChannel.channeltype = int(channel["channeltype"]) theChannel.units = channel['units'] theChannel.description = channel['description'] theChannel.active = int(channel['active']) == 1 theChannel.fileorder = channel['fileorder'] theChannel.alias = channel['alias'] if (not channel['channeltypeOld'] == channel['channeltype']) or ( not channel['oldName'] == str(channel['Name'])): if not channel['oldName'] == str(channel['Name']): newName = str(channel['Name']) oldName = channel['oldName'] else: oldName = str(channel['Name']) newName = None if not channel['channeltypeOld'] == channel['channeltype']: if channel['channeltype'] == 1 or channel['channeltype'] == 3: newType = daqbrokerDatabase.Float extra = "\"" + oldName + "\"::double precision" else: newType = daqbrokerDatabase.Text extra = None else: newType = None if not channel['channeltypeOld'] == channel['channeltype'] and channel['channeltype'] == 3: if not newName: theName = oldName else: theName = newName if not newType: theType = daqbrokerDatabase.Float else: theType = newType column = daqbrokerDatabase.Column(theName, theType) op.drop_column(processRequest['Name'] + "_data", oldName) op.add_column(processRequest['Name'] + "_custom", column) elif not channel['channeltypeOld'] == channel['channeltype'] and channel['channeltypeOld'] != 3: if not newName: theName = oldName else: theName = newName if not newType: if channel['channeltypeOld'] == 1: theType = daqbrokerDatabase.Float else: theType = daqbrokerDatabase.Text else: theType = newType column = daqbrokerDatabase.Column(theName, theType) op.drop_column(processRequest['Name'] + "_custom", oldName) op.add_column(processRequest['Name'] + "_data", column) else: if channel['channeltype'] == 1 or channel['channeltype'] == 2: if extra: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType, postgresql_using=extra) else: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType) else: if extra=='': op.alter_column( processRequest['Name'] + "_custom", oldName, new_column_name=newName, type_=newType) else: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType, postgresql_using=extra) elif newInst: raise InvalidUsage("Cannot issue edit channels on new instrument", status_code=401) if newInst: daqbrokerDatabase.createInstrumentTable(processRequest['Name'], channelsInsert, True) session.add(instrument) daqbrokerDatabase.daqbroker_database.metadata.create_all(current_user.engineObj) session.commit() conn.close() current_user.updateDB() return jsonify('done') except Exception as e: traceback.print_exc() session.rollback() # for statement in deleteStatements: # connection.execute(statement) raise InvalidUsage(str(e), status_code=500)