[sqlalchemy] Determine what joins are in select statement
Hi, Just wondering if there is an easy way to determine what tables are joined in a query? I'm doing something like this query = session.query(System, dynamic_obj).select_from(self.j) Where dynamic_obj could be any mapped object and self.j represents a join produced by the sqlalchemy.schema.Table.join() function. I'd like to know if the table represented by dynamic_obj is already in the self.j I could of course do this programmatically outside of sqla, but it would be nice if sqla could tell me this. Cheers --~--~-~--~~~---~--~~ 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: Multi table select?
On Mon, Oct 26, 2009 at 10:09 PM, Bobby Impollonia bob...@gmail.com wrote:
You can also create a view mapped to that union and use that as a
virtual table so that you don't have to repeat the union specification
for every query:
http://www.w3schools.com/Sql/sql_view.asp
I don't know if that helps for SQLAlchemy though.
On Mon, Oct 26, 2009 at 5:59 PM, AF allen.fow...@yahoo.com wrote:
On Oct 26, 8:48 pm, AF allen.fow...@yahoo.com wrote:
Hello,
I don't know if this is even possible is SQL, so please bear with
me :)
There are a couple a tables (say, a b) that are used as logs for
two different processes. They both have the same simple structure.
(id, time_stamp, user_id, message)
I would like to create a query that merges the data and returns
following results:
time_stamp, user_id, a_or_b, message
(where a_or_b is a value that indicates which table the data row came
from)
Can this be done in SQL/SQLAlchemy.
Thank you,
:)
p.s.
Alternatively, the message columns do not need to be merged though
I guess time_stamp / user would still need to be.
That is: time_stamp, user_id, message_a, message_b
I don't know if that makes any easier...
OK:
http://www.w3schools.com/Sql/sql_union.asp
Doh. OK, so now I have an SQL statement I wrote by hand that works
fine, but I still have two questions:
1) Can this be done via the SQA ORM?
2) If not, how should I I build this using non-ORM SQA?
Thank you,
:)
Let's say you have 2 mapped classes
class Stuff1(Base):
--- etc.
class Stuff2(Base)
--- etc.
You can do something like this
q1 = session.query(Stuff1.columnA,
Stuff1.columnB, literal_column('S1').label('source'))
q2 = session.query(Stuff2.columnA,
Stuff2.columnB, literal_column('S2').label('source'))
subq = session.query().from_statement(union_all(q1, q2)).subquery()
qry = session.query(subq)
Note: just in case your font makes the quotes hard to read,
'S1' is double-quote,single-quote,S,1,single-quote,double-quote
--~--~-~--~~~---~--~~
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: How to label text columns in a query
Only a couple of months late, but here is the final working recipe:
class A(Base):
__tablename__ = 'tbl_a'
id = Column(Integer, primary_key=True)
data = Column(String)
class B(Base):
__tablename__ = 'tbl_b'
id = Column(Integer, primary_key=True)
data = Column(String)
meta.create_all()
session.add(A(data='a1'))
session.add(B(data='b1'))
session.commit()
q1 = session.query(A.data.label('somedata'),
literal_column('A').label('source'))
q2 = session.query(B.data.label('somedata'),
literal_column('B').label('source'))
subq = session.query().from_statement(union_all(q1,q2)).subquery()
query = session.query(subq)
for row in query:
print row.source, row.somedata
generated SQL is:
SELECT anon_1.somedata AS anon_1_somedata, anon_1.source AS anon_1_source
FROM (SELECT tbl_a.data AS somedata, 'A' AS source
FROM tbl_a UNION ALL SELECT tbl_b.data AS somedata, 'B' AS source
FROM tbl_b) AS anon_1
Not sure if using the subquery will cause inefficient SQL, but that would
take some research looking at query plans and might vary by database engine.
--~--~-~--~~~---~--~~
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] Help Writing Portable Query
Hello Guys,
I've got a query which I'm currently running as literal SQL against a
MySQL database. I'm looking to create a ported version of the query
for SQLite but am totally new to that platform so am looking for a
little help with my date/time functions, I'm hoping someone here will
have a little more experience with SQLite and be able to help out.
In addition to making these changes to the literal SQL I'm wondering
if the query can be rewritten in a more SQLAlchemy style using
functions rather than literal SQL so that it is more portable in
future? Or is that going to be tricky?
The query, or at least the WHERE clause can be found in this pastebin
snippet.
http://pastebin.com/m24c39a4f
I appreciate any help you guys can offer to get me started. I
understand I can get the current date within SQLite by running date
('now') however some of the more tricky modifiers for getting
DayOfWeek and DayOfMonth are beyond my understanding at the moment.
Thanks,
Rob
--~--~-~--~~~---~--~~
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: Help Writing Portable Query
Sir Rawlins wrote:
Hello Guys,
I've got a query which I'm currently running as literal SQL against a
MySQL database. I'm looking to create a ported version of the query
for SQLite but am totally new to that platform so am looking for a
little help with my date/time functions, I'm hoping someone here will
have a little more experience with SQLite and be able to help out.
In addition to making these changes to the literal SQL I'm wondering
if the query can be rewritten in a more SQLAlchemy style using
functions rather than literal SQL so that it is more portable in
future? Or is that going to be tricky?
The query, or at least the WHERE clause can be found in this pastebin
snippet.
http://pastebin.com/m24c39a4f
I appreciate any help you guys can offer to get me started. I
understand I can get the current date within SQLite by running date
('now') however some of the more tricky modifiers for getting
DayOfWeek and DayOfMonth are beyond my understanding at the moment.
Thanks,
Rob
SQLite's strftime() function will give you equivalent results:
TO_DAYS(x) - CAST(strftime('%J', x) AS INTEGER)
MOD(x, y) - x % y
DAYOFMONTH(x) - CAST(strftime('%d', x) AS INTEGER)
DAYOFYEAR(x) - CAST(strftime('%j', x) AS INTEGER)
DAYOFWEEK(x) - CAST(strftime('%w', x) AS INTEGER) + 1
AFAIK SQLAlchemy does not provide database-neutral functions for dates,
so you are stuck with database-specific queries.
-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] Create database and transactional blocks strangeness
Hi all,
I am having a problem getting database creation on PostgreSQL done
correctly in an API that I am writing. I am using a svn checkout of SA trunk
from yesterday if that is important.
I have use the following code to create the database:
--- snip ---
...
try:
import psycopg2.extensions as e
ISOLATION_LEVEL_AUTOCOMMIT = e.ISOLATION_LEVEL_AUTOCOMMIT
ISOLATION_LEVEL_READ_COMMITTED = e.ISOLATION_LEVEL_READ_COMMITTED
ISOLATION_LEVEL_SERIALIZABLE = e.ISOLATION_LEVEL_SERIALIZABLE
del e
except ImportError, imp_err:
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_COMMITTED = 1
ISOLATION_LEVEL_SERIALIZABLE = 2
...
def __init__():
...
self._admin_engine = create_engine(
'%s+%s://%s:%...@%s/postgres'%(self.vendor, self.driver,
self.user,
self.password, self.host))
self._AdminSession = sessionmaker(bind=self._admin_engine)
...
@property
def admin_session(self):
if self._admin_session is None:
self._admin_session = self._AdminSession()
return self._admin_session
...
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
I can create the database just fine within the interpreter:
--- snip ---
import mwdb
db = mwdb.orm.database.PostgreSQLDatabase('psycopg2', 'babilen',
'PASSWORD', 'localhost', 'test', 'zh')
db.all_databases()
['template1', 'template0', 'postgres']
db.create()
db.all_databases()
['template1', 'template0', 'postgres', 'test']
db.drop()
db.all_databases()
['template1', 'template0', 'postgres']
--- snip ---
But this fails miserably when the API is used within a program:
--- snip ---
dump_db = mwdb.orm.database.PostgreSQLDatabase(
self.options.pg_driver,
self.options.pg_username,
self.options.password,
self.options.pg_host,
self._database_name(dump_info),
dump_info['language'])
if self._database_name(dump_info) not in dump_db.all_databases():
LOG.info('Create database: %s' % self._database_name(dump_info))
dump_db.create()
--- snip ---
Traceback:
--- snip ---
Traceback (most recent call last):
File /home/babilen/.virtualenvs/wp-import/bin/wp-import, line 185, in
module
pg_importer.import_from_directory(ARGS[0])
File
/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py,
line 147, in import_from_directory
self._import_dump(dump_info)
File
/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py,
line 103, in _import_dump
dump_db.create()
File
/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/mwdb/orm/database.py,
line 515, in create
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/orm/session.py,
line 739, in execute
clause, params or {})
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py,
line 975, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py,
line 1037, in _execute_clauseelement
return self.__execute_context(context)
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py,
line 1060, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py,
line 1122, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py,
line 1120, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/default.py,
line 181, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) CREATE DATABASE cannot run inside
a transaction block
'CREATE DATABASE wp_zh_20091023' {}
--- snip ---
Do you have any idea why this is happening?
Is the .connection.connection.set_isolation_level() the right way to do this?
Why do I have to write connection.connection? This used to (?) be different.
signature.asc
Description: Digital signature
[sqlalchemy] Re: Create database and transactional blocks strangeness
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
--~--~-~--~~~---~--~~
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: Create database and transactional blocks strangeness
On Tue, Oct 27, 2009 at 16:22 -0400, Michael Bayer wrote:
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
You are right! I changed the code to this:
--- snip ---
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
conn = self._admin_engine.connect()
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
conn.execute('CREATE DATABASE %s'%(self.name))
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
and it works like a charm.
But i still have some little questions...
* Is there an even better way to do this? ;-)
* Is it necessary to set the isolation level to the value it had
before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
explicitly setting that?
(I will change the code so it remembers the value of isolation_level
and use that instead of it to ISOLATION_LEVEL_READ_COMMITTED
explicitly)
* Why the .connection.connection ? I remember that I had to write just
one .connection in the past.
And one more word... This is the fastest mailing list I have ever used.
Thank you so much for reacting so fast on this ML, thank you very much
for SA and thanks for the solution to my problem!
have a great afternoon
Wolodja Wentland
signature.asc
Description: Digital signature
[sqlalchemy] Re: Create database and transactional blocks strangeness
Wolodja Wentland wrote:
On Tue, Oct 27, 2009 at 16:22 -0400, Michael Bayer wrote:
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
You are right! I changed the code to this:
--- snip ---
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
conn = self._admin_engine.connect()
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
conn.execute('CREATE DATABASE %s'%(self.name))
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
and it works like a charm.
But i still have some little questions...
* Is there an even better way to do this? ;-)
create_engine() for PG supports an isolation_level parameter. But it
only does the four levels PG provides, it doesn't yet have a hook for
Psycopg2's autocommit mode.
* Is it necessary to set the isolation level to the value it had
before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
explicitly setting that?
the way you have it, yes. Alternatively, you can call detach() on the
connection you returned and it will be de-associated from the connection
pool. Otherwise I would absolutely use try/finally above so that the
isolation level is returned to normal if the CREATE DATABASE fails.
* Why the .connection.connection ? I remember that I had to write just
one .connection in the past.
it should only need to be conn.connection. That returns a wrapper that
will pass all method calls down to the psycopg2 connection. would be
interested to know the error otherwise.
And one more word... This is the fastest mailing list I have ever used.
Thank you so much for reacting so fast on this ML, thank you very much
for SA and thanks for the solution to my problem!
youre welcome
have a great afternoon
Wolodja Wentland
--~--~-~--~~~---~--~~
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] outer join with two clauses
Hi everyone, how do I use two clauses in an outerjoin? e.g.: select ... from t1 outer join t2 on (t1.c1=t2.c1 and t1.c2t2.c2) According to docs, outerjoin is outerjoin(left, right, onclause=None) so I don't really know where to place the second clause. In a query object, outerjoin is different: outerjoin(*props, **kwargs) although I couldn't find much documentation about it. TIA, Mariano. --~--~-~--~~~---~--~~ 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: outer join with two clauses
You wrap your two conditions with and_() as your onclause. Mariano Mara wrote: Hi everyone, how do I use two clauses in an outerjoin? e.g.: select ... from t1 outer join t2 on (t1.c1=t2.c1 and t1.c2t2.c2) According to docs, outerjoin is outerjoin(left, right, onclause=None) so I don't really know where to place the second clause. In a query object, outerjoin is different: outerjoin(*props, **kwargs) although I couldn't find much documentation about it. TIA, Mariano. -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Many to Many relationships, dynamic_loaders, etc
Greetings!
I've known about sqlalchemy for a long time and have been interested in
using it professionally for a while now. I finally have the chance to use it
and have really been enjoying it. But it turns out that I am a little
confused about the best way to handle m2m relationships that have additional
fields.
Let's say we have 4 tables, User, Tag, Post, PostTagAssociation.
PostTagAssociation has FKs of course, but also a tagged_at datetime field.
I am familiar with association_proxy, but I prefer a different sort of
interface. Something closer to dynamic_loader that would let me do things
like:
#querying across my dynamic_loader:
post = test_user.posts.filter(Post.
title.like('%geopolitic%')).first()
#query through an association proxy, eg to get all posts tagged with war
related keywords:
#of course, this doesn't work since it returns an AssociationList instead of
an AppenderQuery:
post.tags.filter(Tag.name.like('%war%'))
#and then i still want to be able to have the AssociationList functionality
like:
post.tags.append(Tag(peace))
post.tags.remove(Tag(war))
#what would be really amazing is if I could set it up to decorate
#the returned item with some or all fields from the m2m table, eg:
print post.tags.first().tagged_at # = 10/27/2009 21:24
This seems like it would be commonly desired functionality, so I am
wondering if perhaps: a) I missed it, and there is some option already in
sqlalchemy, or b) Someone already has created an extension that provides
this.
If not, I wonder if supplying a new query class to the query_class option of
dynamic_loader might be able to accomplish this. Does that strategy make
sense? Are there any examples around that supply a query_class to the
dynamic_loader function? I couldn't find any, though I definitely might have
missed them.
I know it's also possible that am thinking about this all wrong, asking the
wrong question since I am used to other kinds of ORMs. If so, I'd love to
hear how experienced sqlalchemy users achieve this same sort of
functionality (eg, do they manually add methods to their model classes that
work directly with the association table and do a subquery, or something
completely different?)
Thank you so very much, and please excuse the long post.
Best!
Jared Nuzzolillo
--~--~-~--~~~---~--~~
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: UnicodeDecodeError with pg8000 postgresql.conf client_encoding=utf8, engine encoding=utf-8
On Sat, 24 Oct 2009 04:02:04 -0700 (PDT) sector119 sector...@gmail.com wrote: Hi All. I've got UnicodeDecodeError: 'ascii' codec can't decode byte 0xa1 in position 3: ordinal not in range(128) Why it can happen? I use client_encoding=utf8 at postgresql.conf and encoding=utf-8 at create_engine. Is the PG database itself using UTF-8? I think new databases usually default to LATIN-1. It is just a guess: I'm not even sure if it would actually matter for this problem, but maybe it is something to check. -Kyle --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
