Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you Michael. On Sunday, June 7, 2015 at 11:06:32 PM UTC+3, Michael Bayer wrote: > > which part of it. you can just try running it to start. when you want > to use a certain schema, you'd say: > > session = Session() > with session_schema(session, 'myschema'): > # do things with session > > > that's pretty much it. "session_schema" is basically what > "session_shardid" is in the recipe. > > > > > > On 6/7/15 6:33 AM, eli rashlin wrote: > > Hi Michael, > > can you help me with the recipe I'm confuce on how to use it (I'm sorry > but I'm probably still a newbie) > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com . > To post to this group, send email to sqlal...@googlegroups.com > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
Hi Michael, can you help me with the recipe I'm confuce on how to use it (I'm sorry but I'm probably still a newbie) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you so much Mike - I'll try it right away :) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you Michal. I will try to install the patch, It might be a bit problematic as my sqlalchemy version is quite old (0.6.8) and the patch seems to be built for version 0.9 and up. Worst case scenario I can write down the queries by myself and concat the schema name -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you. I tried the method described here https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName, my tables are defined as modules but I'm unable to understand how he does it those are my classes GlobBase.py: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.compiler import compiles from sqlalchemy.schema import CreateTable Base = declarative_base() Messages.py from datetime import datetime from sqlalchemy import * from GlobBase import Base from sqlalchemy.orm import relationship class Messages(Base): __table_args__ = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8' } __tablename__ = 'Messages' id = Column(Integer, autoincrement=True, primary_key=True) message_name = Column(String(50)) protocol_name = Column(String(50)) def get_table_orm_def(self): return self.__table__ def __init__(self, message_name=None, protocol_name=None): self.message_name = message_name self.protocol_name = protocol_name def __repr__(self): return "" % (self.message_name, self.protocol_name) tst.py def map_class_to_some_table(cls, table, entity_name, **kw): newcls = type(entity_name, (cls, ), {}) mapper(newcls, table, **kw) return newcls class Foo(object): pass connection = create_engine(connection_line, pool_recycle = pool_time, echo = False) engine = sessionmaker(bind = connection, expire_on_commit=False)() row = engine.query(Messages).first() but i don't understand how to make it work... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you. I tried the method described here https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName, my tables are defined as modules but I'm unable to understand how he does it those are my classes GlobBase.py: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.compiler import compiles from sqlalchemy.schema import CreateTable Base = declarative_base() from datetime import datetime from sqlalchemy import * from GlobBase import Base from sqlalchemy.orm import relationship class Messages(Base): __table_args__ = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8' } __tablename__ = 'Messages' id = Column(Integer, autoincrement=True, primary_key=True) message_name = Column(String(50)) protocol_name = Column(String(50)) def get_table_orm_def(self): return self.__table__ def __init__(self, message_name=None, protocol_name=None): self.message_name = message_name self.protocol_name = protocol_name def __repr__(self): return "" % (self.message_name, self.protocol_name) tst.py def map_class_to_some_table(cls, table, entity_name, **kw): newcls = type(entity_name, (cls, ), {}) mapper(newcls, table, **kw) return newcls class Foo(object): pass connection = create_engine(connection_line, pool_recycle = pool_time, echo = False) engine = sessionmaker(bind = connection, expire_on_commit=False)() row = engine.query(Messages).first() but i don't understand how to make it work... On Tuesday, June 2, 2015 at 12:52:22 PM UTC+3, eli rashlin wrote: > > Hi, > > I have a very strange behavior, I have a program that uses bulk insertions > to the DB. > for some reason the process which iterate on one table and create bulk and > insert into another table fails with the error of 'No database selected'. > > > this is how I bind the session: > > > #--- > # use_db: > #this method will return a session reference > # using this DB > #--- > > > def use_db(self, new_db_name): > self.connection.execute("USE %s" % (new_db_name)) > session_ref = sessionmaker(bind = self.connection, > expire_on_commit=False) > self.session_cover_tst = session_ref() > return self.session_cover_tst > > > > engine = connection_engine.use_db(db_name) > count_sig = > engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\ > > filter(signals_table.Signals.message_id == msg_row.id).\ > > filter(signals_table.Signals.signal_id == sig_id).\ > > group_by(signals_table.Signals.signal_id).\ > > one()[0] > > > now the table Signals is huge (500M records), but the table is indexed and > everything is working great for a few rounds and then I'm getting the > error 'No database selected' and it fails... > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: getting 'No database selected' randomly from session
yes but the schema is different for each run, I dont want it to be hard coded into the table definition. On Tuesday, June 2, 2015 at 12:52:22 PM UTC+3, eli rashlin wrote: > > Hi, > > I have a very strange behavior, I have a program that uses bulk insertions > to the DB. > for some reason the process which iterate on one table and create bulk and > insert into another table fails with the error of 'No database selected'. > > > this is how I bind the session: > > > #--- > # use_db: > #this method will return a session reference > # using this DB > #--- > > > def use_db(self, new_db_name): > self.connection.execute("USE %s" % (new_db_name)) > session_ref = sessionmaker(bind = self.connection, > expire_on_commit=False) > self.session_cover_tst = session_ref() > return self.session_cover_tst > > > > engine = connection_engine.use_db(db_name) > count_sig = > engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\ > > filter(signals_table.Signals.message_id == msg_row.id).\ > > filter(signals_table.Signals.signal_id == sig_id).\ > > group_by(signals_table.Signals.signal_id).\ > > one()[0] > > > now the table Signals is huge (500M records), but the table is indexed and > everything is working great for a few rounds and then I'm getting the > error 'No database selected' and it fails... > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: getting 'No database selected' randomly from session
Thank you Michael for your answer. How can I use the schema in a query object, is there a way to do it? On Tuesday, June 2, 2015 at 6:26:43 PM UTC+3, Michael Bayer wrote: > > > > On 6/2/15 5:54 AM, eli rashlin wrote: > > BTW is there a way in sqlalchemy to append the name of the database to the > table name > like "SELECT * from dbname.table_name" > > this is the "schema" argument documented at > http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=schema#sqlalchemy.schema.Table.params.schema > . > > > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com . > To post to this group, send email to sqlal...@googlegroups.com > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: getting 'No database selected' randomly from session
BTW is there a way in sqlalchemy to append the name of the database to the table name like "SELECT * from dbname.table_name" -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] getting 'No database selected' randomly from session
Hi, I have a very strange behavior, I have a program that uses bulk insertions to the DB. for some reason the process which iterate on one table and create bulk and insert into another table fails with the error of 'No database selected'. this is how I bind the session: #--- # use_db: #this method will return a session reference # using this DB #--- def use_db(self, new_db_name): self.connection.execute("USE %s" % (new_db_name)) session_ref = sessionmaker(bind = self.connection, expire_on_commit=False) self.session_cover_tst = session_ref() return self.session_cover_tst engine = connection_engine.use_db(db_name) count_sig = engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\ filter(signals_table.Signals.message_id == msg_row.id).\ filter(signals_table.Signals.signal_id == sig_id).\ group_by(signals_table.Signals.signal_id).\ one()[0] now the table Signals is huge (500M records), but the table is indexed and everything is working great for a few rounds and then I'm getting the error 'No database selected' and it fails... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
Thank you so much Simon, your last suggestion solved it - You helped me so much - Its working :) On Tue, Feb 24, 2015 at 4:24 PM, Simon King wrote: > What happens if you replace > > join(clips_table.Clips) > > with > > join(signals_table.Signals.clips) > > ie. telling SQLAlchemy to use the predefined relationship between > Signals and Clips as the join condition. > > On Tue, Feb 24, 2015 at 12:35 PM, eli rashlin > wrote: > > I'm Sorry Simon - wrong query... > > > > This is the query that gives me the error > > > > query_obj = engine.query(signals_table.Signals.sig_value, > > > > signals_table.Signals.exist_in_frames, > > > > signals_table.Signals.local_frames, > > > clips_table.Clips.clip_name).\ > > join(clips_table.Clips).\ > > > > filter(signals_table.Signals.message_id == msg_id).\ > > > filter(signals_table.Signals.signal_id > > == sig_id).\ > > > > filter(func.format(signals_table.Signals.sig_value, 2) == > > func.format(sig_val, 2)).\ > > > > order_by(signals_table.Signals.sig_value) > > > > > > On Tuesday, February 24, 2015 at 1:09:12 PM UTC+2, Simon King wrote: > >> > >> I'm not sure I understand - the line you've shown doesn't include any > >> joins between Signals and Clips, so I don't know why that error would > >> occur. > >> > >> In general, if you are seeing that error message, then you need to > >> explicitly tell SQLAlchemy the join condition between the 2 tables. > >> This is normally done using the "primaryjoin" parameter when defining > >> the relationships. > >> > >> If you are getting the error at query time and you really *are* > >> performing a join, you can specify the join condition at that time as > >> well: > >> > >> > >> > http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins > >> > >> Hope that helps, > >> > >> Simon > >> > >> On Tue, Feb 24, 2015 at 7:56 AM, eli rashlin > wrote: > >> > Thanks Simon for your replay. > >> > when I'm removing the FK definition from the Column - The tables are > >> > being > >> > built as they should - the ptoblem is when i try to perform a join I'm > >> > getting the following error: > >> > > >> > Can't find any foreign key relationships between 'Signals' and > 'Clips'. > >> > > >> > and this is the code: > >> > > >> > > >> > return engine.query(func.count(signals_table.Signals.sig_value)).\ > >> > filter(signals_table.Signals.message_id == msg_id).\ > >> > filter(signals_table.Signals.signal_id == sig_id).\ > >> > filter(func.format(signals_table.Signals.sig_value, 2) == > >> > func.format(sig_val, 2)).\ first()[0] > >> > > >> > > >> > and this is the definition of the class Clips: > >> > from datetime import datetime from sqlalchemy import * from > >> > sqlalchemy.orm > >> > import relationship from BaseCoverRuns import Base class Clips(Base): > >> > __table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' } > >> > __tablename__ = 'Clips' id = Column(Integer, autoincrement=True, > >> > primary_key=True) clip_name = Column(String(255)) def > >> > get_table_orm_def(self): return self.__table__ def __init__(self, > >> > clip_name=None): self.clip_name = clip_name def __repr__(self): return > >> > "" % (self.clip_name) > >> > > >> > -- > >> > You received this message because you are subscribed to the Google > >> > Groups > >> > "sqlalchemy" group. > >> > To unsubscribe from this group and stop receiving emails from it, send > >> > an > >> > email to sqlalchemy+...@googlegroups.com. > >> > To post to this group, send email to sqlal...@googlegroups.com. > >> > Visit this group at http://groups.google.com/group/sqlalchemy. > >> > For more options, visit https://groups.google.com/d/optout. > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > >
Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
I'm Sorry Simon - wrong query... This is the query that gives me the error query_obj = engine.query(signals_table.Signals.sig_value, signals_table.Signals.exist_in_frames, signals_table.Signals.local_frames, clips_table.Clips.clip_name).\ join(clips_table.Clips).\ filter(signals_table.Signals.message_id == msg_id).\ filter(signals_table.Signals.signal_id == sig_id).\ filter(func.format(signals_table.Signals.sig_value, 2) == func.format(sig_val, 2)).\ order_by(signals_table.Signals.sig_value) On Tuesday, February 24, 2015 at 1:09:12 PM UTC+2, Simon King wrote: > > I'm not sure I understand - the line you've shown doesn't include any > joins between Signals and Clips, so I don't know why that error would > occur. > > In general, if you are seeing that error message, then you need to > explicitly tell SQLAlchemy the join condition between the 2 tables. > This is normally done using the "primaryjoin" parameter when defining > the relationships. > > If you are getting the error at query time and you really *are* > performing a join, you can specify the join condition at that time as > well: > > http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins > > Hope that helps, > > Simon > > On Tue, Feb 24, 2015 at 7:56 AM, eli rashlin > wrote: > > Thanks Simon for your replay. > > when I'm removing the FK definition from the Column - The tables are > being > > built as they should - the ptoblem is when i try to perform a join I'm > > getting the following error: > > > > Can't find any foreign key relationships between 'Signals' and 'Clips'. > > > > and this is the code: > > > > > > return engine.query(func.count(signals_table.Signals.sig_value)).\ > > filter(signals_table.Signals.message_id == msg_id).\ > > filter(signals_table.Signals.signal_id == sig_id).\ > > filter(func.format(signals_table.Signals.sig_value, 2) == > > func.format(sig_val, 2)).\ first()[0] > > > > > > and this is the definition of the class Clips: > > from datetime import datetime from sqlalchemy import * from > sqlalchemy.orm > > import relationship from BaseCoverRuns import Base class Clips(Base): > > __table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' } > > __tablename__ = 'Clips' id = Column(Integer, autoincrement=True, > > primary_key=True) clip_name = Column(String(255)) def > > get_table_orm_def(self): return self.__table__ def __init__(self, > > clip_name=None): self.clip_name = clip_name def __repr__(self): return > > "" % (self.clip_name) > > > > -- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com . > > To post to this group, send email to sqlal...@googlegroups.com > . > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
Thanks Simon for your replay. when I'm removing the FK definition from the Column - The tables are being built as they should - the ptoblem is when i try to perform a join I'm getting the following error: Can't find any foreign key relationships between 'Signals' and 'Clips'. and this is the code: return engine.query(func.count(signals_table.Signals.sig_value)).\ filter( signals_table.Signals.message_id == msg_id).\ filter(signals_table.Signals. signal_id == sig_id).\ filter(func.format(signals_table.Signals.sig_value, 2 ) == func.format(sig_val, 2)).\ first()[0] and this is the definition of the class Clips: from datetime import datetime from sqlalchemy import * from sqlalchemy.orm import relationship from BaseCoverRuns import Base class Clips(Base): __table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' } __tablename__ = 'Clips' id = Column(Integer, autoincrement=True, primary_key =True) clip_name = Column(String(255)) def get_table_orm_def(self): return self.__table__ def __init__(self, clip_name=None): self.clip_name = clip_name def __repr__(self): return "" % (self.clip_name) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
I have a table which i have changed the Engine from Myisam to InnoDB, This is the only table that has been changed, there are other tables that are in relation with this table of 1:N class Signals(Base, sql_functions): __tablename__ = 'Signals' def __init__(self, message_id=None, clip_id=None, signal_id=None): self.message_id = message_id self.clip_id= clip_id self.signal_id = signal_id __table_args__ = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8' } __mapper_args__= {'always_refresh': True} id = Column(Integer(10), primary_key=True) message_id = Column(Integer(10), ForeignKey('Messages.id')) clip_id = Column(Integer(10), ForeignKey('Clips.id')) signal_id = Column(Integer(10), ForeignKey('Signal_names.id'), primary_key=True) sig_name = relationship("SignalNames", backref=backref('Signals', order_by=id), primaryjoin = 'Signals.signal_id == SignalNames.id', foreign_keys = 'SignalNames.id') clips = relationship("Clips", backref=backref('Signals', order_by=id), primaryjoin = 'Signals.clip_id == Clips.id', foreign_keys = 'Clips.id') messages = relationship("Messages", backref=backref('Signals', order_by=id), primaryjoin = 'Signals.message_id == Messages.id', foreign_keys = 'Messages.id') def __repr__(self): return "" % (self.message_id, self.clip_id) Index('idx_msg_id', Signals.message_id) The problem is that since I have changed the Engine to InnoDB the foreignKey are being created which is undesired, the problem is that Sqlalchemy is using the foreignKey definition in the Column for defining the Join queries and this definition cannot be dropped. Is there a way to prevent the creation of the FK? or defining the relationships without the adding the foreignKey? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.