Re: [sqlalchemy] Making a new type out of Postgres DATERANGE + Psycopg2 DateRange
perhaps this could help you? http://sqlalchemy-utils.readthedocs.org/en/latest/range_data_types.html#datetimerangetype richard. On 04/24/2015 10:09 AM, Dimitris Theodorou wrote: Hi, I am using psycopg2 and trying to put together a new daterange type that combines the following: 1. A custom daterange class which provides various useful helpers (backwards/forwards iteration, intersection). 2. The psycopg2 DateRange that fills in with some helpers of its own (contains, comparisons) and takes care of marshaling the object from/to the db 3. The DATERANGE sqlalchemy/postgres type to take care of emiting of SQL operators and DDL, and so that I can use it both when querying and when declaring Columns() So my naive approach is the following, going by the guideline at http://docs.sqlalchemy.org/en/latest/core/compiler.html#subclassing-guidelines: | | |frompsycopg2.extras importDateRange fromsqlalchemy.dialects.postgresql importDATERANGE classMyDateRange(TypeDecorator,DateRange): impl =DATERANGE defintersection(): #... | | |This crashes when emitting a table.create() statement. When I look at the documentation on how to create/modify types I am left dumfounded and I realize I would need a significant trial and error investment to figure out how things work. Does anyone else have experience with such an attempt? I'd rather not become an expert in sqlalchemy type extension before I can make this work. Thanks, Dimitris -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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. attachment: richard.vcf
Re: [sqlalchemy] Re: H2 database
On 4/24/15 12:06 PM, Jonathan Vanasco wrote: SqlAlchemy needs 2 things for a database work: 1. A python database driver 2. A SqlAlchemy dialect (that tells SqlAlchemy how to write sql for the driver) So... 1. H2 doesn't seem to have any Python drivers or other support. I just came across an old forum post that talked about possibly using Postgresql client since they supported similar protocols, but that's about all I could find. 2. There are a handful of posts and articles on writing custom dialects. Several recent ones as well. If you're just trying to handle the dialect, and their syntax is reasonably similar to an existing dialect... I think you could do it alone. If you have to write the general Python support as well though, that's a bigger task. plus, there's a README for general info on new dialects! https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] Making a new type out of Postgres DATERANGE + Psycopg2 DateRange
On 4/24/15 9:09 AM, Dimitris Theodorou wrote: Hi, I am using psycopg2 and trying to put together a new daterange type that combines the following: 1. A custom daterange class which provides various useful helpers (backwards/forwards iteration, intersection). 2. The psycopg2 DateRange that fills in with some helpers of its own (contains, comparisons) and takes care of marshaling the object from/to the db 3. The DATERANGE sqlalchemy/postgres type to take care of emiting of SQL operators and DDL, and so that I can use it both when querying and when declaring Columns() So my naive approach is the following, going by the guideline at http://docs.sqlalchemy.org/en/latest/core/compiler.html#subclassing-guidelines: | | |frompsycopg2.extras importDateRange fromsqlalchemy.dialects.postgresql importDATERANGE classMyDateRange(TypeDecorator,DateRange): impl =DATERANGE defintersection(): #... | | | OK well the type object we make here represents the type, not the value, so psycopg2's DateRange would not be part of the class declaration. SQLAlchemy's DATERANGE type was written to integrate with psycopg2 pretty much, does do a lot of operations, including overlaps, containment, etc. the operators are here: http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory , if you're just looking to add new comparison operators to this class, you'd follow the guidelines at http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html#redefining-and-creating-new-operators. Also, always let us know what crashes means, e.g. stack trace, error message, so that we know what it's doing and we can provide more information. | |This crashes when emitting a table.create() statement. When I look at the documentation on how to create/modify types I am left dumfounded and I realize I would need a significant trial and error investment to figure out how things work. Does anyone else have experience with such an attempt? I'd rather not become an expert in sqlalchemy type extension before I can make this work. Thanks, Dimitris -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] Preserving entity in a query after wrapping that query in additional select
q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...) I have a query which selects some mapped entity as well as other columns. I then refer to the name of that entity when working with the result of the query: for entry in q.all(): recipe=entry.Recipe Now, I want to add filtering by some calculated criteria to my query, and so I wrap it in an additional query: q = q.subquery(); q = session.query(q).filter(q.c.avg_1 10 ) However, this way I can no longer access entry.Recipe! Is there a way to make sqlalchemy adapt names? I tried aliased and select_entity_from, but no luck :( -- 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] Is there a way to preserve entity after wrapping the Query in additional select?
I have a query which selects an entity A and some calculated fields q = session.query(Recipe,func.avg(Recipe.somefield).join(.) I then use what I select in a way which assumes I can subscript result with Recipe string: for entry in q.all(): recipe=entry.Recipe # Access KeyedTuple by Recipe attribute ... Now I need to wrap my query in an additional select, say to filter by calculated field AVG: q=q.subquery(); q= session.query(q).filter(q.c.avg_1 1) And now I cannot access entry.Recipe anymore! Is there a way to *make* SQLAlchemy adapt a query to an enclosing one, like aliased(adapt_on_names=True) or select_from_entity()? I tried using those but was given an error -- 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] Declarative setup failing on upgrade to 1.0.1
I'm trying to upgrade from SA 0.9.8 to 1.0.1 and getting a traceback. I'm not sure what's going on here, but the declarative setup is obviously not happy with something. (On 0.9.8, everything runs fine, so I've obviously run afoul of something new/different/fixed.) File /opt/certwise-lcs/eggs/lcs.content.user-1.0.2dev_r10-py2.7.egg/lcs/content/user/makeorm.py, line 89, in make_orm class Principals(Base): File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/api.py, line 55, in __init__ _as_declarative(cls, classname, cls.__dict__) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 87, in _as_declarative _MapperConfig.setup_mapping(cls, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 102, in setup_mapping cfg_cls(cls_, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 134, in __init__ self._early_mapping() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 137, in _early_mapping self.map() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 530, in map del mp_.class_manager.info['declared_attr_reg'] AttributeError: 'NoneType' object has no attribute 'class_manager' This gets fired off on every ORM class, so I'm guessing the cause is somewhere deeper in our code. Any thoughts on what I might look for to find the cause? -- Bill -- 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] SQLAlchemy 1.0.2 Released
SQLAlchemy release 1.0.2 is now available. As additional regressions are still being reported, we would like to get these changes out as fast as possible so that early adopters are working on a 1.0 version that is most representative of what its final form will be. 1.0.2 includes two additional fixes, one of which was particularly thorny in that it was already fixed in 1.0.0b4, but was not fixed correctly, regarding the placement of plain label names in GROUP BY expressions; so the incorrect fix is reverted and the original regression from 0.9 is resolved as well. Changelog for 1.0.2 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_0_2 SQLAlchemy 1.0.2 is available on the download page: http://www.sqlalchemy.org/download.html -- 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] Declarative setup failing on upgrade to 1.0.1
give 1.0.2 a try since we adjusted some things regarding __declare_first__ and __declare_last__. Further than that it depends a lot on what your basic Base setup looks like, mixins in use, extensions like AbstractConcreteBase, stuff like that. Any details you can share would help. On 4/24/15 7:02 PM, Bill Schindler wrote: I'm trying to upgrade from SA 0.9.8 to 1.0.1 and getting a traceback. I'm not sure what's going on here, but the declarative setup is obviously not happy with something. (On 0.9.8, everything runs fine, so I've obviously run afoul of something new/different/fixed.) File /opt/certwise-lcs/eggs/lcs.content.user-1.0.2dev_r10-py2.7.egg/lcs/content/user/makeorm.py, line 89, in make_orm class Principals(Base): File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/api.py, line 55, in __init__ _as_declarative(cls, classname, cls.__dict__) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 87, in _as_declarative _MapperConfig.setup_mapping(cls, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 102, in setup_mapping cfg_cls(cls_, classname, dict_) File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 134, in __init__ self._early_mapping() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 137, in _early_mapping self.map() File /opt/certwise-lcs/eggs/SQLAlchemy-1.0.1-py2.7-linux-x86_64.egg/sqlalchemy/ext/declarative/base.py, line 530, in map del mp_.class_manager.info['declared_attr_reg'] AttributeError: 'NoneType' object has no attribute 'class_manager' This gets fired off on every ORM class, so I'm guessing the cause is somewhere deeper in our code. Any thoughts on what I might look for to find the cause? -- Bill -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] Re: H2 database
SqlAlchemy needs 2 things for a database work: 1. A python database driver 2. A SqlAlchemy dialect (that tells SqlAlchemy how to write sql for the driver) So... 1. H2 doesn't seem to have any Python drivers or other support. I just came across an old forum post that talked about possibly using Postgresql client since they supported similar protocols, but that's about all I could find. 2. There are a handful of posts and articles on writing custom dialects. Several recent ones as well. If you're just trying to handle the dialect, and their syntax is reasonably similar to an existing dialect... I think you could do it alone. If you have to write the general Python support as well though, that's a bigger task. -- 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] H2 database
I am using the H2 database. (http://h2database.com/html/main.html) It looks like it is not possible to use this database with SQLAlchemy. Is that correct? If so: what kind of effort would it take to make SQLAlchemy work with the H2 database? Would that be something a one person team (yours truly) could pull off? -- Cecil Westerhof -- 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] Add information to reflected tables
That worked perfectly, thanks! Behaves exactly the same as if I had
manually defined all the tables using declarative_base.
On Friday, April 24, 2015 at 10:01:49 AM UTC+12, Michael Bayer wrote:
On 4/23/15 5:15 PM, Jeffrey Yan wrote:
I have a couple of tables that I want to reflect. The first is a data
table where one of the columns is a foreign key to the second table.
If I used SQLAlchemy declarative_base, a query might look something like
this:
session.query(Client.name, Suburb.label).join(Suburb) # In the Client
class there is an attribute suburb_id = Column(Integer,
ForeignKey(Suburb.id))
However, this foreign key is not specified in the schema (we're using
postgres 9.2) but we know all the columns that look like something_id are
foreign keys, so I've been defining them that way using SQLAlchemy.
My problem is, although we have a fixed number of property tables
(suburb, country, join_date, ...) - each data table (per client) can have a
different set of columns.
This hasn't been much of a problem so far, since we only have a few
*types* of client data tables, so the combinations have been limited.
However, I'd like to cater for changes in the future.
If I reflect the table using SQLAlchemy, the resultant table will not
have the ForeignKey columns compared to if I did it manually. Is there a
way to add these in after reflection?
you can do this by instrumenting the process of the Table being built
up.It's weird enough that I had to write a demo to verify it works, so
here it is:
from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy import MetaData, ForeignKey, Column
import re
e = create_engine(postgresql://scott:tiger@localhost/test)
conn = e.connect()
trans = conn.begin()
conn.execute(
create table foo (id integer primary key)
)
conn.execute(
create table bar (id integer primary key, foo_id integer)
)
metadata = MetaData()
@event.listens_for(Column, before_parent_attach)
def associate_fk(column, table):
# if you want to limit the event's scope; a good idea
# else this will take place for Column objects everywhere
if table.metadata is not metadata:
return
m = re.match(r(.+)_id, column.name)
if m:
tname = m.group(1)
column.append_foreign_key(ForeignKey('%s.id' % tname))
metadata.reflect(conn)
foo = metadata.tables['foo']
bar = metadata.tables['bar']
assert bar.c.foo_id.references(foo.c.id)
Or is my only option to use reflected tables and explicit join
conditions? Something like:
client_table_1 = Table('client_table_1', metadata, autoload=True,
autoload_with=engine, schema='client_1')
session.query(client_table_1.c.name,Suburb.label).join(Suburb,
client_table_1.c.suburb_id == Suburb.id) # Explicit joins only from now on
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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] Preserve mapped entities after wrapping the query
q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...) I have a query which selects some mapped entity as well as other columns. I then refer to the name of that entity when working with the result of the query:for entry in q.all(): recipe=entry.Recipe Now, I want to add filtering by some calculated criteria to my query, and so I wrap it in an additional query:q = q.subquery();q = session.query(q).filter(q.c.avg_1 10 ) However, this way I can no longer access entry.Recipe! Is there a way to make sqlalchemy adapt names? I tried aliased and select_entity_from, but no luck :( -- 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] Preserve mapped entities after wrapping the query
On 4/24/15 5:25 PM, Пайков Юрий wrote: q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...) I have a query which selects some mapped entity as well as other columns. I then refer to the name of that entity when working with the result of the query: for entry in q.all(): recipe=entry.Recipe Now, I want to add filtering by some calculated criteria to my query, and so I wrap it in an additional query: q = q.subquery(); q = session.query(q).filter(q.c.avg_1 10 ) However, this way I can no longer access entry.Recipe! Is there a way to make sqlalchemy adapt names? I tried aliased and select_entity_from, but no luck :( this is getting into less reliable stuff, but instead of subquery() - session.query(q), use the from_self() method. It's designed to work this way, and your Recipe entity will be adapted into the subquery. I've observed that the vast majority of my users don't seem to get into queries like these, so from_self() is not as popular (or widely tested) as it should be, but it is at the base of a lot of widely used functions like count() and subquery eager loading, so give it a try: http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self apparently it needs some documentation too :) -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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.
