getting 'No database selected' randomly from session

[eli rashlin]

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...

[eli rashlin]

BTW is there a way in sqlalchemy to append the name of the database to the table name
like "SELECT * from dbname.table_name"

[Mike Bayer]

it sounds like at some point your program is using "self.connection"
without emitting that "USE" statement, or you have some kind of routine
that is undoing the "USE" and perhaps multiple callers and/or threads
are accessing "self.connection" at the same time.

[Mike Bayer]

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.

[eli rashlin]

Thank you Michael for your answer.
How can I use the schema in a query object, is there a way to do it?

[Mike Bayer]

On 6/3/15 7:43 AM, eli rashlin wrote:

Thank you Michael for your answer.
How can I use the schema in a query object, is there a way to do it?

if the Table has "schema" on it, the schema name will be rendered into queries automatically..

[eli rashlin]

yes but the schema is different for each run, I dont want it to be hard coded into the table definition.

[Mike Bayer]

On 6/4/15 3:28 AM, eli rashlin wrote:

yes but the schema is different for each run, I dont want it to be hard coded into the table definition.

that we don't support.  Issue https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-schema-translation-map-as proposes this.

To get this now you need to write a before_cursor_execute() event that does a search and replace on the SQL string;  use a schema name like "CHANGEME" so it's easy to spot.   The event gets the name of the schema it should use from a thread local variable that you set on the outside.

[eli rashlin]

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 "<Messages ('%s %s')>" % (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...

[Mike Bayer]

On 6/4/15 12:10 PM, eli rashlin wrote:

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

you wouldn't want to use the entity name approach unless you need to refer to multiple schemas in one session at the same time.  It requires that you have a copy of every class for every schema.   If you have only a limited number of schemas, then that might be what you want.  But OTOH if you are doing somethign like multi-tenancy, where each user has their own schema and they only use their schema at once, then you'd want to do this on a per-session basis without using entity name.

Need to know that part before recommending an approach.

[eli rashlin]

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

[Mike Bayer]

you don't need the patch.  Take a look at the recipe here: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL

I added a note on how to make it work for "schema name" on a per-session basis.

On 6/4/15 2:04 PM, eli rashlin wrote:

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

[eli rashlin]

Thank you so much Mike - I'll try it right away :)

[eli rashlin]

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)

[Mike Bayer]

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)

[eli rashlin]

Thank you Michael.