Re: [sqlalchemy] Reflected Table FK
It makes sense that as foreign keys can be composite, that all foreign keys would be considered composite, thank you, I just had no idea that foreign keys could be composite in the same way that primary keys could be. Thanks again On Fri, Jan 1, 2021 at 8:29 AM Mike Bayer wrote: > A particular column may be constrained by more than one foreign key > constraint (although this is very uncommon), and a particular foreign key > constraint may be "composite" in that it refers to multiple columns. All > primary and foreign key constructs in SQLAlchemy are inherently > composite.having APIs that refer to single-column keys, like > "table.primary_key_column" and stuff like that, encourages applications to > hardcode themselves to be non-composite, and there's really no point in > doing so.If I'm writing a program that looks at primary and foreign key > constraints in an abstract sense I would want it to assume composite in all > cases. > > > > On Thu, Dec 31, 2020, at 3:53 PM, Matthew Graham wrote: > > Are you saying with the multiple foreign keys that for each key, multiple > columns can be used? I am aware that if you had table A and it had columns > B_id and C_id that B_id may be a fk to B and C_id may be a reign key to C, > but are you essentially saying there is an equivalence to a "composite" > foreign key so that you can have a foreign key on columns B_id1 and B_id2 > in A to refer to, only in conjunction, B? > Also thanks for referring me to inspector, I will give that a go > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAP-Gp5PoX7vvcfOUN2j3RS1LM8Cv300YmT7Ne3e1YzXc3J3Gkg%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAP-Gp5PoX7vvcfOUN2j3RS1LM8Cv300YmT7Ne3e1YzXc3J3Gkg%40mail.gmail.com?utm_medium=email_source=footer> > . > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/12781196-61f2-4ac7-9df3-762982e5cac4%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/12781196-61f2-4ac7-9df3-762982e5cac4%40www.fastmail.com?utm_medium=email_source=footer> > . > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAP-Gp5OEMB50h%3DSeOY-jTg5BGp0_7hvhEd8dGzWBbOX6TUHQrA%40mail.gmail.com.
Re: [sqlalchemy] Reflected Table FK
Are you saying with the multiple foreign keys that for each key, multiple columns can be used? I am aware that if you had table A and it had columns B_id and C_id that B_id may be a fk to B and C_id may be a reign key to C, but are you essentially saying there is an equivalence to a "composite" foreign key so that you can have a foreign key on columns B_id1 and B_id2 in A to refer to, only in conjunction, B? Also thanks for referring me to inspector, I will give that a go -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAP-Gp5PoX7vvcfOUN2j3RS1LM8Cv300YmT7Ne3e1YzXc3J3Gkg%40mail.gmail.com.
[sqlalchemy] Reflected Table FK
Hi I am new to SQLAlchemy, I am trying to extract from reflected tables: 1. the column name of the current table 2. the referred table name and 3. the column name of the referred table now I can manage to do this using (and for sake only using first forein key) *metadata = sqlalchemy.MetaData()metadata.reflect(engine)for table in metadata.tables.values():print(table.name)* --- Current table name *l = list(table.foreign_keys)if len(l) > 0: print(l[0].column)* --- Referred table column name (but is prepended with table name and a .) *for fk in table.foreign_key_constraints: print(fk.column_keys[0])* --- current table column name but *print(fk.referred_table)* --- Referred table name There must surely be a cleaner way to do this preferably: - not needing to convert table.foreign_keys to a list and then check list length - getting referred table column name without the foreign table prepended as would rather not have to add in regex replacements - get the current table column name without having to index with column keys[0] Thanks you -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/093cb4b3-381a-4476-aff9-02471d61efa1n%40googlegroups.com.
Re: [sqlalchemy] Loading Multiple Self-Referential Relationships
Thank you for the response! Unfortunately, that did not resolve the issue.
I tried both removing the keyword and replacing it with options 'select' and
'joined' to no avail.
On Monday, January 14, 2019 at 4:22:43 PM UTC-8, Mike Bayer wrote:
>
> On Mon, Jan 14, 2019 at 2:22 PM >
> wrote:
> >
> > Hello,
> >
> > Our data model for a structure named PlateWell in our software utilizes
> a singly linked-list structure with a field for a parent PlateWell. We are
> adding another field pointing to the root PlateWell of the link list to
> improve runtime of certain important operations, however the relationships
> are not loading. Here is the abridged model.
> >
> > class PlateWell(db.Model):
> > __tablename__ = 'plate_wells'
> >
> > id = db.Column(db.Integer, primary_key=True)
> > parent_well_id = db.Column(db.Integer, db.ForeignKey('plate_wells.id'),
> index=True)
> > root_well_id = db.Column(db.Integer, db.ForeignKey('plate_wells.id'),
> index=True)
> >
> > parent_well = db.relationship(
> > 'PlateWell', uselist=False, lazy='noload', remote_side=[id],
> foreign_keys=[parent_well_id])
> > root_well = db.relationship(
> > 'PlateWell', uselist=False, lazy='noload', remote_side=[id],
> foreign_keys=[root_well_id])
> >
> > At the root entry, the root_well_id is equal to id and parent_well_id is
> set to None.
> >
> > Prior to adding the root relationship, the model loaded the parent_well
> relationship without the foreign_keys kwarg. This relationship was fully
> functional.
> >
> > Now, any time a plate well is loaded via a query like
> >
> > well = PlateWell.query.filter(PlateWell.id == well.plate_well_id).one()
> >
> > both parent_well and root_well are set to None, despite both foreign key
> id fields containing an id that does relate to another entry. We've
> attempted adding explicit primaryjoin kwargs, expiring the session to clear
> the cache, but neither seems to work. Is there a trick to loading multiple
> self-referential relationships or some other change we can make to properly
> load these relationships?
>
> I haven't tried your model but the "lazy='noload'" doesn't seem like
> what you want to be doing there, that loader option explicitly
> indicates you want nothing to load so that would likely be where
> "None" is coming from. Removing that option will likely allow it to
> work.
>
>
>
> >
> > This e-mail is private and confidential and is for the addressee only.
> If misdirected, please notify us by telephone, confirming that it has been
> deleted from your system and any hard copies destroyed. You are strictly
> prohibited from using, printing, distributing or disseminating it or any
> information contained in it save to the intended recipient.
> >
> > --
> > 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.
>
--
This e-mail is private and confidential and is for the addressee only. If
misdirected, please notify us by telephone, confirming that it has been
deleted from your system and any hard copies destroyed. You are strictly
prohibited from using, printing, distributing or disseminating it or any
information contained in it save to the intended recipient.
--
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] Loading Multiple Self-Referential Relationships
Hello,
Our data model for a structure named PlateWell in our software utilizes a
singly linked-list structure with a field for a parent PlateWell. We are
adding another field pointing to the root PlateWell of the link list to
improve runtime of certain important operations, however the relationships
are not loading. Here is the abridged model.
class PlateWell(db.Model):
__tablename__ = 'plate_wells'
id = db.Column(db.Integer, primary_key=True)
parent_well_id = db.Column(db.Integer, db.ForeignKey('plate_wells.id'),
index=True)
root_well_id = db.Column(db.Integer, db.ForeignKey('plate_wells.id'),
index=True)
parent_well = db.relationship(
'PlateWell', uselist=False, lazy='noload', remote_side=[id],
foreign_keys=[parent_well_id])
root_well = db.relationship(
'PlateWell', uselist=False, lazy='noload', remote_side=[id],
foreign_keys=[root_well_id])
At the root entry, the root_well_id is equal to id and parent_well_id is
set to None.
Prior to adding the root relationship, the model loaded the parent_well
relationship without the foreign_keys kwarg. This relationship was fully
functional.
Now, any time a plate well is loaded via a query like
well = PlateWell.query.filter(PlateWell.id == well.plate_well_id).one()
both parent_well and root_well are set to None, despite both foreign key id
fields containing an id that does relate to another entry. We've attempted
adding explicit primaryjoin kwargs, expiring the session to clear the
cache, but neither seems to work. Is there a trick to loading multiple
self-referential relationships or some other change we can make to properly
load these relationships?
--
This e-mail is private and confidential and is for the addressee only. If
misdirected, please notify us by telephone, confirming that it has been
deleted from your system and any hard copies destroyed. You are strictly
prohibited from using, printing, distributing or disseminating it or any
information contained in it save to the intended recipient.
--
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] Oracle - How to select from a table partition? - SELECT FROM table PARTITION (p0)
I have an Oracle partitioned table created like so:
DROP TABLE foos;
CREATE TABLE foos (
bar VARCHAR2(10)
) PARTITION BY HASH (bar) (
PARTITION P0,
PARTITION P1,
PARTITION P2,
PARTITION P3
);
CREATE TABLE hellos (
bar VARCHAR2(10)
);
I want to be able to issue deep, complicated queries against it with
SQLAlchemy, ideally without changing the query too much to get it working
with SQLAlchemy. Here is a contrived example:
SELECT fo.foo_bar, fo.hello_bar FROM (
SELECT f.bar foo_bar, h.bar hello_bar
FROM foos *PARTITION (P0)* f
JOIN hellos h
ON f.bar = h.bar
WHERE f.bar = 'baz'
) fo;
Is there a sqlalchemy customization that can be made to handle this?
I basically need to insert the string 'PARTITION (P0)' between the table
name and the alias name. `sel.suffix_with` doesn't work as it will suffix
the end of the query (after the WHERE clause), unless I use a needless
subquery with no clauses.
Here is some set up code:
from sqlalchemy import Table, Column, String, MetaData, select, text, table
metadata = MetaData()
foos = Table('foos', metadata, Column('bar', String(10)))
hellos = Table('hellos', metadata, Column('bar', String(10)))
# To print a regular query without the PARTITION (P0):
f = foos.alias('f')
h = hellos.alias('h')
sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')])
sel = sel.where(f.c.bar == 'baz')
sel = sel.select_from(f.join(h, f.c.bar==h.c.bar))
sel = sel.alias('fo')
out_sel = select(sel.columns)
>>> print out_sel
SELECT fo.foo_bar, fo.hello_bar
FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar
FROM foos AS f JOIN hellos AS h ON f.bar = h.bar
WHERE f.bar = :bar_1) AS fo
# I know that I can get this contrived example working like this, but I
would rather not and I believe it won't cover all my use cases:
*partition_sel = select([foos.c.bar]).suffix_with('PARTITION (P0)')*
f = partition_sel.alias('f')
h = hellos.alias('h')
sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')])
sel = sel.where(f.c.bar == 'baz')
sel = sel.select_from(f.join(h, f.c.bar==h.c.bar))
sel = sel.alias('fo')
out_sel = select(sel.columns)
>>> print out_sel
SELECT fo.foo_bar, fo.hello_bar
FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar
FROM
*(SELECT foos.bar AS bar FROM foos PARTITION (P0) )* AS f JOIN hellos AS h
ON f.bar = h.bar
WHERE f.bar = :bar_1) AS fo
I tried using `table` to insert custom text as mentioned in the SQL
Expression Language Tutorial
<http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-more-specific-text-with-table-literal-column-and-column>
but it prints it out using quotes:
from sqlalchemy import table
f = table(*'foos PARTITION (P0)'*, *foos.columns).alias('f')
h = hellos.alias('h')
sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')])
sel = sel.where(f.c.bar == 'baz')
sel = sel.select_from(f.join(h, f.c.bar==h.c.bar))
sel = sel.alias('fo')
out_sel = select(sel.columns)
>>> print out_sel
SELECT fo.foo_bar, fo.hello_bar
FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar
FROM "foos PARTITION (P0)" AS f JOIN hellos AS h ON f.bar = h.bar
WHERE f.bar = :bar_1) AS fo
Perhaps if I could disable the use of quotes just for this one query, it
would work. However I wouldn't want to disable quotes engine-wide
Thanks and best regards,
Matthew
--
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] How to add comments inside a big query using Classical SQLAlchemy?
Hi Mike,
Thanks so much - this is excellent.
Best regards,
Matthew
On Fri, Oct 27, 2017 at 10:30 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> On Thu, Oct 26, 2017 at 8:23 PM, Matthew Moisen <mkmoi...@gmail.com>
> wrote:
> > Hi Mike,
> >
> > Thanks for your reply.
> >
> > I now have comments activated for my joins and exists and some other
> > functions. I'm still at a loss for how to add comments to an indivdiual
> > column, function, or CASE in the select statment. Would you mind giving
> me a
> > pointer?
>
>
> I added a POC to the wiki to illustrate your original SELECT statement:
>
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/CompiledComments
>
>
> >
> > Any idea how I can go about monkey patching the base ClauseElement?
> >
> > Thanks and best regards,
> >
> > Matthew
> >
> > On Wed, Oct 4, 2017 at 7:29 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> >>
> >> On Wed, Oct 4, 2017 at 5:57 PM, Matthew Moisen <mkmoi...@gmail.com>
> wrote:
> >> > I'm using Classic SQLAlchemy to create SQL statements, which are later
> >> > formatted with sqlparse and stored in a file. I'm not executing the
> SQL
> >> > through SQLAlchemy.
> >> >
> >> >
> >> > Some of the queries have complexities that would benefit from a
> >> > comments. Is
> >> > there any way to get SQLAlchemy to output a query like the following?
> >> >
> >> >
> >> > -- Comment explaining the query
> >> > SELECT foo,
> >> > -- comment explaining the convoluted case statement
> >> > CASE WHEN .. END as complicated_case,
> >> > -- comment exaplaining the convoluted window function
> >> > ROW_NUMBER() OVER (PARTITION BY ..., ORDER BY ...) as
> >> > complicated_row_num
> >> >
> >> > FROM bar JOIN (
> >> > -- Comment explaining subquery and join
> >> > SELECT ...
> >> > ) WHERE 1=1
> >> >-- comment explaining the purpose of the EXISTS clause
> >> >AND EXISTS (SELECT ...)
> >> >
> >> > Reading through this user group, I saw a few posts related to comments
> >> > and
> >> > the ORM. The recommended solution was this link:
> >> >
> >> > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/
> SessionModifiedSQL
> >> >
> >> > However for my use case, I am not executing any of the sql. I'm
> >> > basically
> >> > doing things like this:
> >> >
> >> > sel = select([...])
> >> > sql = str(sel.compile(dialect=oracle.dialect(),
> >> > compile_kwargs={'literal_binds': True})
> >>
> >>
> >> you would need to create custom compilation functions for all the
> >> constructs you're looking to add comments with, and additionally tack
> >> on a comment to each one manually:
> >>
> >> join = foo.join(bar)
> >> join.comment = "some comment"
> >>
> >> then you'd need to compile for Join:
> >>
> >> from sqlalchemy.ext.compiler import compiles
> >>
> >> @compiles(Join)
> >> def _comment_join(element, compiler, **kw):
> >> comment = getattr(element, 'comment')
> >> if comment:
> >>text = "-- %s" % comment
> >>else:
> >>text = ""
> >> return text + compiler.visit_join(element, **kw)
> >>
> >>
> >> a bit tedious but it would be a start and can perhaps be generalized a
> >> bit once you get it going for many constructs.You can in theory
> >> monkeypatch a comment() method onto the base ClauseElement construct
> >> to.
> >>
> >> see http://docs.sqlalchemy.org/en/latest/core/compiler.html .
> >>
> >> >
> >> > with open(file_name, 'w') as f:
> >> > f.write(sql)
> >> >
> >> > Thanks and best regards,
> >> >
> >> > Matthew Moisen
> >> >
> >> > --
> >> > 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.
> >> > ---
> >> >
Re: [sqlalchemy] How to add comments inside a big query using Classical SQLAlchemy?
Hi Mike,
Thanks for your reply.
I now have comments activated for my joins and exists and some other
functions. I'm still at a loss for how to add comments to an indivdiual
column, function, or CASE in the select statment. Would you mind giving me
a pointer?
Any idea how I can go about monkey patching the base ClauseElement?
Thanks and best regards,
Matthew
On Wed, Oct 4, 2017 at 7:29 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:
> On Wed, Oct 4, 2017 at 5:57 PM, Matthew Moisen <mkmoi...@gmail.com> wrote:
> > I'm using Classic SQLAlchemy to create SQL statements, which are later
> > formatted with sqlparse and stored in a file. I'm not executing the SQL
> > through SQLAlchemy.
> >
> >
> > Some of the queries have complexities that would benefit from a
> comments. Is
> > there any way to get SQLAlchemy to output a query like the following?
> >
> >
> > -- Comment explaining the query
> > SELECT foo,
> > -- comment explaining the convoluted case statement
> > CASE WHEN .. END as complicated_case,
> > -- comment exaplaining the convoluted window function
> > ROW_NUMBER() OVER (PARTITION BY ..., ORDER BY ...) as
> complicated_row_num
> >
> > FROM bar JOIN (
> > -- Comment explaining subquery and join
> > SELECT ...
> > ) WHERE 1=1
> >-- comment explaining the purpose of the EXISTS clause
> >AND EXISTS (SELECT ...)
> >
> > Reading through this user group, I saw a few posts related to comments
> and
> > the ORM. The recommended solution was this link:
> > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/
> SessionModifiedSQL
> >
> > However for my use case, I am not executing any of the sql. I'm basically
> > doing things like this:
> >
> > sel = select([...])
> > sql = str(sel.compile(dialect=oracle.dialect(),
> > compile_kwargs={'literal_binds': True})
>
>
> you would need to create custom compilation functions for all the
> constructs you're looking to add comments with, and additionally tack
> on a comment to each one manually:
>
> join = foo.join(bar)
> join.comment = "some comment"
>
> then you'd need to compile for Join:
>
> from sqlalchemy.ext.compiler import compiles
>
> @compiles(Join)
> def _comment_join(element, compiler, **kw):
> comment = getattr(element, 'comment')
> if comment:
>text = "-- %s" % comment
>else:
>text = ""
> return text + compiler.visit_join(element, **kw)
>
>
> a bit tedious but it would be a start and can perhaps be generalized a
> bit once you get it going for many constructs.You can in theory
> monkeypatch a comment() method onto the base ClauseElement construct
> to.
>
> see http://docs.sqlalchemy.org/en/latest/core/compiler.html .
>
> >
> > with open(file_name, 'w') as f:
> > f.write(sql)
> >
> > Thanks and best regards,
> >
> > Matthew Moisen
> >
> > --
> > 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/BgZx_zvtVvA/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 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] How to add comments inside a big query using Classical SQLAlchemy?
I'm using Classic SQLAlchemy to create SQL statements, which are later
formatted with sqlparse and stored in a file. I'm not executing the SQL
through SQLAlchemy.
Some of the queries have complexities that would benefit from a comments.
Is there any way to get SQLAlchemy to output a query like the following?
*-- Comment explaining the query*
SELECT foo,* -- comment explaining the convoluted case statement*
CASE WHEN .. END as complicated_case,
* -- comment exaplaining the convoluted window function*
ROW_NUMBER() OVER (PARTITION BY ..., ORDER BY ...) as complicated_row_num
FROM bar JOIN (
* -- Comment explaining subquery and join*
SELECT ...
) WHERE 1=1* -- comment explaining the purpose of the EXISTS clause*
AND EXISTS (SELECT ...)
Reading through this user group, I saw a few posts related to comments and the
ORM. The recommended solution was this link:
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL
However for my use case, I am not executing any of the sql. I'm basically doing
things like this:
sel = select([...])
sql = str(sel.compile(dialect=oracle.dialect(),
compile_kwargs={'literal_binds': True})
with open(file_name, 'w') as f:
f.write(sql)
Thanks and best regards,
Matthew Moisen
--
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] How to create a Partitioned Oracle Table in SQLAlchemy?
Thanks Mike, I can confirm this works.
For reference, if anyone is using ORM, you can provide the `info` as an
attribute to `__table_args__`, like:
class Foo(Base):
__tablename__ = 'foo'
name = Column(String(10), primary_key=True)
__table_args__ = {
'info': {
'oracle_partition': """
PARTITION BY HASH(name)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
)
"""
}
}
Best regards,
Matthew
On Thursday, March 23, 2017 at 7:17:13 AM UTC-7, Mike Bayer wrote:
>
> Here is a recipe using compilation extension
> (http://docs.sqlalchemy.org/en/rel_1_1/core/compiler.html):
>
> """
> CREATE TABLE sales_hash
>(s_productid NUMBER,
> s_saledate DATE,
> s_custid NUMBER,
> s_totalprice NUMBER)
> PARTITION BY HASH(s_productid)
> ( PARTITION p1 TABLESPACE tbs1
> , PARTITION p2 TABLESPACE tbs2
> , PARTITION p3 TABLESPACE tbs3
> , PARTITION p4 TABLESPACE tbs4
> );
> """
>
>
> from sqlalchemy.schema import CreateTable
> from sqlalchemy.ext.compiler import compiles
> import textwrap
>
>
> @compiles(CreateTable, "oracle")
> def _add_suffixes(element, compiler, **kw):
> text = compiler.visit_create_table(element, **kw)
> if "oracle_partition" in element.element.info:
> text += textwrap.dedent(
> element.element.info["oracle_partition"]).strip()
> return text
>
>
> if __name__ == '__main__':
> from sqlalchemy import create_engine, DATE, Table, MetaData, Column
> from sqlalchemy.dialects.oracle import NUMBER
>
> # use mock strategy just to illustrate this w/o my getting
> # on an oracle box
> def execute_sql(stmt):
> print stmt.compile(dialect=engine.dialect)
> engine = create_engine("oracle://", execute_sql, strategy="mock")
>
> m = MetaData()
> t = Table(
> 'sales_hash', m,
> Column('s_productid', NUMBER),
> Column('s_saledate', DATE),
> Column('s_custid', NUMBER),
> Column('s_totalprice', NUMBER),
> info={
> "oracle_partition": """
> PARTITION BY HASH(s_productid)
> ( PARTITION p1 TABLESPACE tbs1
> , PARTITION p2 TABLESPACE tbs2
> , PARTITION p3 TABLESPACE tbs3
> , PARTITION p4 TABLESPACE tbs4
> )
> """
> }
> )
>
> m.create_all(engine, checkfirst=False)
>
>
>
>
>
> On 03/22/2017 10:11 PM, Matthew Moisen wrote:
> > Hello,
> >
> > In Oracle we can create a Partitioned Table like the following:
> >
> > CREATE TABLE sales_hash
> > (s_productid NUMBER,
> >s_saledate DATE,
> >s_custid NUMBER,
> >s_totalprice NUMBER)
> > PARTITION BY HASH(s_productid)
> > ( PARTITION p1 TABLESPACE tbs1
> > , PARTITION p2 TABLESPACE tbs2
> > , PARTITION p3 TABLESPACE tbs3
> > , PARTITION p4 TABLESPACE tbs4
> > );
> >
> > Is it possible in SQLAlchemy to define this in the Core or ORM? Note
> that this is different from the horizontal/vertical sharding supported in
> SQLAlchemy
> > <
> http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html?highlight=partition#partitioning-strategies>.
>
>
> >
> > Checking the docs
> > <
> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=prefixes#sqlalchemy.schema.Table>
>
> for `Table`, I did not see any "postfixes" or similar.
> >
> > Likewise in the Oracle Dialect page
> > <http://docs.sqlalchemy.org/en/latest/dialects/oracle.html>, it didn't
> mention table partitions.
> >
> > I took a look at the Customizing DDL <
> http://docs.sqlalchemy.org/en/latest/core/ddl.html> page, but it appears
> that this only allows you to ALTER TABLE after it has already been created
> - but in Oracle it is not possible to alter a table to be partitioned.
> >
> > Thanks and best regards,
> >
> > Matthew
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Ve
[sqlalchemy] Oracle - How to get VARCHAR2 columns to use BYTE and not CHAR?
(Cross posting from Stackoverflow) It looks like SQLAlchemy defaults to creating VARCHAR2 columns as CHAR. How can I have it create with BYTE instead? from sqlalchemy import MetaData, Column, String from sqlalchemy.ext.declarative import declarative_base metadata = MetaData() Base = declarative_base(metadata=metadata) class Foo(Base): __tablename__ = 'foo' name = Column(String(10), primary_key=True) Foo.__table__.create(bind=engine) This creates the following table: CREATE TABLE XXMD.FOO ( NAME VARCHAR2(10 CHAR) NOT NULL ) Instead, I would like it to create the following: CREATE TABLE XXMD.FOO ( NAME VARCHAR2(10 BYTE) NOT NULL ) I would prefer to use CHAR, but I'm integrating with a few systems whose table's are BYTE and need my corresponding tables to match theirs. We also have some tables/code that are reliant on Oracle's data dictionary which use BYTE instead of CHAR. I've tried using sqlalchemy.dialects.oracle.VARCHAR2, but it also defaults to CHAR. Best regards, Matthew Moisen --- I'm using SQLAlchemy 1.1.5, cx_Oracle 5.3, an Oracle 12CR1 Client, and an Oracle 12CR1 DB. -- 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] How to create a Partitioned Oracle Table in SQLAlchemy?
Hello, In Oracle we can create a Partitioned Table like the following: CREATE TABLE sales_hash (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY HASH(s_productid) ( PARTITION p1 TABLESPACE tbs1 , PARTITION p2 TABLESPACE tbs2 , PARTITION p3 TABLESPACE tbs3 , PARTITION p4 TABLESPACE tbs4 ); Is it possible in SQLAlchemy to define this in the Core or ORM? Note that this is different from the horizontal/vertical sharding supported in SQLAlchemy <http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html?highlight=partition#partitioning-strategies>. Checking the docs <http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=prefixes#sqlalchemy.schema.Table> for `Table`, I did not see any "postfixes" or similar. Likewise in the Oracle Dialect page <http://docs.sqlalchemy.org/en/latest/dialects/oracle.html>, it didn't mention table partitions. I took a look at the Customizing DDL <http://docs.sqlalchemy.org/en/latest/core/ddl.html> page, but it appears that this only allows you to ALTER TABLE after it has already been created - but in Oracle it is not possible to alter a table to be partitioned. Thanks and best regards, Matthew -- 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] self-referential many-to-many with single attribute
Hi Mike,
Thanks for the pointers. I take your point about directionality, but it
feels like this is a special case that intuitively should work the same way
for a single model that it does for two. However for now, it does what it
does.
I took at look at using a union @property, and while it does work to return
both sides of the relationship, it does not, as you say support querying. I
took a look at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper,
and I"ll be honest, I didn't understand most of it. Also
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#building-query-enabled-properties
seems to suggest that only predefined queries would be possible?
I'm wondering if instead I should use one-to-many relationships, and use an
event to create the opposing relationship when a relationship is added?
Would appreciate your thoughts.
Thanks!
Matt.
On Tuesday, 21 February 2017 23:25:27 UTC, Mike Bayer wrote:
>
> you want "Node.connected" to be the set of all nodes connected in either
> direction.The problem is that relationally, all graphs are
> "directed", so we have the "left", "right" aspect of things.
>
> The easiest way to get ".connected" as the union of both sets (hint!) is
> to just union them in Python. Give Node a "left_nodes" and
> "right_nodes" relationship (example: see
>
> http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html)
>
>
> then make a regular Python @property that returns
> "self.left_nodes.union(self.right_nodes)", easy enough.
>
> If you want to get into querying this relationally, then you need to do
> the UNION on the SQL side and you'd need to get into creating a mapping
> against a UNION and then building a relationship to it. This is
> significantly more involved and would be using some of the techniques at
>
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper.
>
>
>
> But, a @property that does a Python union of two relationships, quick
> and easy.
>
>
>
> On 02/21/2017 04:43 PM, Matthew Brookes wrote:
> >
> > I apologize if this is well trodden ground, but having googled, and
> > stack-overflowed, read the docs, and searched this list, where lots of
> > people have asked the same (or similar) questions, I couldn't find a
> > concrete answer...
> >
> > I'm trying to set up a Model such that an entry can be connected to one
> > or more other entries, and that the reverse relationship can be found
> > from the same attribute.
> >
> > Effectively I'm trying to do this:
> >
> > ```
> >
> > from sqlalchemy import Integer, ForeignKey, String, Column, Table
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import relationship
> >
> > Base = declarative_base()
> >
> > node_to_node = Table("node_to_node", Base.metadata,
> > Column("left_node_id", Integer, ForeignKey("node.id"),
> primary_key=True),
> > Column("right_node_id", Integer, ForeignKey("node.id"),
> primary_key=True)
> > )
> >
> > class Node(Base):
> > __tablename__ = 'node'
> > id = Column(Integer, primary_key=True)
> > label = Column(String)
> > connected = relationship("Node",
> > secondary=node_to_node,
> > primaryjoin=id==node_to_node.c.left_node_id,
> > secondaryjoin=id==node_to_node.c.right_node_id,
> > backref="connected"
> > )
> >
> > ```
> >
> > However, that naturally fails (`Error creating backref 'translations' on
> > relationship 'Sentence.translations': property of that name exists`) as
> > there's no magic to figure out that `translations` should be
> > bi-directional.
> >
> > Is there another way to achieve this?
> >
> > Thanks!
> >
> > Matt.
> >
> > --
> > 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 f
[sqlalchemy] self-referential many-to-many with single attribute
I apologize if this is well trodden ground, but having googled, and
stack-overflowed, read the docs, and searched this list, where lots of
people have asked the same (or similar) questions, I couldn't find a
concrete answer...
I'm trying to set up a Model such that an entry can be connected to one or
more other entries, and that the reverse relationship can be found from the
same attribute.
Effectively I'm trying to do this:
```
from sqlalchemy import Integer, ForeignKey, String, Column, Tablefrom
sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import
relationship
Base = declarative_base()
node_to_node = Table("node_to_node", Base.metadata,
Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True))
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
label = Column(String)
connected = relationship("Node",
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref="connected"
)
```
However, that naturally fails (`Error creating backref 'translations' on
relationship 'Sentence.translations': property of that name exists`) as
there's no magic to figure out that `translations` should be
bi-directional.
Is there another way to achieve this?
Thanks!
Matt.
--
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] Composite Column questions and practices
Hi,
I'm learning SQLAlchemy, and a Composite Column seems like a very useful
concept.
However, there are very few mentions of it I can find. So some related
questions after reading some docs and some experimentation.
* 'Hiding'--let's say that for the Vertex example in the docs, I really
want people to use Vertex.start, and not Vertex.x1/y1. Is it recommended to
name them _x1 and _y1, say by using _x1 = Column('x1, Integer) etc?
* Validation: let's say I want 'QuadrantOnePoint', i.e. x and y need to be
positive. Is there some way to use @validates in the composite class (e.g.
Point)? Even better in the constructor somehow, so that Point can also be
used elsewhere, but with the caveat that it's probably bad for the system
to fail to load if data in the DB is somehow invalid.
I see the MutableBase.coerce() note about validation, but I want the
validation used primarily at the constructor (e.g. v1 =
Vertex(start=Point(3, 4), end=Point(12, 15)) should work, but v1 =
Vertex(start=Point(-3, 4), end=Point(12, -15)) should fail, because the two
Point constructors are both for points outside the valid range.
* Queries: if someone does query(Vertex).filter(start == Point(0,0)), that
seems to work (at least using a my own similar class). But not filter(start
>= Point(1,1)). I get "NotImplementedError: ".
Implementing __gt__ on Point does not fix this. Is something else (say
a comparator_factory) required?
Thanks,
Matthew
--
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] Re: Problem installing
Hi So I noticed that the SQLAlchemy installation didn't end up in a path specified by sys.path. I tried: 1. Appending a new path item to this variable to include where SQLAlchemy was installed. This seem to have no positive effect. 2. I moved the files to a path that was already defined by sys.path. This seemed to work. Not sure why I wasn't successful with #1. Thx Matt On Wednesday, November 12, 2014 3:25:56 PM UTC-5, Matthew Albert wrote: Hi, Downloaded SQLAlchemy 0.9.8 and installed on my win7 machine on python 3.4 using: python setup.py install. Installation text seems to indicate a successful install: http://pastebin.com/zcMzMn1e ... but when I attempt to issue: import sqlalchemy I get a ImportError: No module named 'sqlalchemy' Also tried using pip and get error: Cannot fetch index base URL https://pypi.python.org/simple/ Could not find any downloads that satisfy the requirement sqlalchemy Any thoughts? thx Matt -- 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: Problem installing
Thanks for sharing this extra info. I've only bee using python for about a month, I have no idea why the install was executed the way it was. On Wednesday, November 12, 2014 3:25:56 PM UTC-5, Matthew Albert wrote: Hi, Downloaded SQLAlchemy 0.9.8 and installed on my win7 machine on python 3.4 using: python setup.py install. Installation text seems to indicate a successful install: http://pastebin.com/zcMzMn1e ... but when I attempt to issue: import sqlalchemy I get a ImportError: No module named 'sqlalchemy' Also tried using pip and get error: Cannot fetch index base URL https://pypi.python.org/simple/ Could not find any downloads that satisfy the requirement sqlalchemy Any thoughts? thx Matt -- 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] Microsoft SQL connection with SQLAlchemy and pyodbc
Hi,
I'm trying to make a connection to a 2012 MS SQL database using python 3.4
and SQLAlchemy/pyodbc.
I don't have pyodbc, but noticed that the install of SQLAlchemy included
it. I've copied my code below to select the first value from the table
'Mezzanines'. Please note the connection string:
'mssql+pyodbc://TheServer//TheDB'
Attempting to connection using windows authentication.
Traceback error shown below. No module named 'pyodbc'. Is this because
create_engine is trying to find module pyodbc in the sys.path? Do I need
to put pyodbc in the sys.path?
thx
Matt
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy_declarative import Address, Base, Person
from sqlalchemy.connectors import pyodbc
engine = create_engine('mssql+pyodbc://TheServer//TheDB')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
# Write the query
mezz = session.query('Mezzanines').first()
print(mezz.name)
__
Traceback (most recent call last):
File C:/Python34/Programs/SQLAlchemy Tutorial/sqlalchemy_insert.py,
line 7, in module
engine = create_engine('mssql+pyodbc://gtasfdm')
File C:\Python34\lib\site-packages\sqlalchemy\engine\__init__.py, line
362, in create_engine
return strategy.create(*args, **kwargs)
File C:\Python34\lib\site-packages\sqlalchemy\engine\strategies.py,
line 74, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File C:\Python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py,
line 51, in dbapi
return __import__('pyodbc')
ImportError: No module named 'pyodbc'
--
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: Microsoft SQL connection with SQLAlchemy and pyodbc
Thx Mike,
I've been here before. I'm using windows 64bit and python 3.4. I only
find pyodbc for =v3.3 here. Errors because of lacking python 3.3 registry
entry.
Somebody on stackoverflow suggested this site for 3.4.
http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc
I downloaded and ran the binary install from this site, seemed to go ok,
although python still can't seem to find the module. The installation
wizard correctly identifies the Python34 path and suggests to install at
\Python34\Lib\site-packages which all seems reasonable. The actually
install process completes almost instantaneously which seems a little
suspicious. The closest thing to pyodbc I see in ..\site-packages is a
folder named:
\pyodbc-3.0.7-py3.4.egg-info
Any thoughts on what is going on?
thx
Matt
On Thursday, November 13, 2014 5:10:06 PM UTC-5, Matthew Albert wrote:
Hi,
I'm trying to make a connection to a 2012 MS SQL database using python 3.4
and SQLAlchemy/pyodbc.
I don't have pyodbc, but noticed that the install of SQLAlchemy included
it. I've copied my code below to select the first value from the table
'Mezzanines'. Please note the connection string:
'mssql+pyodbc://TheServer//TheDB'
Attempting to connection using windows authentication.
Traceback error shown below. No module named 'pyodbc'. Is this because
create_engine is trying to find module pyodbc in the sys.path? Do I need
to put pyodbc in the sys.path?
thx
Matt
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy_declarative import Address, Base, Person
from sqlalchemy.connectors import pyodbc
engine = create_engine('mssql+pyodbc://TheServer//TheDB')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
# Write the query
mezz = session.query('Mezzanines').first()
print(mezz.name)
__
Traceback (most recent call last):
File C:/Python34/Programs/SQLAlchemy Tutorial/sqlalchemy_insert.py,
line 7, in module
engine = create_engine('mssql+pyodbc://gtasfdm')
File C:\Python34\lib\site-packages\sqlalchemy\engine\__init__.py, line
362, in create_engine
return strategy.create(*args, **kwargs)
File C:\Python34\lib\site-packages\sqlalchemy\engine\strategies.py,
line 74, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File C:\Python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py,
line 51, in dbapi
return __import__('pyodbc')
ImportError: No module named 'pyodbc'
--
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] Problem installing
Hi, Downloaded SQLAlchemy 0.9.8 and installed on my win7 machine on python 3.4 using: python setup.py install. Installation text seems to indicate a successful install: http://pastebin.com/zcMzMn1e ... but when I attempt to issue: import sqlalchemy I get a ImportError: No module named 'sqlalchemy' Also tried using pip and get error: Cannot fetch index base URL https://pypi.python.org/simple/ Could not find any downloads that satisfy the requirement sqlalchemy Any thoughts? thx Matt -- 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: Problem installing
sys.path ['C:\\Python34\\lib\\site-packages\\pygame\\tests', 'C:\\Python34\\lib\\site-packages\\pygame', 'C:/Python34/Programs/SQLAlchemy Tutorial', 'C:\\Python34\\Lib\\idlelib', 'C:\\Python34\\lib\\site-packages\\pypyodbc-1.3.3-py3.4.egg', 'C:\\Python34\\lib\\site-packages\\pydaqmx-1.2.5.2-py3.4.egg', 'C:\\Windows\\system32\\python34.zip', 'C:\\Python34\\DLLs', 'C:\\Python34\\lib', 'C:\\Python34', 'C:\\Python34\\lib\\site-packages', 'C:\\Python34\\lib\\site-packages\\pygame'] On Wednesday, November 12, 2014 3:25:56 PM UTC-5, Matthew Albert wrote: Hi, Downloaded SQLAlchemy 0.9.8 and installed on my win7 machine on python 3.4 using: python setup.py install. Installation text seems to indicate a successful install: http://pastebin.com/zcMzMn1e ... but when I attempt to issue: import sqlalchemy I get a ImportError: No module named 'sqlalchemy' Also tried using pip and get error: Cannot fetch index base URL https://pypi.python.org/simple/ Could not find any downloads that satisfy the requirement sqlalchemy Any thoughts? thx Matt -- 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] Database agnostic datetime functions
What is the status of database agnostic datetime access functions like day of month or day or year? Are these implemented somewhere? If not are they planned? If not are they in scope? I'm specifically looking for a database-agnostic solution, not a solution for a particular database. Best, -Matthew -- 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] Rearrange columns in SQLAlchemy core Select object
Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com javascript: wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com javascript: wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com javascript: wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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 a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, 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+...@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.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
My current solution is to rely on `replace_selectable` but it's not particularly robust. On Mon, Sep 22, 2014 at 1:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: there’s no magic on that one, you’d need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange- columns-in-sqlalchemy-select-object Best, -Matthew -- 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 a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/ topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, 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+...@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. -- 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/OxowS9BhAKE/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] Rearrange columns in SQLAlchemy core Select object
Hrm, that's an interesting thought. Any interest in a real-time conversation? This work is for Blaze http://blaze.pydata.org/docs/latest/index.html btw. I'm lowering a relational algebra abstraction to a variety of other systems (pandas, spark, python, sqlalchemy, etc...) On Mon, Sep 22, 2014 at 1:35 PM, Michael Bayer mike...@zzzcomputing.com wrote: this is probably already apparent but if I had this sort of problem, I’d more be asking why do I have this problem in the first place, that is, my program has made these two SELECT objects that need to be combined, they instead should be making two “data criteria” objects of some kind that can be constructed into a statement later. that is, you need more abstraction here and you need to bind to the SQL expression system later. On Sep 22, 2014, at 1:32 PM, Matthew Rocklin mrock...@gmail.com wrote: My current solution is to rely on `replace_selectable` but it's not particularly robust. On Mon, Sep 22, 2014 at 1:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: there’s no magic on that one, you’d need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange- columns-in-sqlalchemy-select-object Best, -Matthew -- 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 a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/ topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, 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+...@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. -- 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/OxowS9BhAKE/unsubscribe. To unsubscribe from
[sqlalchemy] Rearrange columns in SQLAlchemy core Select object
How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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] Specify Protected keywords in new SQL Dialect
When building a new SQL dialect how can one specify new protected keywords that should be quoted when generating SQL text queries? -- 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: Specify Protected keywords in new SQL Dialect
Alternatively if someone can point me to the appropriate docs on this I'd be much obliged. A cursory view of the docs and Google didn't yield much. On Friday, September 19, 2014 6:32:26 AM UTC-4, Matthew Rocklin wrote: When building a new SQL dialect how can one specify new protected keywords that should be quoted when generating SQL text queries? -- 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] Rearrange columns in SQLAlchemy core Select object
Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mike...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mrock...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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 a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/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] How To Insert Relationship-Object Data Into DB?
On Wednesday, February 5, 2014 10:44:39 AM UTC-5, Michael Bayer wrote: On Feb 5, 2014, at 9:43 AM, Jude Lucien jlu...@gmail.com javascript: wrote: I have a secondary problem now having changed my model to use declarative base - as in db.create_all() does not create my tables in the database. How can I do this using the declarative base method? Base.metadata is where you’d call create_all(my engine) from. @Jude: db.create_all() is part of the Flask-SQLAlchemy API, not SQLAlchemy proper. AFAIK it's ok to mix the two together (the project I'm working on certainly does) but if you're not subclassing db.Model in your models Flask-SQLAlchemy may not be doing you much good. Which is fine, just be aware. -Matt -- 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/groups/opt_out.
[sqlalchemy] AttributeError: 'CompositeProperty' object has no attribute 'props'
Hi SQLAlchemy,
On SQLAlchemy 0.9.2, if I construct a query selecting a composite property
before constructing any other queries, I see this error:
Traceback (most recent call last):
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/nose/case.py,
line 197, in runTest
self.test(*self.arg)
File /media/psf/vagrant/test_configure_mappers.py, line 47, in
test_composite_prop_query
user_login_query = Session.query(User.login)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py,
line 149, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 1151, in query
return self._query_cls(entities, self, **kwargs)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 106, in __init__
self._set_entities(entities)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 114, in _set_entities
entity_wrapper(self, ent)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 3338, in __init__
column = column._query_clause_element()
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py,
line 150, in _query_clause_element
return self.comparator._query_clause_element()
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 407, in _query_clause_element
return CompositeProperty.CompositeBundle(self.prop,
self.__clause_element__())
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 404, in __clause_element__
return expression.ClauseList(group=False, *self._comparable_elements)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 689, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 419, in _comparable_elements
return self.prop._comparable_elements
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 689, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 236, in _comparable_elements
for prop in self.props
AttributeError: 'CompositeProperty' object has no attribute 'props'
I've written a nose test module that exposes this behavior. Note that no
SQL is ever actually issued (AFAIK?).
import logging
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import composite, sessionmaker, configure_mappers,
scoped_session
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:')
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
Base = declarative_base()
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
class Login(object):
def __init__(self, name, password):
self.name = name
self.password = password
def __composite_values__(self):
return self.name, self.password
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
login = composite(Login, name, password)
def __repr__(self):
return User(name='%s', fullname='%s', password='%s') % (
self.name, self.fullname, self.password)
class TestConfigureMappers(object):
def tearDown(self):
Session.remove()
# This fails
def test_composite_prop_query(self):
user_login_query = Session.query(User.login)
# This works
def test_composite_prop_query_configuring_first(self):
# Either of these two lines will suffice
user_query = Session.query(User)
#configure_mappers()
user_login_query = Session.query(User.login)
user_login = user_login_query
Is this expected behavior? I figure that configure_mappers() must be
exposed publicly for a reason, but the docs say querying should be good
enough to invoke this
process:
http://docs.sqlalchemy.org/en/latest/changelog/migration_07.html?highlight=configure_mappers#compile-mappers-renamed-configure-mappers-simplified-configuration-internals
.
Thanks,
Matt
--
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] from_statement, TextAsFrom and stored procedures
Hi SQLAlchemy,
Our data team wants us to use a (SQL Server 2008 R2) stored procedure to
perform our major query, which is all well and good, except it's preventing
SQLAlchemy's type processing from being applied. This is on SQLAlchemy
0.9.1, using pyodbc and FreeTDS.
For example, say we are trying to map this class (using Flask-SQLAlchemy):
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
random_time = db.Column(UTCDateTime)
def __init__(self, username, email):
self.username = username
self.email = email
self.random_time = datetime.now()
Using this (trivial) user-defined type:
class UTCDateTime(db.TypeDecorator):
impl = db.DateTime
def process_result_value(self, value, dialect):
print AWOGA
return value
Create the table and populate it with some values:
db.create_all()
db.session.add(User('alice', 'al...@gmail.com'))
db.session.add(User('bob', 'b...@gmail.com'))
db.session.commit()
users = db.session.query(User).all()
Two AWOOGAs are output, as expected.
Then, create a stored procedure like this:
CREATE PROCEDURE GetUser AS
SELECT
*
FROM user
GO
And query into User objects using the procedure:
db.session.add(User('charlie', 'char...@gmail.com'))
db.session.commit()
text = db.text('exec getuser')
users = db.session.query(User).from_statement(text).all()
The resulting User objects look reasonable, *but no AWGAs*, and the
strings are all bytestrings.
After looking at the docs more closely, this isn't very surprising: text()
does warn about a lack of type processing, and suggests using
text().columns() to provide a mapping (in lieu of the now-deprecated
typemap kwarg to text()). This creates a TextAsFrom object, which adds some
extra superpowers to text() including a .c attribute. Problem is,
from_statement() doesn't like it:
db.session.commit()
typemap = {'id': db.Integer, 'username': db.String, 'email': db.String,
'random_time': UTCDateTime}
taf = text.columns(**typemap)
users = db.session.query(User).from_statement(taf).all()
This results in a stack trace:
AttributeErrorTraceback (most recent call last)
ipython-input-20-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __iter__(self)
2386
2387 def __iter__(self):
- 2388 context = self._compile_context()
2389 context.statement.use_labels = True
2390 if self._autoflush and not self._populate_existing:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in _compile_context(self, labels)
2732
2733 def _compile_context(self, labels=True):
- 2734 context = QueryContext(self)
2735
2736 if context.statement is not None:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __init__(self, query)
3478 if query._statement is not None:
3479 if isinstance(query._statement, expression.SelectBase)
and \
- 3480 not query._statement.use_labels:
3481 self.statement = query._statement.apply_labels()
3482 else:
AttributeError: 'TextAsFrom' object has no attribute 'use_labels'
Looks like TextAsFrom isn't quite select-like enough for from_statement().
I tried tacking on a taf.use_labels = True before running the query, but
just got another error:
NoSuchColumnError Traceback (most recent call last)
ipython-input-23-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in instances(query, cursor, context)
70 process[0](row, rows)
71 elif single_entity:
--- 72 rows = [process[0](row, None) for row in fetch]
73 else:
74 rows = [util.KeyedTuple([proc(row, None) for proc in
process],
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in _instance(row, result)
358 identitykey = (
359 identity_class,
-- 360 tuple([row[column]
Re: [sqlalchemy] No operator matches the given name and argument filtering on postgres array
On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote:
that’s a little strange but you can get around it using CAST:
match = session.query(MyTable).\
filter(MyTable.myset == cast(z, ARRAY(String))).\
all()
Unfortunately, that doesn't work.
sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type record
to character varying[]
LINE 3: WHERE mytable.myset = CAST((E'a', E'b', E'c') AS VARCHAR[])
^
'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM
mytable \nWHERE mytable.myset = CAST(%(param_1)s AS VARCHAR[])' {'param_1':
('a', 'b', 'c')}
of course if you declare you column like this:
myset = Column(ARRAY(Text))
That doesn't change the original error.. it still complains:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not
exist: character varying[] = text[]
LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM
mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}
So, I'm still looking for a solution..
Thanks for the suggestions.
--
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/groups/opt_out.
Re: [sqlalchemy] No operator matches the given name and argument filtering on postgres array
On Tuesday, 10 December 2013 03:29:48 UTC-5, Matthew Pounsett wrote: On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote: that’s a little strange but you can get around it using CAST: match = session.query(MyTable).\ filter(MyTable.myset == cast(z, ARRAY(String))).\ all() Unfortunately, that doesn't work. In case this is a version issue, I should note I'm using 0.7.8, which is only a little bit behind what is currently shipping for FreeBSD (0.7.10). Running under Python 2.7.3 -- 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/groups/opt_out.
Re: [sqlalchemy] No operator matches the given name and argument filtering on postgres array
On Tuesday, 10 December 2013 10:00:20 UTC-5, Michael Bayer wrote: the test case I’m using is below. You might want to make sure you’re on the latest psycopg2, this is also SQLAlchemy 0.8.4 but the SQL output seems the same. Overall, if maybe you’re on an older postgresql version, you need to log in with psql, figure out what SQL query works directly, then just render that with whatever casts are needed. Found the problem! I think I forgot to drop the table at some point during my tests. Your example below works, and if I go back to mine and make it match it now works too. I should be able to apply this to the actual object I was having trouble with, now. Thanks for the help! from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.postgresql import ARRAY, array Base = declarative_base() class MyTable(Base): __tablename__ = 'mytable' id = Column(Integer, primary_key=True) myset = Column(ARRAY(String)) engine = create_engine(postgresql://scott:tiger@localhost/test, echo=True) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) session = Session(engine) z = ['a', 'b', 'c'] match = session.query(MyTable).\ filter(MyTable.myset == cast(z, ARRAY(String))).\ all() -- 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/groups/opt_out.
[sqlalchemy] No operator matches the given name and argument filtering on postgres array
I'm trying to work with the postgres ARRAY type and I'm having a hard time
figuring out what I'm doing wrong with filtering queries on the array
column. Here's some sample code, omitting the session setup:
class MyTable(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True)
myset = Column(sqlalchemy.dialects.postgresql.ARRAY(String))
Base.metadata.create_all(engine)
z = ['a', 'b', 'c']
match = session.query(MyTable).\
filter(MyTable.myset == z).\
all()
Note that the table is completely empty at this point, and has just been
created by the create_all() method. I expect to get back an empty 'match'
list, but when the query runs I get a long stack trace and the following
sql error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not
exist: character varying[] = text[]
LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM
mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}
On the off chance it mattered, I've tried setting z to be a tuple instead
of a list, but no joy there either. What am I misunderstanding about how
the array type works?
Thanks!
--
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/groups/opt_out.
Re: [sqlalchemy] hybrid_property returning a python type
On Thursday, 25 July 2013 11:39:23 UTC-4, Michael Bayer wrote:
But your backend isn't doing this; if you were using Postgresql for
example, it should be returning a timedelta() already. So perhaps this is
MySQL. you'd need to make a TypeDecorator that receives this integer and
does what you want with it. You'd emulate the Epoch decorator currently
illustrated at
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.TypeDecorator:
Yeah, sorry for forgetting to mention the back end .. I'm using sqlite, so
my datetime objects are being written as strings. That's why I'm using
strftime('%s', ...) to convert them to epoch seconds before doing
arithmetic with them.
I think I understand the mechanism here... except that since this isn't a
real type (there's no data store behind this) is process_bind_param()
useful at all?
For process_result_value() I think I want something like:
def process_result_value(self, value, dialect):
return datetime.timedelta(seconds=value)
... since it's seconds-since-epoch that I'm doing arithmetic with.
Thanks for the pointers! Off to mess around with this and see what I get..
--
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/groups/opt_out.
Re: [sqlalchemy] hybrid_property returning a python type
On Monday, 29 July 2013 20:48:57 UTC-4, Matthew Pounsett wrote:
I think I understand the mechanism here... except that since this isn't a
real type (there's no data store behind this) is process_bind_param()
useful at all?
To answer my own question... yes, of course it does because it needs to
convert to the database (or hybrid property) type in order to run the query
in the first place.
I wound up with this, which works perfectly:
class TimedeltaType(types.TypeDecorator):
impl = types.Integer
def process_bind_param(self, value, dialect):
return value.total_seconds()
def process_result_value(self, value, dialect):
return timedelta(seconds=value)
And later...
@hybrid_property
def duration(self):
if self.finish:
return self.finish - self.start
else:
return timedelta(0)
@duration.expression
def duration(cls):
return type_coerce(
func.strftime('%s', func.coalesce(cls.finish, cls.start)) -
func.strftime('%s', cls.start),
TimedeltaType
)
Thanks again for the help!
--
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/groups/opt_out.
[sqlalchemy] hybrid_property returning a python type
I have a class with 'start' and 'finish' attributes which are DateTime
columns. I'm trying to create a hybrid property 'duration' which returns
the delta as a datetime.timedelta object. This is working fine for the
instance attribute, but I can't seem to get it to work for the class
expression.
This is close, and works, except that the clas expression returns an
integer:
@hybrid_property
def duration(self):
if self.finish:
return self.finish - self.start
else:
return timedelta(0)
@duration.expression
def duration(cls):
return func.strftime('%s', func.coalesce(cls.finish, cls.start)) -\
func.strftime('%s', cls.start)
As soon as I try to wrap that to convert it to the python object, I get an
exception:
@duration.expression
def duration(cls):
return timedelta(
func.strftime('%s', func.coalesce(cls.finish, cls.start)) -
func.strftime('%s', cls.start)
)
TypeError: unsupported type for timedelta days component: _BinaryExpression
Is there something I need to do to convert the return value from func() in
order to be able to work with it? Or, is what I'm attempting even possible?
Thanks!
--
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/groups/opt_out.
[sqlalchemy] filtering by variable attribute names
Does the ORM allow for filtering by a variable attribute name? I found
this discussion using raw SQL:
https://groups.google.com/d/topic/sqlalchemy/Axa-0thwOR8/discussion
But the suggestion doesn't seem to apply in my case. Some sample code to
demonstrate what I'm trying based on the above (I've left out the method
declarations in the class for brevity):
class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key=True)
one = Column(String)
two = Column(String)
x = Foo(bar, baz)
y = Foo(bim, bam)
z = Foo(baz, bar)
session.add(x)
session.add(y)
session.add(z)
for attr in ('one', 'two'):
print attr
print session.query(Foo).\
filter(Foo[attr]=='bar').all()
I want to see the objects x and z printed out in separate lists at the end.
I assume there must be an approach for this, but I haven't been able to
find it.
Does anyone know what I'm missing?
Thanks!
--
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/groups/opt_out.
Re: [sqlalchemy] filtering by variable attribute names
On Tuesday, 23 July 2013 04:16:24 UTC-4, Simon King wrote: You can use the standard python getattr function for this: Ah, of course. That should have occurred to me to try. Thanks, works like a charm! -- 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/groups/opt_out.
Re: [sqlalchemy] Question PickledType usage
Hi Zoltan,
On Tue, Mar 12, 2013 at 9:56 AM, Zoltan Giber zgi...@gmail.com wrote:
I'm new to sqlalchemy, writing my first app using it. I stumbled upon a weird
thing; my user object has a pyckletype
representing a python dict, which i can't find a way to update. I assumed,
that a change in the pickled object will
somehow trigger dirty and my new data should be there, but it is not. My
goal would be to create an user, and a
notebook for the user, then add the notebook's ID to the
user.views['lastopened']
Looking at the code it will be clear i hope (i'm adding some comments here):
newuser = User(email,name,password)
# creating the new user
newuser.notebooks.append(Notebook(My Notes)) #
the child notebook
newuser.views = {}
session.add(newuser)
session.commit() # couldn't find other way to get the
notebook id, but to make a commit. is there a
better way?
user = session.query(User).filter_by(email=email).one() #
I'm not sure if a new query is needed here,
this is eliminating uncertainty
defaultnb = user.notebooks[0]
user.views['lastview'] = defaultnb.id
session.commit()
session.close()
the problem is, that the user.view['lastview'] stays an empty {} ... if i
update it upon creation, it works, but then the
notebook.id is not known to me.
I've not done this myself, but I was curious so I looked in the
sqlalchemy docs for PickleType [0]. I found a note that sounds
applicable: To allow ORM change events to propagate for elements
associated with PickleType, see Mutation Tracking [1]. I hope this is
helpful.
Thanks,
Matthew
[0]
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.PickleType
[1] http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/mutable.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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Many to many relationship, having count filter
Hello all,
I'm writing a SA-based library to work with an existing MSSQL database
project. There is one query in particular I haven't been able to figure out
how to express in SA: I have a many to many relationship between schools
and coordinators and I want to find the schools that have more than one
coordinator.
From my models.py:
class School(Base):
__tablename__ = 'School'
id = Column('Id', Integer, primary_key=True)
school_name = Column('SchoolName', String)
school_coordinators = Table('SchoolCoordinator', Base.metadata,
Column('SchoolId', Integer, ForeignKey('School.Id')),
Column('CoordinatorId', Integer, ForeignKey('Coordinator.Id'))
)
class Coordinator(Base):
__tablename__ = 'Coordinator'
id = Column('Id', Integer, primary_key=True)
email_address = ('EmailAddress', String)
first_name = Column('FirstName', String)
last_name = Column('LastName', String)
schools = relationship('School',
secondary=school_coordinators, backref='coordinators')
And I want to achieve SQL that basically looks like this (IE, show me the
schools with more than one coordinator):
SELECT s.Id, COUNT(c.Id)
FROM School s
INNER JOIN SchoolCoordinator sc ON s.Id = sc.SchoolId
INNER JOIN Coordinator c ON sc.CoordinatorId = c.Id
GROUP BY s.Id
HAVING COUNT(c.Id) 1
The HAVING example in the query object documentation is for a one-to-many
relationship and seems pretty straightforward. None of the incantations
I've tried have worked for a m2m relationship however. Any advice is much
appreciated.
Thanks,
Matthew
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to many relationship, having count filter
On Monday, February 11, 2013 5:00:50 PM UTC-6, Michael Bayer wrote: should be easy enough to create those joins automatically using the relationship: query(School.id, func.count(Coordinator.id)).join(School.coordinators).group_by(School.id).having(func.count(Coordinator.id) 1) or you could join() explicitly with a separate target/ON clause, shouldn't be necessary though. Thanks for your reply Mike. I tried exactly that but I get: AttributeError: type object 'School' has no attribute 'coordinators' So I went ahead and got rid of the backref from the relationship defined in Coordinator and explicitly added the coordinators relationship on School and now it works. Worth a ticket? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Inverting the like() operation
On Monday, 12 March 2012 01:35:10 UTC-4, Michael Bayer wrote: both the left and right sides of an expression are ultimately represented as SQLAlchemy ColumnElements. When you say somecol == 'somestr', the 'somestr' part is coerced into a literal object as a result of it being pulled into the binary expression (that is, an expression with a left, right and an operator in the middle). You can do this explicitly so that you can call operators from either side: literal(foobar).like(foo.bar) Thanks! That looks like exactly what I need. I don't see any reference to that method in the documentation pdf, and even searching for it specifically it's not very prominent in the google results, so I guess it's no wonder I didn't find it on my own. Now that I know what I'm looking for it's easy to find in pydoc though. I'll be able to give this a test tonight. Thanks again! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/gh4ytYd_cNMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Inverting the like() operation
I'm having a tough time figuring out how to manage this, and I don't think I know enough about what the end result might look like to be able to google it successfully. I'm trying to invert the sense of the like() operation in an unusual way. Rather than doing 'not like()' I want to take the usual Class.attribute.like(wildcard_expression) and do wildcard_expression.like(Class.attribute). The implication being that I'm storing wildcard expressions in the database, and passing absolute strings to see which wildcards match. The final SQL would look something like this: dbtest=# create table foo ( bar varchar primary key ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE dbtest=# insert into foo values ( 'foo%' ); INSERT 0 1 dbtest=# select * from foo where 'foobar' like foo.bar; bar -- foo% (1 row) Anyone have any suggestions for how to express that select from SQLAlchemy? I'm using SQLAlchemy version 0.7.3 with postgreSQL 8.4.8 under python 2.7.2. Thanks for any suggestions.. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sqlite and database is locked errors.
Michael,
Thanks! Using create_engine(..., poolclass=SingletonThreadPool) works
perfectly, though create_engine(..., pool_threadlocal=True) still showed
similar database is locked on 'session commit'
FWIW, I do have a single engine, connection, and session in the
application, as with (now):
self.engine = create_engine('sqlite:///%s' % dbname,
poolclass=SingletonThreadPool)
self.conn = self.engine.connect()
self.session = sessionmaker(bind=self.engine)()
self.metadata = MetaData(self.engine)
self.metadata.reflect()
I haven't been rigorous in checking that only one wxPython thread is using
the connection, as the wx code has lots of wx.CallAfter() to allow
callbacks from the networking library to call into wx code, which then
might want to write into the database. My suspicion is that this is the
root cause of the issue.
Anyway, I very much appreciate the quick, helpful response and fantastic
library.
--Matt
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/20EJ6t8trqsJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] sqlite and database is locked errors.
First, I apologize in advance for the vague question and thank you in advance for the great SQLAlchemy library, and any help you might be able to give. I have a GUI application (wxPython) that uses SQLAlchemy and sqlite3 to store state information. The application connects to network resources, using a wrapped C library that internally makes heavy use of threads. This application worked (and still works) fine with sqlalchemy 0.6.8, but gives database is locked errors on session.commit() with 0.7.2 and 0.7.3. I get the same behavior on both linux (python 2.6) and windows (python 2.6 and 2.7). This app definitely needs to communicate with the network resources, and the interaction with that library needs to be wrapped with wx.CallAfter() in order to isolate network communication from the GUI threads. I've tried to be careful about separating this from calls to sqlalchemy/sqlite, and am not finding any obvious errors. Are there any ideas about what changed between 0.6.8 and 0.7.2 that might trigger this change? Are there any general suggestions on how to resolve this? It seems the previous questions about database is locked are answered with don't use sqlite. This application really needs a single, no-server datastore, so that would mean either staying with 0.6.8 indefinitely or not using SQLAlchemy, neither of which seems like a good choice to me. Again, thanks for any insight. --Matt -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/T3WBK-ZU6fIJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
On Mar 26, 2010, at 3:20 AM, Chris Withers wrote: Matthew Williams wrote: From previous posts to this and other lists, it seems that ORMs and threads don't get along too well... What makes you think that? First of all, most of my impressions about ORMs come from SQLAlchemy. This quote from this list (http://twistedmatrix.com/pipermail/twisted-python/2009-March/019359.html ) sums up what I have found as well: It's much trickier if you want to use the ORM, unless you are very careful to fully eager load every thing in any possible database operation if you have need of the information subsequently in your twisted code. Otherwise you may block unexpectedly simply when accessing your objects, and end up with database operations from the main twisted thread. So perhaps I should have said SQL Alchemy's ORM and threads don't get along too well... that's not to say it's impossible, you just have to be exceedingly careful how you use it. and, as far as I can tell, there's no way to get away from threads if you don't want longish queries to block your entire application. Right, SQLAlchemy doesn't play nicely with *non-threaded* environments, from my understanding, which may well be wrong ;-) It took me quite some time to piece together everything I could find related to sAsync (which seems to be a dead but functional project), so I threw up a quick Trac page for it at http://sasync.org . Cool. What is it you're doing that needs to mix Twisted and SQLAlchemy? The project (an internal project) doesn't really *need* to mix them... I could just use mysqldb. Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
On Mar 26, 2010, at 7:16 AM, Chris Withers wrote: Cool. What is it you're doing that needs to mix Twisted and SQLAlchemy? The project (an internal project) doesn't really *need* to mix them... I could just use mysqldb. Heh, wrong end of the stick again; my question was why you needed to use Twisted ;-) I'm using txjsonrpc to build a JSON-RPC server. If you have suggestions for alternatives to txjsonrpc, I'm certainly open to them, as this project is just getting started. Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] sAsync
Is anyone here familiar with sAsync, developed some years ago by Ed Suominen? It used to be hosted at http://foss.eepatents.com/sAsync/, but that site is no longer active. If no one is actively maintaining this project, would there be any objections to me resurrecting the project? On first tests, it seems to work fine with SA 0.6 with only a tweak to get it to recognize the version. Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sAsync
In case anyone is interested, I've thrown up the code and what I could find in the way of docs at http://sasync.org/. sAsync is a package that wraps SQLAlchemy to provide asynchronous database access using SA's SQL Builder. It was originally announced to the Twisted mailing list in 2006 (http://twistedmatrix.com/pipermail/ twisted-python/2006-May/013121.html) and was actively maintained by Ed Suominen (is he still around?) until March of 2008. I hope someone will find this package useful. Note that it is released under GPL. Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: @comparable_using
I got around this by switching to declarative and declaring my property like this: _id = column_property(Column(id,Integer,primary_key=True), comparator_factory=some_comparator_factory()) And then used @synonym_for for the getter: @synonym_for(_id) @property def id(self): return some_function(self._id) I'm still not sure what was wrong with my original code, so would be interested in any comments, for academic reasons. Thank you! Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] @comparable_using
Hi, I've recently started using SQLAlchemy and really like it... but I'm having some difficulty getting the decorator @comparable_using to work (v 0.6b2). I have a class that looks something like this: class MyClass(object): @comparable_using(my_comparator_maker(some_instance_variable,another_instance_variable)) @property def some_property(self): return fake response Where my_comparator_maker() returns a ColumnProperty.Comparator subclass. MyClass().some_property is not giving me fake response, though... it is giving me a ComparableProperty object. Also, if I try to actually filter on some_property, I receive the error filter() argument must be of type sqlalchemy.sql.ClauseElement or string. Any help would be greatly appreciated. By the way, I am basing my code off the example given in http://groups.google.com/group/sqlalchemy/msg/a7a76f23df3238c2 from last fall. Has something changed that is preventing this from working? Thanks! Matthew -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] TypeError when appending to Associationproxy
Hello,
Just getting started with association proxy and having trouble making
it work. The lookups seem to work fine but when I try to add a new
element to the association, I get TypeError: __init__() takes exactly
1 argument (2 given). Code test case below, I've left a bunch of
columns out for brevity:
from sqlalchemy import Column, Integer, String, DateTime, Boolean,
ForeignKey, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Org(Base):
__tablename__ = 'tOrg'
id = Column(Integer, primary_key=True, name='OrgID')
name = Column(String(100), name='OrgName')
def __repr__(self):
return Org(%s: '%s') % (self.id, self.name,)
class News(Base):
__tablename__ = 'tNews'
id = Column(Integer, primary_key=True, name='NewsID')
title = Column(String(255), name='NewsTitle')
body = Column(Text, name='NewsBody')
author = Column(String(255), name='NewsAuthor')
is_active = Column(Boolean, name='NewsActive')
date = Column(Date, name='NewsDate')
priority = Column(Integer, name='NewsPriority')
orgs = association_proxy('newsorgs', 'org')
def __repr__(self):
return News(%s: '%s') % (self.id, self.title,)
class NewsOrg(Base):
__tablename__ = 'trefNewsOrg'
id = Column(Integer, primary_key=True, name='NewsOrgID')
news_id = Column(Integer, ForeignKey(News.id), name='NewsID')
news = relation('News', backref=backref('newsorgs'))
org_id = Column(Integer, ForeignKey(Org.id), name='OrgID')
org = relation(Org)
def __repr__(self):
if self.org:
orgname = self.org.name
else:
orgname = 'ALL'
return NewsOrg(%s: '%s', (%s)) % (self.id,
self.news.title, orgname,)
def testcase(session):
myorg = session.query(Org).filter(Org.id==6).one()
otherorg_news_associations = session.query(NewsOrg).filter
(NewsOrg.org_id==1).all()
mystory = otherorg_news_associations[0].news
mystory.orgs.append(myorg) # -- TypeError: __init__() takes
exactly 1 argument (2 given)
session.commit()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: TypeError when appending to Associationproxy
Excellent, thank you. A few minutes after I posted I was able to
figure out the NewsOrg.__init__ method fix, but the creator lambda
argument to association_proxy is much cleaner. Thanks again!
On Nov 13, 2:45 pm, Conor conor.edward.da...@gmail.com wrote:
Matthew R wrote:
Hello,
Just getting started with association proxy and having trouble making
it work. The lookups seem to work fine but when I try to add a new
element to the association, I get TypeError: __init__() takes exactly
1 argument (2 given). Code test case below, I've left a bunch of
columns out for brevity:
from sqlalchemy import Column, Integer, String, DateTime, Boolean,
ForeignKey, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Org(Base):
__tablename__ = 'tOrg'
id = Column(Integer, primary_key=True, name='OrgID')
name = Column(String(100), name='OrgName')
def __repr__(self):
return Org(%s: '%s') % (self.id, self.name,)
class News(Base):
__tablename__ = 'tNews'
id = Column(Integer, primary_key=True, name='NewsID')
title = Column(String(255), name='NewsTitle')
body = Column(Text, name='NewsBody')
author = Column(String(255), name='NewsAuthor')
is_active = Column(Boolean, name='NewsActive')
date = Column(Date, name='NewsDate')
priority = Column(Integer, name='NewsPriority')
orgs = association_proxy('newsorgs', 'org')
def __repr__(self):
return News(%s: '%s') % (self.id, self.title,)
class NewsOrg(Base):
__tablename__ = 'trefNewsOrg'
id = Column(Integer, primary_key=True, name='NewsOrgID')
news_id = Column(Integer, ForeignKey(News.id), name='NewsID')
news = relation('News', backref=backref('newsorgs'))
org_id = Column(Integer, ForeignKey(Org.id), name='OrgID')
org = relation(Org)
def __repr__(self):
if self.org:
orgname = self.org.name
else:
orgname = 'ALL'
return NewsOrg(%s: '%s', (%s)) % (self.id,
self.news.title, orgname,)
def testcase(session):
myorg = session.query(Org).filter(Org.id==6).one()
otherorg_news_associations = session.query(NewsOrg).filter
(NewsOrg.org_id==1).all()
mystory = otherorg_news_associations[0].news
mystory.orgs.append(myorg) # -- TypeError: __init__() takes
exactly 1 argument (2 given)
session.commit()
The association_proxy is trying to create the NewsOrg object by calling
NewsOrg(myorg). Since your NewsOrg class does not override __init__, the
default declarative __init__ is used, which takes only keyword
arguments. This causes the TypeError: __init__() takes exactly 1
argument (2 given) error.
You can fix this by either adding an __init__ method to NewsOrg like this:
def __init__(self, org=None, **kwargs):
super(NewsOrg, self).__init__(**kwargs)
self.org = org
or, preferably, add a creator argument to association_proxy:
orgs = association_proxy('newsorgs', 'org', creator=lambda org:
NewsOrg(org=org))
You can find out more about the 'creator' argument
at:http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html...
-Conor
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: returning values as a list of values rather than as list of tuples
Hi Faheem,
On Thu, Jan 15, 2009 at 11:05 AM, Faheem Mitha fah...@email.unc.edu wrote:
Hi,
The following code returns a list of tuples to python from the db,
corresponding to the values of the 'snpval_id' column in the table 'cell'.
I was wondering if there was an easy way to have it return a list of
values (in this case, integers) instead.
result = conn.execute(select snpval_id from cell where patient_chipid IN
('Duke1_plateC_F11.CEL')).fetchall()
*
Easiest thing is probably just to use a list comprehension:
result_ints = [row[0] for row in result]
Cheers,
MZ
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleted rowcount doesn't match number of objects deleted.
I've gotten this before with incorrectly configured backrefs on my
mappers, so that might be another thing to look at.
On Mon, Sep 8, 2008 at 5:18 AM, Harish K Vishwanath
[EMAIL PROTECTED] wrote:
Hello,
I am getting the below exception :
Traceback (most recent call last):
File d:\recogsys\src\python\RSITerm\SQLConvert\SqlDB.py, line 134, in
sqlCommit
self.session.commit()
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\session.py,
line 668, in commit
self.transaction.commit()
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\session.py,
line 374, in commit
self._prepare_impl()
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\session.py,
line 358, in _prepare_impl
self.session.flush()
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\session.py,
line 1358, in flush
self._flush(objects)
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\session.py,
line 1426, in _flush
flush_context.execute()
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\unitofwork.py,
line 261, in execute
UOWExecutor().execute(self, tasks)
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\unitofwork.py,
line 758, in execute
self.execute_delete_steps(trans, task)
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\unitofwork.py,
line 777, in execute_delete_steps
self.delete_objects(trans, task)
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\unitofwork.py,
line 764, in delete_objects
task.mapper._delete_obj(task.polymorphic_todelete_objects, trans)
File
c:\python24\lib\site-packages\SQLAlchemy-0.5.0beta4dev_r5060-py2.4.egg\sqlalchemy\orm\mapper.py,
line 1349, in _delete_obj
raise exc.ConcurrentModificationError(Deleted rowcount %d does not
match number of objects deleted %d % (c.rowcount, len(del_objects)))
ConcurrentModificationError: Deleted rowcount 0 does not match number of
objects deleted 1
when trying to delete a UserObject which is in the DB. When is this
exception raised? Is there anything wrong...
the function where I get this exception is below :
def DropUserDB(self,progressFunct = None, args = (), sleepTime = .2):
userList =
self.session.query(RSITerm.SQLConvert.SqlUser.SqlUser).all()
if userList == []:
msg = No Users in Users DB!
self.LogInfo(msg)
return
total = len(userList)
counter = 0
self.LogInfo('SqlDB.DropUserDB(): Dropping User Database...')
for user in userList:
try:
self.session.delete(user)
if not self.sqlCommit():
msg = SqlDB.DropUserDB : Error in Commit!
self.LogInfo(msg)
return
except:
msg = SqlDB.DropUserDB : Error deleting user!
self.LogInfo(msg)
printException(msg,False)
return
Thanks !
--
Regards,
Harish
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] no server side cursors in SA 0.4.6?
Using Postgres 8.3.1
Consider the following script. In SA 0.4.3 it works as intended. In SA
0.4.6, it does not. In particular, the time to get the resultset in 0.4.3
is sub-second. The time in 0.4.6 is about 20 seconds. Also, when running
on 0.4.3 the memory consumption of the script is constant under 10MB. When
running on 0.4.6, it grows to hundreds of MB and is dependent on the size of
the result set. Seems to me that 0.4.3 used a cursor like it was configured
to and 0.4.6 ignored the server_side_cursors=True parameter to the
create_engine call. How do I make 0.4.6 use server side cursors?
#!/usr/bin/python
from time import time
from sqlalchemy import text
from sqlalchemy import create_engine
stime = time()
engine = create_engine('postgres://[EMAIL PROTECTED]/postgres',
server_side_cursors=True, encoding='utf-8')
conn = engine.connect()
trans = conn.begin()
print have engine, connection, transaction after about %.4f seconds %
(time() - stime)
stime = time()
rs = conn.execute(text(select * from generate_series(1,1000) s0,
generate_series(1,1) s1))
print have resultset after about %.4f seconds % (time() - stime)
count = 0
stime = time()
for r in rs:
count += 1
print counted %s rows after about %.4f seconds % (count, time() - stime)
stime = time()
rs.close()
print closed resultset after about %.4f seconds % (time() - stime)
stime = time()
trans.commit()
print commited after about %.4f seconds % (time() - stime)
stime = time()
conn.close()
print closed connection after about %.4f seconds % (time() - stime)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] transactional sessions not transactional?
The following test case of mine fails on PG 8.3 and SA 0.4.3 Basically,
create two sessions, make some changes in the first and obverse they are
visible before commit/rollback in the second (and via connectionless
execution directly on the engine), but become unvisible after rollback. The
first two print statements both show a row returned (that should only be
visible from s0), but after the rollback the print statements show there are
no values. It's almost like a threadlocal strategy is being used when it
was never configured. Ideas/thoughts/comments?
#!/usr/bin/python
from sqlalchemy.sql import text
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgres://[EMAIL PROTECTED]/testsatransaction')
new_session = sessionmaker(bind=engine, transactional=True)
engine.execute(text(drop table if exists foo))
engine.execute(text(create table foo(c1 int)))
s0 = new_session()
s1 = new_session()
s0.execute(text(insert into foo values(1)))
(one,) = s0.execute(text(select * from foo)).fetchone()
assert one == 1
print engine.execute(text(select * from foo)).fetchone()
print s1.execute(text(select * from foo)).fetchone()
s0.rollback()
print engine.execute(text(select * from foo)).fetchone()
print s1.execute(text(select * from foo)).fetchone()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SQL for (col1, col2) NOT IN (SELECT ...)
Hi all, I'm trying to run a (non-ORM) query of the form: SELECT job_id, pathname FROM jobfilesTable WHERE (job_id, pathname) NOT IN (SELECT job_id, pathname FROM tempTable) After searching the docs and the mailing list, the best I've been able to come up with is something like: from sqlalchemy.sql.expression import _Grouping, ClauseList s = select([jobfilesTable.c.job_id, jobfilesTable.c.pathname]) s2 = select([tempTable.c.job_id, tempTable.c.pathname]) colgroup = _Grouping(ClauseList(jobfilesTable.c.job_id, jobfilesTable.c.pathname)) s = s.where(~colgroup.in_(s2)) It seems to generate the appropriate SQL, but I'd prefer not to have to rely on _Grouping(), as it appears not to be part of the public API. Any suggestions for a more elegant way of doing this? Thanks, Matt Z. P.S. Creating the temporary table seen in select s2 was a bear, involving a manual string substitution on a Table.create() bound to an Engine with strategy=mock then feeding the result to the DB. Are there any plans for supporting temporary tables in SA? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL for (col1, col2) NOT IN (SELECT ...)
Thanks for the quick reply! I've always been quite impressed with the quality of SA and its support. I'm a bit swamped at work at the moment but I'll see about putting a 'CREATE TEMPORARY TABLE' patch together. MZ On Mon, Apr 28, 2008 at 4:09 PM, Michael Bayer [EMAIL PROTECTED] wrote: that's pretty good you came up with that. We haven't placed explicit support for multiple items as the subject of an IN statement. You can do what you have there without _Grouping by saying ClauseList(...).self_group(). I think you're safe with that for now though we should add a public function for this purpose at some point. if its just a matter of saying CREATE TEMPORARY TABLE instead of CREATE TABLE, we can accept a patch for temporary=True, sure. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy, Postgres and ENum?
I didn't see anything in the doc, and google wasn't much help in this case so I'm guessing that SA doesn't support a ENum type with PG? If not, are there standard/best practices for working around it in SA? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SA confusing timestamp with interval?
I'm using SA 0.4.3 and PostgreSQL 8.3.1
I'm new to SA, so perhaps I'm doing something wrong or just not
understanding something, but I think SA is trying to treat my timestamps as
intervals in some cases. If I run the equivalent (select c0 from t0 where
c0 current_timestamp - interval '1 hour') via psql, it works as expected.
However,
If I run:
#!/usr/bin/python
from datetime import datetime
from sqlalchemy.sql import text
from sqlalchemy import create_engine, MetaData
engine = create_engine('postgres://[EMAIL PROTECTED]/testdb',
encoding='utf-8')
metadata = MetaData(bind=engine)
engine.execute(text(drop table if exists t0))
engine.execute(text(create table t0(c0 timestamp(0) with time zone)))
engine.execute(text(insert into t0 values(current_timestamp)))
engine.execute(text(select c0 from t0 where c0 :bindArg - interval '1
hour'), bindArg=datetime.utcnow())
I get:
Traceback (most recent call last):
File ./saerr.py, line 14, in module
engine.execute(text(select c0 from t0 where c0 :bindArg - interval '1
hour'), bindArg=datetime.utcnow())
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
1214, in execute
return connection.execute(statement, *multiparams, **params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
846, in execute
return Connection.executors[c](self, object, multiparams, params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
897, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
909, in _execute_compiled
self.__execute_raw(context)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
918, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
962, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py, line
944, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) invalid input
syntax for type interval: 2008-04-18T15:37:02.235955
select c0 from t0 where c0 %(bindArg)s - interval '1 hour' {'bindArg':
datetime.datetime(2008, 4, 18, 15, 37, 2, 235955)}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SA confusing timestamp with interval?
I get a similar result if I use psycopg2 directly:
#!/usr/bin/python
import psycopg2
from datetime import datetime
conn = psycopg2.connect('''dbname=testdb user=postgres host=localhost''')
cur = conn.cursor()
cur.execute(drop table if exists t0)
cur.execute(create table t0(c0 timestamp(0) with time zone))
cur.execute(insert into t0 values(current_timestamp))
cur.execute(select c0 from t0 where c0 %(bindArg)s - interval '1 hour',
{'bindArg':datetime.utcnow()})
On Fri, Apr 18, 2008 at 6:26 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
On Apr 18, 2008, at 4:42 PM, Matthew Dennis wrote:
I'm using SA 0.4.3 and PostgreSQL 8.3.1
I'm new to SA, so perhaps I'm doing something wrong or just not
understanding something, but I think SA is trying to treat my
timestamps as intervals in some cases. If I run the equivalent
(select c0 from t0 where c0 current_timestamp - interval '1 hour')
via psql, it works as expected. However,
If I run:
#!/usr/bin/python
from datetime import datetime
from sqlalchemy.sql import text
from sqlalchemy import create_engine, MetaData
engine = create_engine('postgres://[EMAIL PROTECTED]/testdb',
encoding='utf-8')
metadata = MetaData(bind=engine)
engine.execute(text(drop table if exists t0))
engine.execute(text(create table t0(c0 timestamp(0) with time
zone)))
engine.execute(text(insert into t0 values(current_timestamp)))
engine.execute(text(select c0 from t0 where c0 :bindArg -
interval '1 hour'), bindArg=datetime.utcnow())
the text() above sends through the bind argument to psycopg2 directly,
which knows how to handle datetime objects. What happens if you test
with raw psycopg2 ?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SA confusing timestamp with interval?
I posted it on the psycopg list at
http://lists.initd.org/pipermail/psycopg/2008-April/006026.html, but it
mangled my link to this discussion (by eating a space after the URL and
appending the first word of the next sentence)
On Fri, Apr 18, 2008 at 9:34 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
On Apr 18, 2008, at 10:20 PM, Matthew Dennis wrote:
I get a similar result if I use psycopg2 directly:
#!/usr/bin/python
import psycopg2
from datetime import datetime
conn = psycopg2.connect('''dbname=testdb user=postgres
host=localhost''')
cur = conn.cursor()
cur.execute(drop table if exists t0)
cur.execute(create table t0(c0 timestamp(0) with time zone))
cur.execute(insert into t0 values(current_timestamp))
cur.execute(select c0 from t0 where c0 %(bindArg)s - interval '1
hour', {'bindArg':datetime.utcnow()})
great. lets let them know on the psycopg2 list.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
I've posted a synopsis of this on the earlier referenced pylons thread to see what those guys have to say. I don't think this is really an AJAX specific problem - although the asynchronous nature of AJAX means its more likely to occur. It seems to me that any http request can arrive concurrently at the web server from the same web browser sharing the same token or session -- unless the app server (pylons) in this instance serialized access to the controller under these circumstances then concurrent access to the same data model could easily occur. Perhaps SQLAlchemy could optionally cache any in-memory models and return the same instance to multiple threads so that local serialization on the model could take place? On Nov 2, 10:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: this would seem to me like it should be fixed at the ajax level, or possibly the level of the webserver where it receives ajax events. your ajax functionality constitutes a GUI, and most GUIs I've worked with have a single event queue whereby all events are serialized into a single stream. the nature of the XMLHttpRequest is asynchronous so it seems like either your ajax library would have to delay the second ajax request until the first one completes, or the web server would have to enforce similar behavior (probably easier on the ajax side).- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
Thanks for the response. I duplicated the original problem with the
following test:
$ cat /f/t.py
import sqlite3
c1 = sqlite3.connect(db, timeout=5.0)
try:
c1.execute(create table person(lastname, firstname))
c1.execute(insert into person values('newhook', 'matthew'))
c1.commit()
except sqlite3.OperationalError, e:
pass
c1 = sqlite3.connect(db, timeout=5.0, isolation_level=immediate)
c2 = sqlite3.connect(db, timeout=5.0, isolation_level=immediate)
c1.execute(insert into person values('newhook', 'matthew'))
c2.execute(insert into person values('newhook', 'matthew'))
While I understand why this deadlocks without BEGIN IMMEDIATE (of
course, this would always deadlock -- but this deadlocks immediately
as opposed to after 5 seconds). The documentation, imo, doesn't really
make it obvious:
timeout - When a database is accessed by multiple connections, and one
of the processes modifies the database, the SQLite database is locked
until that transaction is committed. The timeout parameter specifies
how long the connection should wait for the lock to go away until
raising an exception. The default for the timeout parameter is 5.0
(five seconds).
This isn't true of course as it depends on the isolation.
I verified with the next test that things do work correctly:
$ cat t1.py
import sqlite3
con = sqlite3.connect(db, timeout=5.0)
try:
con.execute(create table person(lastname, firstname))
con.execute(insert into person values('newhook', 'matthew'))
con.commit()
except sqlite3.OperationalError, e:
pass
for row in con.execute(select * from person):
print row
import threading
import thread
class TestThread(threading.Thread):
def __init__(self):
threading.Thread.__init__(self)
self._stop = False
self._lock = thread.allocate_lock()
self._count = 0
def run(self):
while True:
self._lock.acquire()
if self._stop:
self._lock.release()
return
self._lock.release()
con = sqlite3.connect(db, timeout=5.0,
isolation_level=immediate)
con.execute(insert into person values('newhook',
'matthew'))
con.commit()
time.sleep(0.1)
self._count = self._count + 1
def count(self):
return self._count
def stop(self):
self._lock.acquire()
self._stop = True
self._lock.release()
t1 = TestThread()
t2 = TestThread()
t1.start()
t2.start()
import time
time.sleep(2)
t1.stop()
t2.stop()
t1.join()
t2.join()
print t1._count=%d % t1.count()
print t2._count=%d % t2.count()
Regards, Matthew
On Nov 2, 7:53 pm, Gerhard Häring [EMAIL PROTECTED] wrote:
On Nov 1, 3:13 pm, Matthew Newhook [EMAIL PROTECTED] wrote:
I posted this message in the pylons group but as of yet have received
no response.
I saw it there and followed here. And subscribed while I'm at it - I'm
playing with SQLAlchemy myself recently.
http://groups.google.com/group/pylons-discuss/browse_frm/thread/093ec...
I've been looking into this problem a little more. From my reading of
the SQLite documentation there should be a 5 second timeout by default
(I'm using pysqlite 2.3.2). [...]
Please update to the latest pysqlite (version 2.3.5). I've improved
pysqlite's concurrency with 2.3.4 by deferring the implicit BEGIN/
COMMITs.
This was the problem and the
patch:http://initd.org/tracker/pysqlite/ticket/184
-- Gerhard
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
On Nov 2, 11:55 pm, Michael Bayer [EMAIL PROTECTED] wrote: I'd have to disagree here; SQLAlchemy is a database access library, its not a caching or application framework. Providing a thread- synrchronized caching container would be the job for Pylons or some other third party software (or roll your own). Also this solution wouldn't work for application models that are non-threaded (like process-split models); out-of-sync requests would still hit a locked database. Ok, after thinking some more I agree with the above. I'm not really sure whether this can be fixed in general, however, at the AJAX or web server level effectively. If I started the transaction earlier and upgraded it to a write transaction when I initially load the cart then I could ensure that this failure scenario wouldn't occur. Is that currently possible? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
I took your advice and duplicate the problem in a small python test
app. Changing the isolation_level to immediate seems to help the
problem, which is good!
task.mapper.save_obj(task.polymorphic_tosave_objects, trans)
Module sqlalchemy.orm.mapper:1201 in save_obj
... some variables ...
rec (coffeeshop.models.CartItem object at 0x1a3b750,
{'cart_items_id': 12, 'quantity': 2}, ...;sqlalchemy.orm.map
rows0
t table table_to_mapper update [(coffeeshop.models.CartItem
object at 0x1a3b750, {'cart_items_id': 12, 'quantity':
2}, ...sqlalchemy.orm.map
per.Mapper object at 0x161b470)]
updated_objects set([coffeeshop.models.CartItem object at
0x1a3b750])
class 'sqlalchemy.exceptions.ConcurrentModificationError': Updated
rowcount 0 does not match number of objects updated 1
The SQL echo is:
$ cat /f/t
2007-11-02 18:46:17,556 INFO sqlalchemy.engine.base.Engine.0x..b0
BEGIN
2007-11-02 18:46:17,557 INFO sqlalchemy.engine.base.Engine.0x..b0
SELECT cart_items.product_id AS cart_items_product_id,
cart_items.price AS cart_items_price, cart_items.id AS cart_items_id,
cart_items.cart_id AS cart_items_cart_id, cart_it
ems.quantity AS cart_items_quantity
FROM cart_items
WHERE cart_items.id = ? ORDER BY cart_items.oid
2007-11-02 18:46:17,557 INFO sqlalchemy.engine.base.Engine.0x..b0
[u'12']
2007-11-02 18:46:17,559 INFO sqlalchemy.engine.base.Engine.0x..b0
COMMIT
2007-11-02 18:46:18,518 INFO sqlalchemy.engine.base.Engine.0x..b0
UPDATE cart_items SET quantity=? WHERE cart_items.id =
?
2007-11-02 18:46:18,519 INFO sqlalchemy.engine.base.Engine.0x..b0 [2,
12]
2007-11-02 18:46:18,519 INFO sqlalchemy.engine.base.Engine.0x..b0
ROLLBACK
Debug at: http://192.168.2.34:5432/_debug/view/1194000220
That doesn't look good! The code in question is something like:
try:
if c.cart.remove(id):
c.cart.flush()
transaction.commit()
except:
transaction.rollback()
raise
Its crapping out on the transaction.commit().
Regards, Matthew
On Nov 1, 11:39 pm, Michael Bayer [EMAIL PROTECTED] wrote:
1st step would be to ensure youre on the latest version of sqlite.
second step would be to create a test program illustrating the
behavior using pysqlite only (sqlalchemy doesn't have anything to do
with sqlite lock timeout issues). if you can confirm that the
timeout isnt working in that case, you can submit a bug report to
pysqlite and/or sqlite. the sqlite devs are *very* responsive to
issues.
On Nov 1, 2007, at 10:13 AM, Matthew Newhook wrote:
I posted this message in the pylons group but as of yet have received
no response.
http://groups.google.com/group/pylons-discuss/browse_frm/thread/
093ec04b48e49c3c?hl=en#
I've been looking into this problem a little more. From my reading of
the SQLite documentation there should be a 5 second timeout by default
(I'm using pysqlite 2.3.2). Looking at the source this looks correct.
However, in testing my app when I get this exception there is
definitely no 5 second timeout! It happens immediately. I also tried
500 seconds, and had no better luck. When I concurrently access my
webapp I immediately get this exception.
Also the exception confuses me. The 'database is locked' text
indicates that the error is SQLITE_LOCKED which is documented as:
SQLITE_LOCKED
This return code is similar to SQLITE_BUSY in that it indicates
that the database is locked. But the source of the lock is a recursive
call to sqlite_exec. This return can only occur if you attempt to
invoke sqlite_exec from within a callback routine of a query from a
prior invocation of sqlite_exec. Recursive calls to sqlite_exec are
allowed as long as they do not attempt to write the same table.
However, the documentation indicates that I should be getting
SQLITE_BUSY or IOERROR.
Anyone have any ideas how to solve this problem?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
I posted this message in the pylons group but as of yet have received no response. http://groups.google.com/group/pylons-discuss/browse_frm/thread/093ec04b48e49c3c?hl=en# I've been looking into this problem a little more. From my reading of the SQLite documentation there should be a 5 second timeout by default (I'm using pysqlite 2.3.2). Looking at the source this looks correct. However, in testing my app when I get this exception there is definitely no 5 second timeout! It happens immediately. I also tried 500 seconds, and had no better luck. When I concurrently access my webapp I immediately get this exception. Also the exception confuses me. The 'database is locked' text indicates that the error is SQLITE_LOCKED which is documented as: SQLITE_LOCKED This return code is similar to SQLITE_BUSY in that it indicates that the database is locked. But the source of the lock is a recursive call to sqlite_exec. This return can only occur if you attempt to invoke sqlite_exec from within a callback routine of a query from a prior invocation of sqlite_exec. Recursive calls to sqlite_exec are allowed as long as they do not attempt to write the same table. However, the documentation indicates that I should be getting SQLITE_BUSY or IOERROR. Anyone have any ideas how to solve this problem? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
