[sqlalchemy] Subclass all dialect sqltypes

2017-08-23 Thread Richard Postelnik 
I was looking into an issue with a package and noticed that what was 
supposed to be an MSSQL timestamp specific case was being applied to all 
timestamps. Upon further investigation I found that the *mssql.TIMESTAMP* was 
equal to *sa.TIMESTAMP*. Both yield a *sqlalchemy.sql.sqltypese.TIMESTAMP*. 
Is it reasonable to expect that all dialect types yield dialect specific 
subclasses? If so, can this feature be added?

Example:
import sqlalchemy as sa
from sqlalchemy.dialects import mssql
type(sa.TIMESTAMP()) # sqlalchemy.sql.sqltypes.TIMESTAMP
type(mssql.TIMESTAMP())  # sqlalchemy.sql.sqltypes.TIMESTAMP

I would expect *mssql.TIMESTAMP() *to return a 
*sqlalchemy.dialects.mssql.base.TIMESTAMP* even if its not redifining the 
base timestamp class. This would make it consistent with other types that 
are redefined such as *mssql.BIT *which yields 
*sqlalchemy.dialects.mssql.base.BIT.*

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] horizontal sharding and bulk_insert

2017-08-23 Thread Mike Bayer 
On Wed, Aug 23, 2017 at 2:01 AM, Антонио Антуан  wrote:
>
>

>
> So, and now we always make queries without shard_id chosing: we always make
> queries on 'default' database. One exception: when we run app on master and
> read statistics from geo-slaves, we point it explicitly:
> "Session.query(...).set_shard_id(NODE_PREFIX)". So, yes, we use sharding
> mechanizm only for that.
>
> My question still the same: can I override "connection_callable" member with
> None on my "ShardedSessionWithDefaultBind" class?

Sure, the ShardedSession is supposed to be more of an example class
that you can hack on and connection_callable is a simple None/
not-None thing that is used when you flush() to get a list of
connections per row, where each connection is decided based on the
sharding conditionals.


>
> Also, if you can advise any other mechanism to make things as simple as we
> have them now (with horizontal_sharding mechanism), it would be great, our
> team will appreciate it :)

My suggestion was that bulk_insert_mappings() / bulk_update_mappings /
bulk_save_objects don't actually make the objects as any part of the
session's state, nor does the existing object state of the session
have any bearing on the operation of bulk_insert/bulk_update other
than being part of the same transaction.   So if you don't want to
tinker with the state of your sharded_session, just use a different
Session.

If you have code like this:

 some_stuff = my_sharded_session.query(Stuff).filter(...).all()

 my_sharded_session.bulk_save_objects(objects)

you could change it to this:

 some_stuff = my_sharded_session.query(Stuff).filter(...).all()

 ad_hoc_session =
Session(bind=my_sharded_session.connection(shard_id='default'))
 ad_hoc_session.bulk_save_objects(objects)

that way you don't have to change the state of my_sharded_session.
It probably doesn't matter much either way, e.g.
session.connection_callable=None or this, assuming there is no
concurrent sharing of the sharded_session instance.



>
>>
>> >
>> > If it can help: I ALWAYS perform UPDATE/INSERT/DELETE and most of SELECT
>> > queries in only one database. So, I wrote such subclass:
>> >
>> >
>> > class ShardedSessionWithDefaultBind(ShardedSession):
>> > def get_bind(self, mapper, shard_id=None, instance=None,
>> > clause=None,
>> > **kw):
>> > if shard_id is None:
>> > shard_id = default_shard_id
>> > return super(ShardedSessionWithDefaultBind,
>> > self).get_bind(mapper,
>> > shard_id, instance, clause, **kw)
>> >
>> >
>> > Maybe I can override "__init__" for my class and write
>> > "self.connnection_callable = None"?
>> > My research of sqlalchemy code didn't make me sure that it is safe
>> > enough.
>> > But I see, that "connection_callable" used only for checking into
>> > "_bulk_insert" and "_bulk_update" functions in
>> > sqlalchemy.orm.persistence
>> > module.
>> > So, if I 100% sure, that I ALWAYS perform INSERT/UPDATE/DELETE queries
>> > in
>> > only one database, can I make it?
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> > To post example code, please provide an MCVE: Minimal, Complete, and
>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> > description.
>> > ---
>> > 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 https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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

Re: [sqlalchemy] Set up a composite key using a foreign key and another column with Python

2017-08-23 Thread Joshua Peppeman 

