i have question dynamicly creating tables sqlalchemy. not know tables names when python server script starts.
how works is, have written client app collects data, using rest(server) , requests(client) push data server component. catch data @ server api point , write them(params) database, that's good.
my problem comes in when have client @ new location. server should create new table in database(server) each location client reports server.
the clients automatically create new table name based on location specified. client work/read/write 1 table @ time until stops.
client db:
database> location1> device(string(17)) location(text) data1(text) data2(text) on server dynamically create new table in same database new unknown location reported client. therefore on server have table per "location" , database filled locations.
server:
database = "mysql://user:pwwd@localhost/server" metadata = metadata() engine = create_engine(database) base = declarative_base() class records(base): __tablename__ = 'records' device = column(string(17), primary_key=true) data1 = column(text()) location = column(text()) data2 = column(text()) base.metadata.create_all(bind=engine, checkfirst=true) the server structure want create follows:
database> location1> device(string(17)) location(text) data1(text) data2(text) location2> device(string(17)) location(text) data1(text) data2(text) location3> device(string(17)) location(text) data1(text) data2(text) *question: found fair amount of posts topic, good post here in post struggle understand how create columns said table. , how make call outside class (orm) 'unknown_table1' when 'unknown_table2' dynamically created after 'unknown_table1'.
i find out if table exists with:
from sqlalchemy.engine.reflection import inspector inspector = inspector.from_engine(engine) def known_location(self, _loc): global inspector global dyntable _know_tables = inspector.get_table_names() if _loc not in _know_tables: create table , add data on own!. any assistance appreciated.
i found solution. please tell me how can improve it.
i setup standard database connection, connect server table , create if doesn't exist.
_location = "server" records = table(_location, metadata, column('device', string(17), primary_key=true), column('data1', text()), column('location', text()), column('data2', text()), extend_existing=true ) base.metadata.create_all(bind=engine, checkfirst=true) i have sequence through data posted server using rest. parse parameters variables pass def below.
def collector(_device, _data1, _data2, _loc): global session global metadata records = table(_loc, metadata, column('device', string(17), primary_key=true), column('data1', text()), column('location', text()), column('data2', text()), extend_existing=true ) check = exists().where(records.c.device==_device) if check == true: updt = update(records).where(records.c.device==_device).values(data1=_data1, location=_loc, data2=_data2) session.add(updt) session.commit() else: create_new_table(_loc) ins = records.insert().values(debice=_device, data1=_data1, location=_loc, data2=_data2) session.execute(ins) session.commit() session.close() if table not exist call function called "create_new_table()"... below.
def create_new_table(_loc): global engine global metadata global base records = table(_loc, metadata, column('device', string(17), primary_key=true), column('data1', text()), column('location', text()), column('data2', text()), extend_existing=true ) base.metadata.create_all(bind=engine, checkfirst=true) records.create(engine, checkfirst=true) please let me know if think can improve anything.
Comments
Post a Comment