Re: [sqlalchemy] Re: Bitwise Flag Type
Hmm, looks like I spoke too soon. Testing against a SQLite database the hybrid attribute approach works fine but I'm having some trouble with SQL Server. Basically, given the structure that Michael laid out, the following query: model = TestModel( flags=1 ) session.add(model) session.commit() result = session.query(TestModel).filter(TestModel.flag_one == True).first() Resullts in this exception: ProgrammingError: (ProgrammingError) (102, Incorrect syntax near '='.DB-Lib error message 102, severity 15: General SQL Server error: Check messages from the SQL Server ) 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags FROM testmodel WHERE ((testmodel.flags %(flags_1)s) %(param_1)s) = 1' {'flags_1': 1, 'param_1': 0} So it looks like the equality comparison is coercing True to 1, I can't figure out which hook I need to use to change this. I've tried to use coerce_compared_value with no effect. Alex On Mon, Mar 31, 2014 at 8:49 PM, Alex quixop...@googlemail.com wrote: The hybrid property and custom comparator approach works like a charm, I had an inkling that hybrid propertyies were the correct approach but hadn't really thought about subclassing hybrid_property, this has helped me understand the custom types architecture a lot, thank very much. Alex On Mon, Mar 31, 2014 at 4:13 PM, Jonathan Vanasco jonat...@findmeon.comwrote: I'm interested in what you find. I know TypeDecorator is the right solution, I was looking at that for this exact same situation a few weeks ago ( https://groups.google.com/forum/#!searchin/sqlalchemy/vanasco%7Csort:date/sqlalchemy/sQtOYxSUiqI/5ns2vWMFaGAJ) I have a similar situation. I wrote a generic bitwise wrapper class that I was ashamed of, but I guess I should eventually release it. i can toss it on github under the MIT if you'd like. The way I have my bitwise stuff working is this: * I create an class that inherits from my `BitwiseSet` class. That subclass stores a mapping of the bitwise values, the parent class has functions for common bitwise operations ( add, remove, has_any, has_all -- both by string and integer ). The parent class has an 'encode' and 'decode' function, which returns an int or list of elements (as int or string ). class BitwiseClassFieldA(BitwiseSet): set_ = { 'flag_a' 1, 'flag_b' 2, 'flag_c' 3, } * I have a property on each of my sqlalchemy objects that works something like this... class Foo(SqlAlchemyObject): bitwise_field_a = sa.Column( sa.Integer, default=0 ) @property def bitwise_manager_field_a(self): if self._bitwise_manager_field_a is None: self. bitwise_manager_field_a = BitwiseClassFieldA(self.bitwise_field_a) return self.bitwise_manager_field_a _ bitwise_manager_field_a = None * when i do saves , i call the manager's `encode` function instance.bitwise_field_a = instance.bitwise_manager_field_a.encode() anyways, when I was trying to get a TypeDecorator working, I was focused on the Integer field having a decorator and managing my object -- not on defining many column attributes like you are. i think that might be an easier avenue, because you want to affect the column itself for these comparisons. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Mu9m1dVU1Gw/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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] Defining CheckConstraint if column value is in list of values
Hi, I want to define a CheckConstraint like this: my_column in ('a', 'b') In Alembic I can write: op.create_check_constraint('ck_name', 'table_name', sqlalchemy.sql.column('my_column').in_('a', 'b')) Using SQLAlchemy I have the problem that the constraint must be defined when defining the column (It was ignored when adding the constraint directly to the mapped class.): The naive approach does not work, because col is not defined in the constraint call: class MyModel(DeclarativeBase): col = sqlalchemy.String(CheckConstraint(col.in_('a', 'b'))) When I try to use sqlalchemy.sql.column(col).in_(a, b) is rendered as col in (:param1, :param2). What is the consistent way to define a CheckConstraint for a list of values? Or at least get a fully complied expression with quoted parameters filled in? (I do not want to use an Enum because changing the values of the Enum is hard.) Thanks in advance, Michael Howitz -- 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] Defining CheckConstraint if column value is in list of values
On Apr 1, 2014, at 8:44 AM, Michael Howitz icema...@gmail.com wrote: Hi, I want to define a CheckConstraint like this: my_column in ('a', 'b') In Alembic I can write: op.create_check_constraint('ck_name', 'table_name', sqlalchemy.sql.column('my_column').in_('a', 'b')) Using SQLAlchemy I have the problem that the constraint must be defined when defining the column (It was ignored when adding the constraint directly to the mapped class.): The naive approach does not work, because col is not defined in the constraint call: class MyModel(DeclarativeBase): col = sqlalchemy.String(CheckConstraint(col.in_('a', 'b'))) When I try to use sqlalchemy.sql.column(col).in_(a, b) is rendered as col in (:param1, :param2). What is the consistent way to define a CheckConstraint for a list of values? Or at least get a fully complied expression with quoted parameters filled in? (I do not want to use an Enum because changing the values of the Enum is hard.) not sure what CheckCosntraint inside of sqlalchemy.String is, assuming that's a typo. Plus in_() requires a list of arguments, and you can't refer to col as itself like that, so assuming this is not a real example. The bound parameters in the CHECK constraint should be rendering as literals, are you on a recent SQLAlchemy 0.8 or 0.9? this was fixed around version 0.8.3 or so. test case: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) __table_args__ = (CheckConstraint(data.in_([1, 2])), ) e = create_engine(sqlite://, echo=True) Base.metadata.create_all(e) output: CREATE TABLE a ( id INTEGER NOT NULL, data VARCHAR, PRIMARY KEY (id), CHECK (data IN (1, 2)) ) -- 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] Bitwise Flag Type
Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that works is the following: SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags FROM testmodel WHERE (testmodel.flags 1) 0 I can get sqlalchemy to emit this like so: session.query(TestModel).filter(TestModel.flag_one) And the negation of it: session.query(TestModel).filter(not_(TestModel.flag_one)) I can't figure out how to emit the required SQL on comparison with a boolean value though. Alex On Tue, Apr 1, 2014 at 1:54 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Apr 1, 2014, at 6:34 AM, Alex quixop...@googlemail.com wrote: Hmm, looks like I spoke too soon. Testing against a SQLite database the hybrid attribute approach works fine but I'm having some trouble with SQL Server. Basically, given the structure that Michael laid out, the following query: model = TestModel( flags=1 ) session.add(model) session.commit() result = session.query(TestModel).filter(TestModel.flag_one == True).first() Resullts in this exception: ProgrammingError: (ProgrammingError) (102, Incorrect syntax near '='.DB-Lib error message 102, severity 15: General SQL Server error: Check messages from the SQL Server ) 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags FROM testmodel WHERE ((testmodel.flags %(flags_1)s) %(param_1)s) = 1' {'flags_1': 1, 'param_1': 0} So it looks like the equality comparison is coercing True to 1, I can't figure out which hook I need to use to change this. I've tried to use coerce_compared_value with no effect. SQL server doesn't have a boolean type, you can only use one and zero. the issue there is more likely the bitwise comparison operators or the nesting of the parenthesis. get that query to work first at the SQL server console to figure out the syntax it wants. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Mu9m1dVU1Gw/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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] Bitwise Flag Type
On Apr 1, 2014, at 11:28 AM, Alex quixop...@googlemail.com wrote: Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that works is the following: SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags FROM testmodel WHERE (testmodel.flags 1) 0 I can get sqlalchemy to emit this like so: session.query(TestModel).filter(TestModel.flag_one) And the negation of it: session.query(TestModel).filter(not_(TestModel.flag_one)) I can't figure out how to emit the required SQL on comparison with a boolean value though. well we've tried to improve boolean rendering to work around this = 1 is true thing, but I've identified more bugs in that regard, and even with the bug fixes that doesn't really help this specific situation. Here, you can have: filter(TestModel.flag_one) filter(~TestModel.flag_one) or: filter(TestModel.flag_one == True) filter(TestModel.flag_one == False) to have both at the same time, is more work. You need to build an __eq__() method that swallows the True and negates on False, and playing with this there some various ways to do this, trying a few here I'm having semi-success. The most foolproof would be to build another special type that does this. -- 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: Session execute mapping
If you have: class MyTable(base): pass You could do session.query( MyTable ).from_statement() * http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.from_statement See also: http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-text http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.text -- 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 access to postgres copy_from in the middle of a session transaction
So I have an ORM session that I've called session.begin() on to start a transaction. I found from some other posts that I can get access to postgres COPY command via psycopg2's copy_from method. So a simple test case for my code looks something like this: session.begin() session.execute(CREATE SCHEMA data); cursor = session.bind.raw_connection().cursor() cursor.copy_from(open(data.raw, data.table1)) The problem I'm having is that the CREATE SCHEMA command was created in the transaction, and the cursor that I'm trying to use copy_from does not contain state from that transaction. What I'm wondering is if there's a way I can get access to the raw cursor that has my pending transaction, so I can call copy_from on it and continue to use sqlalchemy for other things. Regards, J -- 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] Getting access to postgres copy_from in the middle of a session transaction
On Apr 1, 2014, at 6:07 PM, jjaq...@gmail.com wrote: So I have an ORM session that I've called session.begin() on to start a transaction. I found from some other posts that I can get access to postgres COPY command via psycopg2's copy_from method. So a simple test case for my code looks something like this: session.begin() session.execute(CREATE SCHEMA data); cursor = session.bind.raw_connection().cursor() cursor.copy_from(open(data.raw, data.table1)) The problem I'm having is that the CREATE SCHEMA command was created in the transaction, and the cursor that I'm trying to use copy_from does not contain state from that transaction. get the cursor like this: conn = session.connection() # SQLAlchemy Connection dbapi_conn = conn.connection # DBAPI connection (technically a connection pool wrapper called ConnectionFairy, but everything is there) cursor = dbapi_conn.cursor() # actual DBAPI cursor -- 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] Mapping lots of similar tables / database design
I posted this question on Stack Overflow a few days ago, and got some response but nothing that really solves my problem. I'm hoping that I can get some more input here. The initial recommendation was to keep all the data in two tables (one meta and one data table), but this might become unwieldy with the number of rows (I estimate it will be about 6 billion rows in total). I have many (~2000) locations with time series data. Each time series has millions of rows. I would like to store these in a Postgres database. My current approach is to have a table for each location time series, and a meta table which stores information about each location (coordinates, elevation etc). I am using SQLAlchemy to create and query the tables. I would like to have a relationship between the meta table and each time series table to do queries like select all locations that have data between date A and date B and select all data for date A and export a csv with coordinates. What is the best way to create many tables with the same structure (only the name is different) and have a relationship with a meta table? Or should I use a different database design? Currently I am using this type of approach to generate a lot of similar mappings: from sqlalchemy import create_engine, MetaDatafrom sqlalchemy.types import Float, String, DateTime, Integerfrom sqlalchemy import Column, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker, relationship, backref Base = declarative_base() def make_timeseries(name): class TimeSeries(Base): __tablename__ = name table_name = Column(String(50), ForeignKey('locations.table_name')) datetime = Column(DateTime, primary_key=True) value = Column(Float) location = relationship('Location', backref=backref('timeseries', lazy='dynamic')) def __init__(self, table_name, datetime, value): self.table_name = table_name self.datetime = datetime self.value = value def __repr__(self): return {}: {}.format(self.datetime, self.value) return TimeSeries class Location(Base): __tablename__ = 'locations' id = Column(Integer, primary_key=True) table_name = Column(String(50), unique=True) lon = Column(Float) lat = Column(Float) if __name__ == '__main__': connection_string = 'postgresql://user:pw@localhost/location_test' engine = create_engine(connection_string) metadata = MetaData(bind=engine) Session = sessionmaker(bind=engine) session = Session() TS1 = make_timeseries('ts1') # TS2 = make_timeseries('ts2') # this breaks because of the foreign key Base.metadata.create_all(engine) session.add(TS1(ts1, 2001-01-01, 999)) session.add(TS1(ts1, 2001-01-02, -555)) qs = session.query(Location).first() print qs.timeseries.all() This approach has some problems, most notably that if I create more than one TimeSeries object the foreign key doesn't work. Previously I've used some work-arounds (such as not defining a foreign key), but it all seems like a big hack and I feel that there must be a better way of doing this. How should I organise and access my data? Any advice appreciated, Peter -- 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] Mapping lots of similar tables / database design
On Apr 1, 2014, at 6:56 PM, Peter Stensmyr peter.stens...@gmail.com wrote: I posted this question on Stack Overflow a few days ago, and got some response but nothing that really solves my problem. I'm hoping that I can get some more input here. The initial recommendation was to keep all the data in two tables (one meta and one data table), but this might become unwieldy with the number of rows (I estimate it will be about 6 billion rows in total). I have many (~2000) locations with time series data. Each time series has millions of rows. I would like to store these in a Postgres database. My current approach is to have a table for each location time series, and a meta table which stores information about each location (coordinates, elevation etc). I am using SQLAlchemy to create and query the tables. I would like to have a relationship between the meta table and each time series table to do queries like select all locations that have data between date A and date B and select all data for date A and export a csv with coordinates. What is the best way to create many tables with the same structure (only the name is different) and have a relationship with a meta table? Or should I use a different database design? This approach has some problems, most notably that if I create more than one TimeSeries object the foreign key doesn't work. Previously I've used some work-arounds (such as not defining a foreign key), but it all seems like a big hack and I feel that there must be a better way of doing this. How should I organise and access my data? so the idea of making lots of tables and using a function to generate declarative classes with different table names, that's not unheard of. Such an approach is the basis for recipes like the entity name recipe which you can see at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName. Then the idea of each of your TimeSeries reaching out to Location, and having TimeSeries.location, also fine. But then the whole thing goes horribly wrong when you try to claim that there will be a timeseries backref on Location. Think about it. You have 2000 individual Python classes all called TimeSeries, all referring to a different table. This is the question you have to answer (and which I think is going to wind you up back at one table): 1. which one of those 2000 TimeSeries classes do I refer to when I say Location.timeseries ? 2. what SQL would a query like query(Location).join(timeseries) produce? Are you looking for an enormous UNION of 2000 tables? that's not going to work. Think about this in terms of SQL. If you want to query across *all* timeseries at once, having them all in 2000 tables is not going to be possible. If you did have that setup, the approach would be to write a map/reduce query that hits each TimeSeries table separately and figures it out at that level, just like if you were running a noSQL database like CouchDB or something like that. -- 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] Mapping lots of similar tables / database design
On Tue, Apr 1, 2014 at 8:07 PM, Michael Bayer mike...@zzzcomputing.com wrote: Think about it. You have 2000 individual Python classes all called TimeSeries, all referring to a different table. This is the question you have to answer (and which I think is going to wind you up back at one table): 1. which one of those 2000 TimeSeries classes do I refer to when I say Location.timeseries ? 2. what SQL would a query like query(Location).join(timeseries) produce? Are you looking for an enormous UNION of 2000 tables? that's not going to work. Think about this in terms of SQL. If you want to query across *all* timeseries at once, having them all in 2000 tables is not going to be possible. If you did have that setup, the approach would be to write a map/reduce query that hits each TimeSeries table separately and figures it out at that level, just like if you were running a noSQL database like CouchDB or something like that. Thing is, this is not relational data. I believe you need a columnar data store. Which, I guess it's up to you to choose - I don't know many opensource ones. -- 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: Mapping lots of similar tables / database design
ah! i didn't catch this before... backref_name = timeseries_%s % name ## might be better as an the id of the location location = relationship('Location', backref=backref(backref_name, lazy='dynamic')) I don't think he can do one table. originally i did, but I looked into some high-performance sql blogs and posts -- postgres starts to crap-out on performance around a billion rows. it can handle the storing fine, and it's still rock-solid, but it's not fast... and seems to degrade quickly. everyone says you need to start partitioning your tables and indexes at that point. he's got a location-based partition system already. -- 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] pycon 2014
Hi Mike, I really wish I could be there and see your presentations. They're usually recorded right? Just checking and hoping it's recorded... and if so, whoever records it does a great job. :) https://us.pycon.org/2014/speaker/profile/455/ Introduction to SQLAlchemy https://us.pycon.org/2014/schedule/presentation/75/ Thursday 9 a.m.-12:20 p.m. in Room 523 A Building the App https://us.pycon.org/2014/schedule/presentation/187/ Saturday 3:15 p.m.-4 p.m. in Room 517D -- 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] pycon 2014
The tutorial that I do is the same one..for the past few years, so the whole thing is up at http://www.sqlalchemy.org/library.html#tutorials . the talk I'm doing, there'll be video of that, sure. On Apr 1, 2014, at 7:33 PM, nathan nathanma...@gmail.com wrote: Hi Mike, I really wish I could be there and see your presentations. They're usually recorded right? Just checking and hoping it's recorded... and if so, whoever records it does a great job. :) https://us.pycon.org/2014/speaker/profile/455/ Introduction to SQLAlchemy https://us.pycon.org/2014/schedule/presentation/75/ Thursday 9 a.m.-12:20 p.m. in Room 523 A Building the App https://us.pycon.org/2014/schedule/presentation/187/ Saturday 3:15 p.m.-4 p.m. in Room 517D -- 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. -- 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.