That worked! I'd still like to know about orderinglist though. Am I going 
to run into problems if I end up with a lot of data?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: getting model's grandparent after persistent_to_deleted event has fired.

2017-08-23 Thread Felix Ruiz de Arcaute 
ok thanks. Unfortunately, I'll have to mess with my data model, but if
there is no other way, I'll do it like that.

On Wed, Aug 23, 2017 at 4:19 AM, Mike Bayer 
wrote:

> On Tue, Aug 22, 2017 at 6:46 PM, cecemel  wrote:
> > Hello,
> >
> > thanks again for the answer. Perhaps my case was not clear enough. To
> make
> > it a bit more explicit, I updated the example accordingly.
> >
> > from sqlalchemy import event
> >
> > from sqlalchemy import *
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import sessionmaker, backref, relationship
> >
> > Base = declarative_base()
> >
> >
> > 
> #
> > # MODEL
> > 
> #
> > class House(Base):
> > __tablename__ = 'house'
> > id = Column(Integer, primary_key=True)
> > rooms = relationship("Room",
> >  backref=backref("house", lazy="joined"),
> >  cascade='all, delete-orphan')
> >
> >
> > class Room(Base):
> > __tablename__ = 'room'
> > id = Column(Integer, primary_key=True)
> > house_id = Column(Integer, ForeignKey('house.id'))
> > beds = relationship("Bed",
> > backref=backref("room", lazy="joined"),
> > cascade='all, delete-orphan')
> >
> >
> > class Bed(Base):
> > __tablename__ = 'bed'
> > id = Column(Integer, primary_key=True)
> > room_id = Column(Integer, ForeignKey('room.id'))
> >
> >
> > 
> #
> > # CONFIG
> > 
> #
> > def setup():
> > engine = create_engine("sqlite://", echo=True)
> >
> > Base.metadata.bind = engine
> > Base.metadata.create_all(engine)
> >
> > SessionFactory = sessionmaker(
> > bind=engine
> > )
> >
> > event.listen(SessionFactory, 'deleted_to_detached',
> > listener_bed_has_been_removed)
> >
> > return SessionFactory
> >
> >
> > def listener_bed_has_been_removed(session, instance):
> > if type(instance) is not Bed:
> > return
> >
> > # so, in this example, this function should be called 3 times.
> > # The first time it is called, I get no problems, I can access
> > instance.room.house_id the call proceeds
> > # The second bed is a problem, I get the error
> > # "sqlalchemy.orm.exc.DetachedInstanceError: Parent instance  > 0x7f24fe14bb70> is not bound to a Session; lazy load operation of
> attribute
> > 'room' cannot proceed"
> >
> > # SO, my question is: is there ANY way to keep these references to
> > parents in this function?
> >
> > bed_id = instance.id
> > house_id = instance.room.house_id
> >
> >
> > print("execute a service call to external service here bed_id {},
> > house_id {}".format(bed_id, house_id))
> >
> >
> > if __name__ == "__main__":
> > session_factory = setup()
> > session = session_factory()
> >
> > session.add(House(id=1))
> > session.add(Room(id=1, house_id=1))
> > session.add(Bed(id=1, room_id=1))
> > session.add(Bed(id=2, room_id=1))
> > session.add(Bed(id=3, room_id=1))
> > session.commit()
> >
> > room = session.query(Room).get(1)
> > session.delete(room)
> > session.commit()
> > session.close()
> >
> >
> > So, for this example, I am looking for a solution to keep the references
> to
> > the 'house' from the 'bed' model after flush (any solution would be
> good).
> > Is there perhaps a way to dynamically set a property in 'bed' -model (e.g
> > house_id), once 'room'-backref has been loaded ?
>
> so the way you have this set up, you have not only room->bed but you
> also have delete,delete-orphan cascade.  this indicates your intent
> that a Bed object should be deleted when it is both not associated
> with any Room, as well as when its parent Room is deleted.   So the
> behavior you are getting is what you specified.
>
> if you *dont* want Bed to be deleted when Room is deleted, you'd want
> to remove that delete-orphan casacde.  If you then want Bed to be
> directly associated with House in the absense of Room, then yes you'd
> add another column house_id to Bed with a corresponding
> relationship(), and you'd need to make sure that is assigned as you
> want as well when the objects go into the database.
>
>
>
>
>
> >
> >
> > On Tuesday, August 22, 2017 at 5:36:39 PM UTC+2, Mike Bayer wrote:
> >>
> >> you would need to illustrate an MCVE of what's happening.  objects
> >> don't "lose track" of their related objects unless yes, you deleted
> >> them, in which case you should not expect that they would be there.
> >> when the object is expired, it will go to reload them, and they'll be
> >> gone.
> >>
> >> On Tue, Aug 22, 2017 at

Re: [sqlalchemy] horizontal sharding and bulk_insert

2017-08-23 Thread Антонио Антуан 


вторник, 22 августа 2017 г., 17:29:41 UTC+3 пользователь Mike Bayer написал:
>
> On Tue, Aug 22, 2017 at 3:43 AM, Антонио Антуан  > wrote: 
> > Hi guys 
> > I tried to implement horizontal sharding in my project. Everything is 
> ok, 
> > except bulk_inserts. 
> > When I run tests, I got this error: 
> >   File "/home/anton/Projects/proj/core/model/messages.py", line 210, in 
> > create 
> > Session.bulk_insert_mappings(MessageEntity, to_commit) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/scoping.py", line 
> 157, 
> > in do 
> > return getattr(self.registry(), name)(*args, **kwargs) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2345, 
> > in bulk_insert_mappings 
> > mapper, mappings, False, False, return_defaults, False) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2416, 
> > in _bulk_save_mappings 
> > transaction.rollback(_capture_exception=True) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", 
> line 
> > 60, in __exit__ 
> > compat.reraise(exc_type, exc_value, exc_tb) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line 
> 2411, 
> > in _bulk_save_mappings 
> > mapper, mappings, transaction, isstates, return_defaults) 
> >   File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/persistence.py", 
> line 
> > 35, in _bulk_insert 
> > "connection_callable / per-instance sharding " 
> > NotImplementedError: connection_callable / per-instance sharding not 
> > supported in bulk_insert() 
> > 
> > I do not understand what 'connection_callable' does, but I really need 
> to 
> > implement it. Is there any ways to do it? 
>
> The idea of bulk_insert() is strictly one of performance with some 
> degree of convenience; you don't need it to actually accomplish 
> anything as there are many other ways to achieve what it does.   the 
> "connection_callable" thing is specifically so that an ORM persistence 
> operation can get at the appropriate database connection for a 
> particular INSERT/UPDATE/DELETE, however this adds complexity so is 
> not part of bulk_insert(). 
>
> In the case of application side horizontal sharding, you'd want to 
> take the data you're passing to it and pre-sort it into individual 
> per-shard sets, then use a Session per shard to run the inserts. 
>
> Also, application side horizontal sharding is not a great way to do 
> sharding in any case.  If you're on Postgresql I'd strongly recommend 
> using table inheritance instead. 
>
 
