[sqlalchemy] Querying against the remote side of a ManyToMany
From the example here: http://www.sqlalchemy.org/docs/04/documentation.html#datamapping_manytomany How can i construct a query that will return the keywords for posts from a particular user? I'd expect this to work: session.query(Keyword).join(BlogPost.keywords) \ .filter(BlogPost.author == wendy).all() and it does with 0.5, but not with 0.4.7p1 :( I can make this work by adding a backref on the keywords relation and joining against it, but in real life (ie. my application) I'd like to avoid this given the number of tables that link to it. Any ideas? Test code is here: http://paste.pocoo.org/show/85495/. --~--~-~--~~~---~--~~ 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: generative queries
Im using 0.4.5, is this feature only available in 0.5?
On Sep 16, 4:02 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 16, 2008, at 3:51 AM, Grimsqueaker wrote:
I'm having some trouble using generative queries. In my case, I want
to compile a query and then add further from (join) clauses onto it
and change the column clause so that I get different results each
time. I can add from, where and column clauses easily using the
provided methods, but there seems to be no provision for removing
clauses. Is this intentional or is it an oversight?
Can anyone provide some insight into this issue?
The level of state which Query builds up for some generations is
fairly complex so we've balked on adding reversibility throughout
for now.
You can specify any custom set of columns and get a result immediately
using values() however:
query.values(MyClass.id, MyClass.name,
func.max(MyClass.foo).label('foo'))
--~--~-~--~~~---~--~~
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: Declarative documentation
Hi Doug, I'm a new user (like this week) of SqlAlchemy and I'm trying to find more information about using the Declarative system. In particular I'm trying to build a hierarchical table with one-to-many relationships within the table. So if anyone knows where there might be some additional documentation about this, examples or just some guidance, I'd very much appreciate it!! There's extensive documentation online and in the ext/declarative.py module itself. Beyond that it's basically just straight SQLAlchemy. So you would be handling a self referential hierarchy as demonstrated here: http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_selfreferential I hope that helps. Michael http://blog.michaeltrier.com/ --~--~-~--~~~---~--~~ 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] text() TextClauses and like literals
I'm having an issue I'm curious if anyone else has come across. When
I use a like SQL expression within a text() clause, I receive the
following exception:
... [my app code] ...
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 838 in scalar
return self.execute(object, *multiparams, **params).scalar()
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 848 in execute
return Connection.executors[c](self, object, multiparams, params)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 899 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-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 911 in _execute_compiled
self.__execute_raw(context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 920 in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/base.py', line 962 in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File '/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc1-py2.5.egg/
sqlalchemy/engine/default.py', line 123 in do_execute
cursor.execute(statement, parameters)
TypeError: 'dict' object is unindexable
The code to replicate this exception is pretty simple:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%\''))
That is my error, here is my solution:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%%\''))
Having two percent signs doesn't escape the %, it is still passed to
my postgres database with two % signs, but postgres interprets that
fine, and I don't get the SQLAlchemy exception.
Anyone else have this issue -- I thought I'd post b/c it took me a
fair amount of time to find the above solution :-).
The reason I am using literals + text clause for these queries is that
postgres will not use my index for like 'query%' queries otherwise (if
it is a bindable parameter it won't use the index).
Question on another note -- is there a utility/convenience function to
escape a string for before using it in a query (since I'm using
literals here I need to watch for SQL injection attacks)?
Thanks,
-Mark
--~--~-~--~~~---~--~~
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: Querying against the remote side of a ManyToMany
On Sep 17, 2008, at 3:35 AM, Alec Thomas wrote: From the example here: http://www.sqlalchemy.org/docs/04/documentation.html#datamapping_manytomany How can i construct a query that will return the keywords for posts from a particular user? I'd expect this to work: session.query(Keyword).join(BlogPost.keywords) \ .filter(BlogPost.author == wendy).all() and it does with 0.5, but not with 0.4.7p1 :( I can make this work by adding a backref on the keywords relation and joining against it, but in real life (ie. my application) I'd like to avoid this given the number of tables that link to it. Any ideas? the backref is the standard approach to giving sqlalchemy a path to join from Y to X, without spelling out the join condition yourself. Theres no reason at all you cant put one of these there - they dont impact performance, they dont load unnecessarily, etc. if you'd like to use a sql.join() object, which is the only other option in 0.4, say: query (Keyword ).select_from (keywords .join (post_keywords).join(posts_table)).filter(BlogPost.author==wendy).all() the join() expressions there *should* work without an ON clause given. Its a little weird that joining from Keyword along BlogPost.keywords works in 0.5, that wasn't the intended usage there but I guess I can see how that's working. basically, either have relation() present (a backref is just a kind of relation()) so SQLA knows where to go, or spell out the join explicitly. --~--~-~--~~~---~--~~ 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] Problem using utf-8
Hi
Im using utf-8 in my mysql tables. If i insert a String with German
Umlaute like Ü it is converted to Ãœ in the database, it ist returned as
Ü. - What can i do that this will be correctly saved?
my connection looks like this:
engine = create_engine('mysql://xxx:[EMAIL PROTECTED]/test123', echo=False,
pool_size=30, pool_recycle=3600)
use_unicode, or the other Parameters doesn't work for me.
the table definition looks like this:
raum_table = Table('raum', metadata,
Column('id', Integer, ForeignKey('objekt.id'),primary_key=True),
Column('name', Unicode(100)),
Column('anzahl', Integer),
Column('raumbezeichnung', Integer),
Column('raumtyp', Integer),
Column('siedlungstyp', Integer),
Column('gebaeude', Unicode(200)),
Column('gebaeudeteil', Unicode(200)),
Column('fundstelle', Unicode(200)),
Column('befundgattung', Integer),
Column('befund', Unicode(200)),
Column('orttyp', Integer),
Column('ort', Integer)
)
the mapper looks like this:
mapper(Raum, raum_table,
inherits=Objekt, polymorphic_identity='raum',
properties={
'personen':relation(Person, secondary = person_raum_table),
'handlungen':relation(Handlung, secondary = raum_handlung_table),
'objekte':relation(WObjekt, secondary = wobjekt_raum_table),
'eigenschaften' : relation(Eigenschaft, secondary =
eigenschaft_raum_table)
}
)
--~--~-~--~~~---~--~~
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: generative queries
On Sep 17, 2008, at 8:57 AM, Empty wrote: Hello, Im using 0.4.5, is this feature only available in 0.5? Yes, it's not available in 0.4.5. There is the predecessor _values available in 0.4.5. It was experimental and it's behavior has changed somewhat but will basically do the same thing. upgrade to 0.4.7p1 and its available as query.values(). --~--~-~--~~~---~--~~ 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: text() TextClauses and like literals
On Sep 17, 2008, at 9:19 AM, Mark Mitchell wrote:
The code to replicate this exception is pretty simple:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%\''))
That is my error, here is my solution:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%%\''))
psycopg2's paramstyle is %(name)s, so the % needs escaping. to
replicate:
import psycopg2
conn = psycopg2.connect(user='scott', password='tiger', database='test')
cursor = conn.cursor()
cursor.execute(SELECT count(*) WHERE mytable.column like '%', {})
--~--~-~--~~~---~--~~
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: text() TextClauses and like literals
Thanks! That is good to know. Having trouble finding the docs for
psycopg2 -- any idea off the top of your head what the escape syntax
is for %? (When I put in '%%', pyscopg2 outputted %%, not %, to
postgres (but didn't throw the exception)).
I found the home page for pyscopg2 but no docs.
http://initd.org/projects/psycopg2
-Mark
On Sep 17, 10:31 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 17, 2008, at 9:19 AM, Mark Mitchell wrote:
The code to replicate this exception is pretty simple:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%\''))
That is my error, here is my solution:
count = con.scalar(select(['count(*)'], from_obj=my_table,
whereclause=text('mytable.column
like \'%%\''))
psycopg2's paramstyle is %(name)s, so the % needs escaping. to
replicate:
import psycopg2
conn = psycopg2.connect(user='scott', password='tiger', database='test')
cursor = conn.cursor()
cursor.execute(SELECT count(*) WHERE mytable.column like '%', {})
--~--~-~--~~~---~--~~
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: Problem using utf-8
I ran into a similar issue using MySQL-python-1.2.1_p2-1 (mysqldb)
with SA 0.4.2p3-1.
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg00373.html
might shed some more light on this issue which might be a double
encoding problem?
Here is the subset of relevant keyword arguments we use with that
particular database driver in create_engine:
create_engine_kwargs = {
'encoding': 'utf-8',
'connect_args': {
'use_unicode': False,
'charset': 'utf8',
},
}
On Wed, Sep 17, 2008 at 9:57 AM, Jürgen Hauptmann
[EMAIL PROTECTED] wrote:
Hi
Im using utf-8 in my mysql tables. If i insert a String with German
Umlaute like Ü it is converted to Ãœ in the database, it ist returned as
Ü. - What can i do that this will be correctly saved?
my connection looks like this:
engine = create_engine('mysql://xxx:[EMAIL PROTECTED]/test123', echo=False,
pool_size=30, pool_recycle=3600)
use_unicode, or the other Parameters doesn't work for me.
the table definition looks like this:
raum_table = Table('raum', metadata,
Column('id', Integer, ForeignKey('objekt.id'),primary_key=True),
Column('name', Unicode(100)),
Column('anzahl', Integer),
Column('raumbezeichnung', Integer),
Column('raumtyp', Integer),
Column('siedlungstyp', Integer),
Column('gebaeude', Unicode(200)),
Column('gebaeudeteil', Unicode(200)),
Column('fundstelle', Unicode(200)),
Column('befundgattung', Integer),
Column('befund', Unicode(200)),
Column('orttyp', Integer),
Column('ort', Integer)
)
the mapper looks like this:
mapper(Raum, raum_table,
inherits=Objekt, polymorphic_identity='raum',
properties={
'personen':relation(Person, secondary = person_raum_table),
'handlungen':relation(Handlung, secondary = raum_handlung_table),
'objekte':relation(WObjekt, secondary = wobjekt_raum_table),
'eigenschaften' : relation(Eigenschaft, secondary =
eigenschaft_raum_table)
}
)
--~--~-~--~~~---~--~~
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: Declarative documentation
Michael,
Thanks for getting back to me so quickly. I've figured out how to get a
one-to-many, single table relationship working with one way relations,
here is that configuration:
class JobData(Base):
__tablename__ = jobs
id = Column(Integer, primary_key=True, autoincrement=True)
pid = Column('pid', Integer, ForeignKey('jobs.id'))
srcpath = Column(String(128), default=None)
press= Column(Integer, default=None)
priority = Column(Integer, default=None)
created = Column(DateTime, default=datetime.datetime.now)
def __init__(self, srcpath=None):
session = Session()
self.srcpath = srcpath
session.add(self)
session.commit()
Jobs that are children of other jobs get their pid field initialized,
and this seems to work well.
I wasn't sure if the link you sent was what you intended as that brought
up a page about eager loading. However, it was interesting reading all
the same!
Thanks again,
Doug
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Empty
Sent: Wednesday, September 17, 2008 9:02 AM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Declarative documentation
Hi Doug,
I'm a new user (like this week) of SqlAlchemy and I'm trying to find
more information about using the Declarative system. In particular
I'm
trying to build a hierarchical table with one-to-many relationships
within the table. So if anyone knows where there might be some
additional documentation about this, examples or just some guidance,
I'd very much appreciate it!!
There's extensive documentation online and in the ext/declarative.py
module itself. Beyond that it's basically just straight SQLAlchemy.
So you would be handling a self referential hierarchy as demonstrated
here:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_
selfreferential
I hope that helps.
Michael
http://blog.michaeltrier.com/
--~--~-~--~~~---~--~~
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: Problem using utf-8
On Sep 17, 2008, at 10:50 AM, Bo Shi wrote: I ran into a similar issue using MySQL-python-1.2.1_p2-1 (mysqldb) with SA 0.4.2p3-1. I would advise upgrading to MySQL-python 1.2.2. I believe some utf-8 issues have been fixed. --~--~-~--~~~---~--~~ 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] MySQL String with no length broken as of r4705
I have some of my columns defined as Column('foo', Unicode). This
worked fine with MySQL, but a change made in r4705 appears to have
broken this. Prior to 4705, that would generate a TEXT column in the
create table. This was switched to just VARCHAR.
MySQL 5.0.45 does not accept VARCHAR without a length, and from what I
see in the MySQL docs, this is the case all the way through MySQL 6.
I have SQLAlchemy generate my tables and I also have an sqlalchemy-
migrate migration in place to create a new table, which is how I
spotted this. Am I missing something, or is this a bug?
Thanks,
Kevin
--~--~-~--~~~---~--~~
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: MySQL String with no length broken as of r4705
Hi Mike, On Sep 17, 1:53 pm, Michael Bayer [EMAIL PROTECTED] wrote: We've strictified the difference between String() and Text() in 0.5 such that String() doesn't implicitly go from VARCHAR to TEXT without a length. So in your case you want to use UnicodeText. OK, I guess that's reasonable. But, at the same time, I would call the current behavior a bug, because it generates SQL that is rejected by the DBMS. It should either raise an exception saying You dork! you need to either provide a length or use a text column or just spit out a TEXT column, possibly with a warning. Thanks for the quick response! Kevin --~--~-~--~~~---~--~~ 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: MySQL String with no length broken as of r4705
On Sep 17, 2008, at 3:18 PM, Kevin Dangoor wrote: Hi Mike, On Sep 17, 1:53 pm, Michael Bayer [EMAIL PROTECTED] wrote: We've strictified the difference between String() and Text() in 0.5 such that String() doesn't implicitly go from VARCHAR to TEXT without a length. So in your case you want to use UnicodeText. OK, I guess that's reasonable. But, at the same time, I would call the current behavior a bug, because it generates SQL that is rejected by the DBMS. It should either raise an exception saying You dork! you need to either provide a length or use a text column or just spit out a TEXT column, possibly with a warning. Thanks for the quick response! some databases do accept VARCHAR without a length. Also, its a valid use case to use String/VARCHAR by itself without a length if you're using it in a CAST expression, as well as all the scenarios where you are using it just for its type alone and not to generate the DDL identifier. So in this case the DBMS's exception is the best exception to be raised (we usually try not to superimpose our own exceptions on top of what the database reports just fine). --~--~-~--~~~---~--~~ 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: MySQL String with no length broken as of r4705
On Sep 17, 4:21 pm, Michael Bayer [EMAIL PROTECTED] wrote: some databases do accept VARCHAR without a length. But the code I was looking at is in mysql.py, so I was assuming this is where database-specific behavior goes. Also, its a valid use case to use String/VARCHAR by itself without a length if you're using it in a CAST expression, as well as all the scenarios where you are using it just for its type alone and not to generate the DDL identifier. So in this case the DBMS's exception is the best exception to be raised (we usually try not to superimpose our own exceptions on top of what the database reports just fine). I've never used a CAST expression. Interesting... But, it looks like 'VARCHAR' is not a valid value for a CAST in MySQL: You can have CHAR with or without a length, according to this page: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Your exception handling strategy makes good sense, in general. In this case, I found the exception from the database to not be very helpful with respect to actually fixing this problem. It complained about a syntax error in a general area of the query. It didn't state anything like VARCHAR must have a length or anything like that. And, to compound the troubleshooting fun, when you use an abstraction like SQLAlchemy you generally expect that the basics will just work, even more so since it had just worked for me in the past. BTW, I should note that I'm just going to properly declare using UnicodeText and be done with it, but the behavior of mysql.py in this regard is suspect since it generates an expression that is not valid for CAST or table creation. Kevin --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
