[sqlalchemy] Re: obtaining a table schema
Look at the primary_key attribute of the table instance.
uu = Table('u',meta,
Column('id',Integer,primary_key=True),
Column('data',Integer))
print uu.primary_key.columns
['u.id']
Mike
On Tue, Apr 28, 2009 at 7:53 PM, Paul Rigor (gmail) paulri...@gmail.comwrote:
Hi gang,
I've recently started using sqlalchemy, so hopefully this isn't a stupid
question...
I was wondering whether there was an easy way to obtain a particular
table's schema if one is using just bare connection (ie, not using any
special orm's). Specifically, is there a utility method somewhere which
allows one to obtain the primary key of a table?
Thanks!!
paul
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--~--~-~--~~~---~--~~
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: Overriding reflected columns in SqlSoup?
Stephen,
Thanks for the response. I got around the primary key by making explicit sql
queries (luckily only a few small tables were problematic.)
Thanks again,
-neil
On Tue, Apr 28, 2009 at 1:11 AM, Stephen Emslie stephenems...@gmail.comwrote:
Hi Neil
I managed to make queries on those tables by creating a new table
definition and getting a class mapping from that explicitly, for
example:
from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import *
engine = create_engine('sqlite:///:memory:')
metadata = MetaData(bind=engine)
retailer_table = Table('retailer', metadata, Column('retailer_id',
primary_key=True), autoload=True)
db = SqlSoup(metadata)
Retailer = db.map(retailer_table)
Retailer.first()
MappedRetailer(retailer_id=33199, ...)
So then the Retailer class returns MappedRetailer instances even
though the underlying schema defines no primary key. That part seemed
fine, but I then had to work with relations on those tables that I was
mapping explicitly and I found it easier at that point just to skip
SqlSoup and define the table metadata and mapping myself.
I hope that helps.
Stephen Emslie
On Sun, Apr 26, 2009 at 11:48 PM, NeilK neilku...@gmail.com wrote:
Hi Stephen, did you find a way to access those tables without a
primary key using SqlSoup?
Thanks,
-neil
On Apr 25, 3:42 am, Stephen Emslie stephenems...@gmail.com wrote:
I am using SqlSoup to do a little maintenance on a database whose
schema I have no control over. Unfortunately some tables are without a
primary key, and thus SqlSoup complains when accessing them:
sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'category' does not have
a primary key defined
When reflecting tables directly with sqlalchemy, using Table(name,
meta, autoload=True), one can override the reflected columns to
compensate for the lack of a primary key. Is this possible in SqlSoup?
Stephen Emslie
--~--~-~--~~~---~--~~
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] query().union on a lot of queries
Hi, I have to use sqlalchemy to execute a very complex query. It would be the union of ~30.000 smaller queries, joined with query.union() or query.union_all(). I wonder whether it is better to just execute all these queries separately and merge the results in python, or try the query.union approach. I noticed that unifying two queries usually produces a bit of garbaged query, in the sense that the ORM tries to rename every object the the verbose output is very long. Has anyone ever tried to unify something like 30.000 query objects? Thanks --~--~-~--~~~---~--~~ 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: obtaining a table schema
On Apr 29, 1:53 am, Paul Rigor (gmail) paulri...@gmail.com wrote: Hi gang, I've recently started using sqlalchemy, so hopefully this isn't a stupid question... I was wondering whether there was an easy way to obtain a particular table's schema if one is using just bare connection (ie, not using any special orm's). Specifically, is there a utility method somewhere which allows one to obtain the primary key of a table? Hi, you could look at some sqlalchemy recipes, like this one: - http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay Otherwise, look at the documentation or at the code of the recipe. Thanks!! paul -- Paul Rigor Graduate Student Institute for Genomics and Bioinformatics Donald Bren School of Information and Computer Sciences University of California in Irvine 248 ICS2 Bldg. +1 (760) 536 - 6767 (skype) --~--~-~--~~~---~--~~ 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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Wed, Apr 29, 2009 at 9:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
- row = self.cursor.fetchone()
+ row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
Can you read over this ticket and see if maybe you are affected.
http://www.sqlalchemy.org/trac/ticket/1350
If not then somebody more familiar with sa would need to look into why
these tests are failing.
Thanks,
Lucas
--~--~-~--~~~---~--~~
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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 2009, at 10:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
This failure has started about a month ago and I haven't had time to
investigate.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Interesting fix. I'll apply and test against windows and pyodbc.
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
-row = self.cursor.fetchone()
+row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
--~--~-~--~~~---~--~~
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] contains_eager and self-referential queries
I'm having a bit of a problem eager loading the parent node of a
hierarchical tree-like table (ie, every node has one to many children).
If I simply add a options(eagerload(Asset.Parent)) to my query it
works as expected.
However often I need to select a node based on it's attributes as well
as the parent's attributes, so do a
join(Asset.Parent).options(contains_eager(Asset.Parent))
which gets me the correct node, however the parent isn't eager loaded,
and worse Asset.Parent is the same as the child.
Looking at the SQL that SA is generating, the join is correct, but no
columns from the joined row are returned.
Attached is a test, and the output.
Note about the test, the path column of my table has a unique constraint
on it, all three of the queries return the same node, but only the first
returns the correct node mapped as Asset.Parent.
--
David Gardner
Pipeline Tools Programmer, Sid the Science Kid
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.collections import attribute_mapped_collection
DB_HOST = 'dbserver'
DB_NAME = 'mydb'
DB_USER = 'user'
DB_PASS = 'password'
db_uri = 'postgres://%s:%...@%s/%s'%(DB_USER,DB_PASS,DB_HOST,DB_NAME)
db = create_engine (db_uri, pool_size=200,max_overflow=200)
metadata = MetaData(db)
class Asset(object):
pass
asset_table=Table('nodehierarchy',metadata, autoload=True)
asset_mapper = mapper(Asset, asset_table, properties = {
'Children' : relation(Asset, cascade='all',
primaryjoin=(asset_table.c.uid==asset_table.c.parentuid),
collection_class=attribute_mapped_collection('name'),
backref=backref('Parent',remote_side=[asset_table.c.uid]),
remote_side=[asset_table.c.parentuid]),
})
session=create_session()
p='testshow/eps/201'
db.echo=True
a=session.query(Asset).\
options(eagerload(Asset.Parent)).\
filter(Asset.path==p).one()
parent_name1=a.Parent.name
session.close()
session=create_session()
a=session.query(Asset).join(Asset.Parent,aliased=True).\
options(contains_eager(Asset.Parent)).\
filter(Asset.name==parent_name1).reset_joinpoint().\
filter(Asset.path==p).one()
parent_name2=a.Parent.name
session.close()
session=create_session()
parent_alias=aliased(Asset)
a = session.query(Asset).join((parent_alias,Asset.Parent)).\
options(contains_eager(Asset.Parent)).\
filter(parent_alias.name==parent_name1).\
filter(Asset.path==p).one()
print parent_name1==parent_name2
print parent_name1==a.Parent.name
print parent_name2==a.Parent.name
2009-04-29 09:42:35,575 INFO sqlalchemy.engine.base.Engine.0x...cc10 SELECT
anon_1.nodehierarchy_uid AS anon_1_nodehierarchy_uid,
anon_1.nodehierarchy_updated AS anon_1_nodehierarchy_updated,
anon_1.nodehierarchy_name AS anon_1_nodehierarchy_name,
anon_1.nodehierarchy_type AS anon_1_nodehierarchy_type,
anon_1.nodehierarchy_parentuid AS anon_1_nodehierarchy_parentuid,
anon_1.nodehierarchy_path AS anon_1_nodehierarchy_path, nodehierarchy_1.uid AS
nodehierarchy_1_uid, nodehierarchy_1.updated AS nodehierarchy_1_updated,
nodehierarchy_1.name AS nodehierarchy_1_name, nodehierarchy_1.type AS
nodehierarchy_1_type, nodehierarchy_1.parentuid AS nodehierarchy_1_parentuid,
nodehierarchy_1.path AS nodehierarchy_1_path
FROM (SELECT nodehierarchy.uid AS nodehierarchy_uid, nodehierarchy.updated AS
nodehierarchy_updated, nodehierarchy.name AS nodehierarchy_name,
nodehierarchy.type AS nodehierarchy_type, nodehierarchy.parentuid AS
nodehierarchy_parentuid, nodehierarchy.path AS nodehierarchy_path
FROM nodehierarchy
WHERE nodehierarchy.path = %(path_1)s
LIMIT 2 OFFSET 0) AS anon_1 LEFT OUTER JOIN nodehierarchy AS nodehierarchy_1
ON nodehierarchy_1.uid = anon_1.nodehierarchy_parentuid
2009-04-29 09:42:35,576 INFO sqlalchemy.engine.base.Engine.0x...cc10 {'path_1':
'testshow/eps/201'}
2009-04-29 09:42:35,588 INFO sqlalchemy.engine.base.Engine.0x...cc10 SELECT
nodehierarchy.uid AS nodehierarchy_uid, nodehierarchy.updated AS
nodehierarchy_updated, nodehierarchy.name AS nodehierarchy_name,
nodehierarchy.type AS nodehierarchy_type, nodehierarchy.parentuid AS
nodehierarchy_parentuid, nodehierarchy.path AS nodehierarchy_path
FROM nodehierarchy JOIN nodehierarchy AS nodehierarchy_1 ON nodehierarchy_1.uid
= nodehierarchy.parentuid
WHERE nodehierarchy_1.name
[sqlalchemy] Re: contains_eager and self-referential queries
David Gardner wrote: I'm having a bit of a problem eager loading the parent node of a hierarchical tree-like table (ie, every node has one to many children). If I simply add a options(eagerload(Asset.Parent)) to my query it works as expected. However often I need to select a node based on it's attributes as well as the parent's attributes, so do a join(Asset.Parent).options(contains_eager(Asset.Parent)) if this is a self referential join, you have to alias the target you're joining to. usually the aliased=True flag would be sufficient for the join(), but since you want to contains_eager() it as well, this all must be laid out explicitly: parent = aliased(Asset) query.join((parent, Asset.parent)).options(contains_eager(Asset.parent, alias=parent)) --~--~-~--~~~---~--~~ 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: InvalidRequestError: The transaction is inactive due to a rollback... using sqlite with multiple commits.
Daniel wrote: I've learned a bit more. Apparently the sqlite database occasionally gets locked by another process and that lock lasts longer than the five second default timeout. SQL Alchemy quietly issues a ROLLBACK, but doesn't say anything more about it. The result is that the session is no longer active and eventually produces the error I mentioned above. Here are the log messages showing the insert failing after the five second default timeout. 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 INSERT INTO table (id, value) VALUES (?, ?) 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 [1, 'my value'] 2009-04-28 15:10:53,148 INFO sqlalchemy.engine.base.Engine.0x...ae70 ROLLBACK Is there some way to configure SQL Alchemy to raise an exception when it issues the rollback, or when it finds the database locked? SQLAlchemy definitely raises an exception if the DBAPI sends one. a ROLLBACK does not occur if there was no exception thrown. check if you're catching it and not re-raising. --~--~-~--~~~---~--~~ 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: contains_eager and self-referential queries
Great! That works now. Thanks for the help. if this is a self referential join, you have to alias the target you're joining to. usually the aliased=True flag would be sufficient for the join(), but since you want to contains_eager() it as well, this all must be laid out explicitly: parent = aliased(Asset) query.join((parent, Asset.parent)).options(contains_eager(Asset.parent, alias=parent)) -- David Gardner Pipeline Tools Programmer, Sid the Science Kid 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 relation select
Hi, I've got table Ad which has many to many relation with table Equipment through table ad_equipment. I want to select all ads which have all equipments in the list. Corresponding SQL query will look like this: select * from ad where ad.id in ( select id from ad join ad_equipment eq1 on ad.id = eq1.ad join equipment e1 on eq1.equipment = e1.id join ad_equipment eq2 on ad.id = eq2.ad join equipment e2 on eq2.equipment = e2.id where e1.name = 'name1' and e2.name = 'name2' ) I've tried this the following: eq1 = ad_equipment_table.alias() eq2 = ad_equipment_table.alias() e1 = equipment_table.alias() e2 = equipment_table.alias() ad_table.join(eq1).join(e1).join(eq2).join(e2) but this corresponds to: select * from ad JOIN ad_equipment AS ad_equipment_1 ON ad.id = ad_equipment_1.ad JOIN equipment AS equipment_1 ON equipment_1.id = ad_equipment_1.equipment JOIN ad_equipment AS ad_equipment_2 ON equipment_1.id = ad_equipment_2.equipment AND ad.id = ad_equipment_2.ad JOIN equipment AS equipment_2 ON equipment_2.id = ad_equipment_1.equipment AND equipment_2.id = ad_equipment_2.equipment which is not what I want as it joins e2 to be the same as e1... Is it possible to write that SQL select in SA expression language? thanks, tomas --~--~-~--~~~---~--~~ 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] M:N select
Hi, I've got table Ad which has many to many relation with table Equipment through table ad_equipment. I want to select all ads which have all equipments in the list. Corresponding SQL query will look like this: select * from ad where ad.id in ( select id from ad join ad_equipment eq1 on ad.id = eq1.ad join equipment e1 on eq1.equipment = e1.id join ad_equipment eq2 on ad.id = eq2.ad join equipment e2 on eq2.equipment = e2.id where e1.name = 'name1' and e2.name = 'name2' ) How can I do this kind of select? I've tried something like eq1 = ad_equipment_table.alias() eq2 = ad_equipment_table.alias() e1 = equipment_table.alias() e2 = equipment_table.alias() ad_table.join(eq1).join(e1).join(eq2).join(e2) but this is not I wanted as resulting SQL joins for e1 and e2 are wired together... Is it possible to create this select by SA expression? Or is there a better way how to do that? thanks, tomas --~--~-~--~~~---~--~~ 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] reflecting oracle's INTERVAL DAY TO SECOND type
I'm having trouble reflecting Oracle's INTERVAL DAY TO SECOND type, does anybody have any suggestions? /Library/Python/2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'INTERVAL DAY TO SECOND' of column 'duration' self.dialect.reflecttable(conn, table, include_columns) --~--~-~--~~~---~--~~ 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: Many to many relation select
I've forgotten to attach the file with tables...
On Wed, Apr 29, 2009 at 4:12 PM, Tomáš Drenčák to...@drencak.com wrote:
Hi,
I've got table Ad which has many to many relation with table Equipment
through table ad_equipment. I want to select all ads which have all
equipments in the list.
Corresponding SQL query will look like this:
select * from ad where ad.id in (
select id from ad
join ad_equipment eq1 on ad.id = eq1.ad
join equipment e1 on eq1.equipment = e1.id
join ad_equipment eq2 on ad.id = eq2.ad
join equipment e2 on eq2.equipment = e2.id
where e1.name = 'name1' and e2.name = 'name2'
)
I've tried this the following:
eq1 = ad_equipment_table.alias()
eq2 = ad_equipment_table.alias()
e1 = equipment_table.alias()
e2 = equipment_table.alias()
ad_table.join(eq1).join(e1).join(eq2).join(e2)
but this corresponds to:
select * from ad
JOIN ad_equipment AS ad_equipment_1 ON ad.id = ad_equipment_1.ad
JOIN equipment AS equipment_1 ON equipment_1.id = ad_equipment_1.equipment
JOIN ad_equipment AS ad_equipment_2 ON equipment_1.id =
ad_equipment_2.equipment AND ad.id = ad_equipment_2.ad
JOIN equipment AS equipment_2 ON equipment_2.id =
ad_equipment_1.equipment AND equipment_2.id = ad_equipment_2.equipment
which is not what I want as it joins e2 to be the same as e1...
Is it possible to write that SQL select in SA expression language?
thanks,
tomas
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import MetaData, Integer, String, Table, ForeignKey, Column
metadata = MetaData()
equipment_table = Table('equipment', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
)
ad_equipment_table = Table('ad_equipment', metadata,
Column('id', Integer, primary_key=True),
Column('ad', ForeignKey('ad.id')),
Column('equipment', ForeignKey('equipment.id')),
)
ad_table = Table('ad', metadata,
Column('id', Integer, primary_key=True),
)
[sqlalchemy] Windows Vista
I'm not sure if is a sqlalchemy error but I think is, at least, a strange behaviour. I was trying to ran the addressbook example of amfast package, which combines sqlalchemy as orm and cherrypy as server. Before I do that I've done the following steps: - install python (2.6.1) - build and Install setuptools (0.6c9) - install pyamf via easy_install (0.4.2) - install cherrypy via easy_install (3.1.2) - install sqlalchemy via easy_install (v0.5.3) - install MinGW to compile amfast (0.3.0) - download, compile and install amfast So, inside .amfast/examples/addressbook/python i run: python cp_server.py and got: Traceback (most recent call last): File cp_server.py, line 11, in module import utils File C:\Desenvolvimento\python\AMFast\examples\addressbook\python \utils.py, line 11, in module from amfast.class_def.sa_class_def import SaClassDef File build\bdist.win32\egg\amfast\class_def\sa_class_def.py, line 10, in module File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\__init__.py, line 1 6, in module from sqlalchemy.orm.mapper import ( File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\mapper.py, line 25, in module from sqlalchemy.orm import attributes, exc, sync File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\attributes.py, line 24, in module from sqlalchemy.orm import interfaces, collections, exc ImportError: cannot import name interfaces This error occurs only in windows vista. I tried the same steps on a machine with XP installed and it runs fine. I tried also on other machine with GNU/Linux Ubuntu 8.04 without surprises. On windows vista I uninstalled completely python 2.6 and installed python 2.5.4 but I got the same error. May have anything I've done wrong? Has someone who has got the same error? 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: obtaining a table schema
Thanks Mike,
Like I mentioned for my particular application, I won't be using the usual
ORM but just the bare engine/connection. I'll just be provided with a table
name and a connection. I did a little bit of research but was only able to
figure out how to obtain the primary for a mysql database (ie, through the
mysql dialect instance).
I was just wondering if there was a transparent interface regardless of the
database dialect.
Paul
On Wed, Apr 29, 2009 at 5:35 AM, Mike Conley mconl...@gmail.com wrote:
Look at the primary_key attribute of the table instance.
uu = Table('u',meta,
Column('id',Integer,primary_key=True),
Column('data',Integer))
print uu.primary_key.columns
['u.id']
Mike
On Tue, Apr 28, 2009 at 7:53 PM, Paul Rigor (gmail)
paulri...@gmail.comwrote:
Hi gang,
I've recently started using sqlalchemy, so hopefully this isn't a stupid
question...
I was wondering whether there was an easy way to obtain a particular
table's schema if one is using just bare connection (ie, not using any
special orm's). Specifically, is there a utility method somewhere which
allows one to obtain the primary key of a table?
Thanks!!
paul
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--~--~-~--~~~---~--~~
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: reflecting oracle's INTERVAL DAY TO SECOND type
thats only a warning. the column is still usable, you just have to ensure that the python value being passed as a bind parameter is one that cx_oracle will interpret properly. On Apr 29, 2009, at 2:20 PM, Mike Lowe wrote: I'm having trouble reflecting Oracle's INTERVAL DAY TO SECOND type, does anybody have any suggestions? /Library/Python/2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'INTERVAL DAY TO SECOND' of column 'duration' self.dialect.reflecttable(conn, table, include_columns) --~--~-~--~~~---~--~~ 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: obtaining a table schema
Thanks,
FYI that link you sent was very useful.
For anyone else interested, here's my code snippet. I've tested this with
both mysql and sqlite databases.
def get_primary_key(tablename,*args,**kwargs):
from sqlalchemy import MetaData
metadata = MetaData(*args,**kwargs) # uri similar to instantiating
an engine
metadata.reflect()
try:
table = metadata.tables[tablename]
except KeyError:
table = metadata.tables.values()[0]
except IndexError:
raise(Exception(Error: The database does not contain any
tables.))
try:
primary_key = table.primary_key.keys()[0]
except IndexError:
raise(Exception(Error: The specified table has no primary
key!))
return primary_key
On Wed, Apr 29, 2009 at 12:33 PM, Michael Bayer mike...@zzzcomputing.comwrote:
the Table object as well as the primary_key attribute are transparent as
far as what DBAPI and database is in use. Its also not part of the ORM.
there is a more fine-grained interface called the Inspector available in
0.6, but you can get the same results by reflecting a Table.
On Apr 29, 2009, at 3:09 PM, Paul Rigor (gmail) wrote:
Thanks Mike,
Like I mentioned for my particular application, I won't be using the usual
ORM but just the bare engine/connection. I'll just be provided with a table
name and a connection. I did a little bit of research but was only able to
figure out how to obtain the primary for a mysql database (ie, through the
mysql dialect instance).
I was just wondering if there was a transparent interface regardless of the
database dialect.
Paul
On Wed, Apr 29, 2009 at 5:35 AM, Mike Conley mconl...@gmail.com wrote:
Look at the primary_key attribute of the table instance.
uu = Table('u',meta,
Column('id',Integer,primary_key=True),
Column('data',Integer))
print uu.primary_key.columns
['u.id']
Mike
On Tue, Apr 28, 2009 at 7:53 PM, Paul Rigor (gmail)
paulri...@gmail.comwrote:
Hi gang,
I've recently started using sqlalchemy, so hopefully this isn't a stupid
question...
I was wondering whether there was an easy way to obtain a particular
table's schema if one is using just bare connection (ie, not using any
special orm's). Specifically, is there a utility method somewhere which
allows one to obtain the primary key of a table?
Thanks!!
paul
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--
Paul Rigor
Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California in Irvine
248 ICS2 Bldg.
+1 (760) 536 - 6767 (skype)
--~--~-~--~~~---~--~~
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: query on a field from an expression
Resolved by moving the method into the schema procedure file. On Apr 28, 10:21 am, Paul Hemans p_hem...@hotmail.com wrote: Hi I am new to Python. I need to produce a simple query on the key field. exists = self.session.query(BILLS).filter(ID==1) However, the process is running generically. That is the declarative table definitions are in the file schema.py and the table, field and value are determined at runtime. So what I have is the following ( I have omitted a bit of code for clarity) : class myClass(): def import_data import schema # self.tableName defined elsewhere TI = eval(schema.+self.tableName+()) exists = None for node in tupleNode.childNodes: for dataNode in node.childNodes: cValue = dataNode.data if node.tagName == self.keyField: Prob. # self.keyField is determined elsewhere exists = self.session.query(TI).filter(getattr (TI,self.keyField)==cValue) I get to the query and get the following message: Invalid column expression 'schema.BILLS object at 0x29DB7330' Any help would be appreciated. --~--~-~--~~~---~--~~ 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: InvalidRequestError: The transaction is inactive due to a rollback... using sqlite with multiple commits.
how is it that you know this is due to the SQLite timeout ? did you create a test case ? creating a fully reproducible test case would be the next step. On Apr 29, 2009, at 5:15 PM, Daniel wrote: I'm not catching it or re-raising it. Where else could I look to solve this. On Apr 29, 11:57 am, Michael Bayer mike...@zzzcomputing.com wrote: Daniel wrote: I've learned a bit more. Apparently the sqlite database occasionally gets locked by another process and that lock lasts longer than the five second default timeout. SQL Alchemy quietly issues a ROLLBACK, but doesn't say anything more about it. The result is that the session is no longer active and eventually produces the error I mentioned above. Here are the log messages showing the insert failing after the five second default timeout. 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 INSERT INTO table (id, value) VALUES (?, ?) 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 [1, 'my value'] 2009-04-28 15:10:53,148 INFO sqlalchemy.engine.base.Engine.0x...ae70 ROLLBACK Is there some way to configure SQL Alchemy to raise an exception when it issues the rollback, or when it finds the database locked? SQLAlchemy definitely raises an exception if the DBAPI sends one. a ROLLBACK does not occur if there was no exception thrown. check if you're catching it and not re-raising. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