Yes, Postgresql used into a project. But I can't use inheritance, because I 
have several servers, each of them located in separate country. 
Yes, I could use inheritance and FDW, but in that case system become more 
complicated.

When application instance runs on master node - it can read and write to 
master database and can only read from geo-slaves.
When application instance runs on geo-slave - it can read from master, read 
from replicated table on current node and write data (some statistics) to 
some table on current node.
Periodically master read data-tables (statistics) from each geo-slave and 
write new data on master database.


Previously my project used something like that:
class ClusterMeta(object):
_pg_config = {}
_pg_sessions = defaultdict(list)
is_slave = config.get('default', 'is_slave')
prefix = config.get('default', 'prefix')

def _get_pg_conf(self):
for name, conn_string in config['sqlalchemy'].items():
if name.startswith('conn_string_slave_'):
sp = name.replace('conn_string_slave_', '').lower()
self._pg_config[sp] = conn_string
elif name in ['conn_string_master', 'conn_string']:
sp = 'default' if name == 'conn_string' else 'master'
if sp in self._pg_config:
raise ValueError('{} conn_string already 
configured!'.format(sp.upper()))
else:
self._pg_config[sp] = conn_string

def _create_pg_session(self, conn_string):
"""
:rtype: tuple(base, session)
"""
engine = create_engine(conn_string,
   convert_unicode=True)

session = ScopedSession(sessionmaker(bind=engine))
base = declarative_base()
base.query = session.query_property()

return base, session

   def get_session(self, prefix, with_base=False):
if prefix not in self._pg_sessions:
conn_string = self._pg_config.get(prefix)
if not conn_string:
raise ValueError('No conn_string for prefix "%s"' % prefix)
if prefix not in self._pg_sessions:
self._pg_sessions[prefix] = []
s = self._create_pg_session(conn_string)
self._pg_sessions[prefix].insert(0, s)
else:
s = self._pg_sessions[prefix][0]
return s if