[sqlalchemy] Inexplicable NoResultFound error
Hi, I have a script that's basically been running unmodified for years. In it, it performs an SQLAlchemy query that does a simple join between two tables: platePointing = session.query(PlatePointing).join(Plate).filter(Plate.plate_id==plateid).one() Suddenly, I'm getting a NoResultFound error on this. I've double checked the values in the database; the row - a single matching row - is in fact there. I turned on echo to output the raw SQL queries. I copied and pasted the query that SQLAchemy generated into the database, and sure enough it finds the row. The same script/query finds the appropriate row for some values but no others. Since the raw SQL query generated by the line above finds the row but it fails in the script, I'm at a loss on how to proceed to debug this. Any suggestions would be appreciated! Cheers, Demitri -- 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] Self-join and autoload
On Mar 28, 2014, at 6:40 AM, Simon King si...@simonking.org.uk wrote:
The alternative is to define the children relationship after the
class has been defined:
class PhotoObj(Base):
__tablename__ = 'photoobj'
__table_args__ = {'autoload':True, 'schema':'sdssphoto'}
PhotoObj.children = relationship(PhotoObj, backref=backref('parent',
remote_side=[PhotoObj.pk]))
Yes, that works perfectly. Odd - that was one of my permutations at one point!
I must have had something else going on.
Thanks!
Demitri
--
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] Self-join and autoload
Hi,
I'm trying to configure a table with autoload but can't quite get the syntax to
set up a self-relationship. This is my abbreviated) schema:
CREATE TABLE sdssphoto.photoobj
(
pk bigint NOT NULL DEFAULT nextval('photoobj_pk_seq'::regclass),
parent_photoobj_pk bigint
CONSTRAINT photoobj_pk PRIMARY KEY (pk),
CONSTRAINT parent_fk FOREIGN KEY (parent_photoobj_pk)
REFERENCES sdssphoto.photoobj (pk) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
and my code:
class PhotoObj(Base):
__tablename__ = 'photoobj'
__table_args__ = {'autoload':True, 'schema':'sdssphoto'}
children = relationship('PhotoObj', backref=backref('parent',
remote_side=[PhotoObj.pk]))
The error I get is NameError: name 'PhotoObj' is not defined. I've tried many
iterations, but can't quite seem to get this right. Any suggestions would be
appreciated!
Cheers,
Demitri
--
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] Foreign key reflection error?
Hello, Thanks Mike for the comments. Before I answer the questions you asked, I want to note I found a workaround without making any changes to the database-- I just reversed the tables in the definition. At first I was using: Survey.bossSpectrumHeaders = relationship(BOSSSpectrumHeader, backref=survey) Changing this to the following worked: BOSSSpectrumHeader.survey = relationship(Survey, backref=bossSpectrumHeaders) I'm not really sure how to interpret that. On Nov 3, 2011, at 6:25 PM, Michael Bayer wrote: two things I notice, first why using extend_existing - suggests theres more going on here. I'm not wholly sure why that is in place; my colleague wrote that part. Also are you certain the foreign key from boss.spectrum_header points to the platedb.schema table and not another schema table elsewhere ? Yes, that table name is unique across all schemas. Yet another thing, when you reflect the foreign key from spectrum_header, it may not be coming back with platedb as the schema since you appear to be referring to the remote table using the implicit search path. SQLAlchemy may not be matching that up like you expect. There was an issue regarding this which was fixed in 0.7.3, another user relying upon a long search path. I do have a long search path. Do you get different results using 0.7.2 ? No, the first thing I did when I got this error was upgrade to 0.7.3. can you try defining your foreign key constraints in PG consistently with regards to how you're using schemas in your model ? (i.e. either the FK is to platedb.schema in PG, or remove the platedb schema from Survey). I was reading the descriptions from PGAdmin3 - apparently they drop the schema in the display when the table is on the search path. There's nothing that I can do to further put the table in the schema, as it were. I hope that knowing that reversing the order works helps to point to the problem...? Cheers, Demitri -- 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] Foreign key reflection error?
Hi,
I'm getting the following error with SQLAlchemy 0.7.3:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Survey.bossSpectrumHeaders. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed
as well.
I am relating two tables named Survey and BOSSSpectrumHeader. The former is
in a schema called platedb and the latter in another schema called boss.
The latter table has numerous foreign keys from the platedb schema, but and
each fail if I comment out the previous one. The search path is platedb,
shared, boss, photofield, twomass, public. The python code is:
class BOSSSpectrumHeader(Base):
__tablename__ = 'spectrum_header'
__table_args__ = {'autoload' : True, 'schema' : 'boss', 'extend_existing' :
True}
class Survey(Base):
__tablename__ = 'survey'
__table_args__ = {'autoload' : True, 'schema' : 'platedb'}
Survey.bossSpectrumHeaders = relationship(BOSSSpectrumHeader, backref=survey)
Finally, the SQL definitions of the tables are pasted below. Is there something
I am missing? Why is the foreign key not being retrieved via reflection?
Virtually everything else (including cross-schema relationships) is working
fine.
Thanks for any help!
Cheers,
Demitri
---
CREATE TABLE boss.spectrum_header
(
pk integer NOT NULL DEFAULT nextval('spectrum_header_pk_seq'::regclass),
...
survey_pk integer NOT NULL,
CONSTRAINT boss_spectrum_header_pk PRIMARY KEY (pk ),
CONSTRAINT survey_fk FOREIGN KEY (survey_pk)
REFERENCES survey (pk) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
CREATE TABLE survey
(
pk serial NOT NULL,
label text,
CONSTRAINT survey_pk PRIMARY KEY (pk ),
CONSTRAINT survey_label_uniq UNIQUE (label )
)
--
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.
Re: [sqlalchemy] Exclusive SELECT?
Hi M,
Thanks very much for your help. Adding .with_lockmode('update') to my
session.query statement worked like a charm!
Now I just need to figure out how to catch exceptions that occur in the work
unit in the thread, but that's a topic for another list...
Cheers,
Demitri
--
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] Exclusive SELECT?
Hi, I'm working on a script using SQLAlchemy against a PostgreSQL database and using Python's multiprocessing. The pattern is for each thread to: - start a transaction (session.begin()) - retrieve the next row in table X that has not yet been processed - set a being_processed flag in the row so no other query will return it - close the transaction (session.commit()) The rest of the thread then performs the work, saves the results back, and cleans up. This is all working well except for one thing - multiple threads are retrieving the same row. My first solution was to pull the work above into the main thread, but this won't work as I'd like to run this same script on *multiple* machines - the database needs to be the gatekeeper. Does anyone have any suggestions on how I can make a database-wide exclusive transaction through SQLAlchemy (or otherwise!)? I'm using SQLAlchemy 0.6.7. Cheers, Demitri -- 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] In-memory sqlite database to blob?
Hi, I'd like to write a script that creates an in-memory SQLite database via SQLAlchemy, but when I've finished with it I'd like to upload it as a file to a server, preferably without ever creating a temporary file on the client side. Is this possible? Cheers, Demitri -- 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.
Re: [sqlalchemy] In-memory sqlite database to blob?
Thanks for the pointers. I'l probably just write it to a file initially to keep it simple! Cheers, Demitri -- 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] searching for new objects not yet in database
Hi, I just want to check on something. Let's say I've got a script that's populating a database and will commit the transaction at the end. It looks for a particular object (let's call it A), and if NoResultFound it creates a new object and does a session.add(A). What if in a later iteration the script (before commit), I look up A again. The query seems to not find the object in the database (of course), but not the session either. The 'solution' is to keep track of new objects of that type I create and look in that list before attempting to create a new one. This doesn't seem elegant. Ideally I would have thought that a session.query()... would have found the object newly added into the session. Am I missing something or is there a more elegant way to handle this? In this case, I'm ok creating a nested session and saving the object directly to the database so it's available for future queries. What's the best practice method to do this? Cheers, Demitri -- 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] searching for new objects not yet in database
Thanks for the quick reply! On Nov 12, 2010, at 7:41 PM, Michael Bayer wrote: mm, right there that's not the default behavior. If you did an add(A), the next query() you do will autoflush. A is now in the database within the scope of the current transaction, so query() will find it. Yes, I have autoflush=False. A typical script for me is to load a batch of files into our database. If there is any problem with processing any of the files, I want the commit to fail - I don't want a partial import. I'd rather fix the problem and do another batch import (because I don't want to write a bunch of code checking how much was written and where to pick up from). I definitely don't want a query() to be a write operation. Or is this a case where a nested transaction would be appropriate? Cheers, Demitri -- 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] Using the declarative base across projects
Hi,
Just wanted to say thanks to those who helped me with this. Simon's solution
was exactly what I was looking for (though I have to admit I don't exactly
understand *how* it works!). But that's no longer an SQLAlchemy question...
Cheers,
Demitri
On Jul 8, 2010, at 5:49 AM, King Simon-NFHD78 wrote:
In general, you don't need a database connection just to define your
tables and mappers. The 'bind' parameter to DeclarativeBase is optional,
and only necessary if you are using autoloading. So one solution to your
problem would be not to use autoloading, and bind to a database at the
Session level rather than the Mapper level. That would be the usual way
to use the same set of classes against multiple databases.
If you really need to use autoloading, you could move all your class
definitions into a function that accepts a database engine as a
parameter. For example:
#
# ModelClasses.py
class Namespace(object):
def __init__(self, **kwargs):
self.__dict__.update(kwargs)
def initdb(connection_string):
engine = create_engine(connection_string)
Base = declarative_base(bind=engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload': True}
return Namespace(Base=Base,
Table1=Table1)
# or, you could be lazy:
# return Namespace(**locals())
# MainScript1.py
import ModelClasses
db = ModelClasses.initdb(my_connection_string)
# access db.Table1, db.Base etc.
Hope that helps,
Simon
--
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] Lazy load on a column basis?
Hi, Is lazy loading supported on a column by column basis, or only through relationships? Cheers, Demitri -- 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: Unknown Issue Causing Extremely Slow Query
On Jul 19, 2010, at 4:08 PM, Michael Bayer wrote: so its going to be doing that somewhat inefficient isinstance(list) thing you see below, this appears to be how it handles arrays of arbitrary numbers of dimensions. This could be optimized if the ARRAY type accepted some clues as to how many dimensions are present. There is a function in postgresql called array_dims that returns the array dimensions - could this be of some use? It could query the database dynamically to get the number of values. Seems a little suspicious that the slowdown would be 8000% though. We have four columns in that table of type numeric[]. So that query returns 1000 rows x 4 numeric[] columns of 4634 values in each array. (I work with Adrian.) Cheers, Demitri -- 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: Unknown Issue Causing Extremely Slow Query
Hi Michael, Assuming I understood you correctly, I tried the code below. The result was the same (the query took 486 seconds). Since I autoload everything, I first adjust the column types to the class you defined. Did I misunderstand something? Thanks again for your help. Cheers, Demitri --- from sqlalchemy.dialects import postgresql from sqlalchemy.schema import Column from sqlalchemy.types import Numeric ... class ARRAY(postgresql.ARRAY): def bind_processor(self, dialect): return None def result_processor(self, dialect, coltype): return None for c in Spectrum.__table__.get_children(): if type(c) is Column: if c.name in [values, inv_var, and_mask, or_mask]: # numeric[] columns c.type = ARRAY(Numeric) print c.name spPlateFilename = spPlate-3586-55181.fits spectra = session.query(Spectrum).join(SpectrumHeader).\ filter(SpectrumHeader.filename == spPlateFilename).all() -- 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: Unknown Issue Causing Extremely Slow Query
Hi,
Pasted below is a profile of the earlier code posted. I did update it with your
new definition of ARRAY Michael, but that only shaved off 18 seconds (down to
468s total) when run without the profiler.
The large number of __new__ calls roughly tracks with the number of numeric
values coming back from the database - 1000 rows x 4 columns x 4634 per array,
or 18,536,000 new objects. It looks like Conor's suggestion that the bottleneck
is the str-to-Decimal conversion is correct. (This would explain why our
initial imports were taking an extremely long time as well...).
I intend to use lazy loading on these columns, but before that I'm certainly
open to any suggestions to cut the time down significantly! We'll also look
into psycopg2's Decimal class.
If the type in the database is numeric[], how can I retrieve it as a string?
Thanks again for the help!
Cheers,
Demitri
Mon Jul 19 20:29:16 2010prof_stats
194856576 function calls (194856471 primitive calls) in 538.608 CPU
seconds
Ordered by: cumulative time
List reduced from 460 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
11.9681.968 538.608 538.608 string:1(module)
10.0000.000 536.430 536.430 ./examples/db_test.py:56(main)
10.0360.036 536.414 536.414
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1447(all)
10010.0010.000 530.8940.530
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1619(instances)
10.0000.000 529.213 529.213
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:2379(fetchall)
10.0000.000 529.211 529.211
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:2360(_fetchall_impl)
1 31.366 31.366 529.211 529.211 {method 'fetchall' of
'psycopg2._psycopg.cursor' objects}
18538000 124.7840.000 497.8450.000
/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py:511(__new__)
18551033 290.3530.000 290.3530.000 {built-in method __new__ of type
object at 0xf6360}
18538021 45.5680.000 45.5680.000 {built-in method match}
74152000 17.2190.000 17.2190.000 {built-in method group}
185386309.9080.0009.9080.000 {isinstance}
185380006.3560.0006.3560.000 {method 'lstrip' of 'str' objects}
10.0000.0005.4845.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1560(__iter__)
10.0000.0005.4845.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1567(_execute_and_instances)
10.0000.0005.4845.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py:703(execute)
10.0000.0005.4835.483
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1148(execute)
10.0000.0005.4835.483
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1212(_execute_clauseelement)
10.0000.0005.4825.482
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1255(__execute_context)
10.0000.0005.4825.482
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1351(_cursor_execute)
Time spent within each function
Mon Jul 19 20:29:16 2010prof_stats
194856576 function calls (194856471 primitive calls) in 538.608 CPU
seconds
Ordered by: internal time
List reduced from 460 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
18551033 290.3530.000 290.3530.000 {built-in method __new__ of type
object at 0xf6360}
18538000 124.7840.000 497.8450.000
/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py:511(__new__)
18538021 45.5680.000 45.5680.000 {built-in method match}
1 31.366 31.366 529.211 529.211 {method 'fetchall' of
'psycopg2._psycopg.cursor' objects}
74152000 17.2190.000 17.2190.000 {built-in method group}
185386309.9080.0009.9080.000 {isinstance}
185380006.3560.0006.3560.000 {method 'lstrip' of 'str' objects}
15.4825.4825.4825.482 {method 'execute' of
'psycopg2._psycopg.cursor' objects}
185380162.8520.0002.8520.000 {method 'strip' of 'str' objects}
11.9681.968 538.608 538.608 string:1(module)
92701451.4870.0001.4870.000 {len}
40000.6340.0000.6340.000
[sqlalchemy] Using the declarative base across projects
Hi,
I have a question that I can't find a satisfactory answer to. Apologies in
advance if it's more of a Python question, but it's possible that there is a SA
solution.
I have a project that defines a database connection and classes based on
database tables. A script that uses these classes would start with:
import project.DatabaseConnection as db # create_engine() and metadata defined
here
import project.ModelClasses
In ModelClasses, I define all of the table classes using:
Base = declarative_base(bind=db.engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload' : True} # requires metadata to work
This is fine. I have a second project that also defines a different set of
tables that I want to use, so the main script would then:
import project2.ModelClasses # a different set of tables
If it's not clear how the db parameter (the database connection) was defined
in ModelClasses, well, that's my problem. I can't pass a parameter to an import
statement of course. The DatabaseConnection class defines the engine and
metadata, and now I need to use these objects to generate my base class. How
can I pass this object around? Should I be using a different model?
The python way seems to be to create a config class, but
project2.ModelClasses won't know anything about it if it's defined in the first
project. As to the reason why there are two separate projects, consider the
case where one set of tables is one logical group, and the second is a
replicated copy from another server. I can't merge all of these projects since
they really are independent units, but sometimes I will link them (as above).
Any advice would be greatly appreciated!
Cheers,
Demitri
--
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] Using the declarative base across projects
Hi Lance, Thanks for your comments. On Jul 7, 2010, at 12:28 PM, Lance Edgar wrote: Why not just do this in project2 ? import project.DatabaseConnection as db Base = declarative_base(bind=db.engine) # ... etc. The DatabaseConnection class contains the particulars of the connection (i.e. host, username, password) which can be different. I don't want to create dependencies between the projects, I just want to reuse the class definitions. I want to create the DatabaseConnection once and pass it into the definition of the classes. Another approach I tried was to make ModelClasses an object and define the classes in a method there (so I could just pass the Base class to it), but the class definitions were in the wrong namespace. The python way seems to be to create a config class, but project2.ModelClasses won't know anything about it if it's defined in the first project. As to the reason why there are two separate projects, consider the case where one set of tables is one logical group, and the second is a replicated copy from another server. I can't merge all of these projects since they really are independent units, but sometimes I will link them (as above). I don't understand why project2 wouldn't know anything about it if defined in (first) project. All it needs to do is import the connection info from the project (as in above example). If the database configuration really transcends both project and project2 though, then yes it probably could be wrapped in a config module of some sort in another project; depending on the scope that may be a bit overkill. If you can consider either project or project2 to be slightly more default than the other then the db config could stay there I'd think. This is a bit tricky to explain. Imagine I have one database, and I create a project (1) to work with that database (connections, table class definitions, etc.). That is standalone (to me). I have another completely separate database (2) on another host where I do the same thing. Using replication I then create a read-only copy of database 1 in database 2, and join some of the tables. Project 2 needs to generate the classes, but use SA's Base class that is dynamically generated. Since it's dynamic, I have to create it at run time... but now I can't pass that to the definition of project 1's classes. It's that communication that I'm struggling with. Cheers, Demitri -- 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] Sessions, threads, TurboGears best practice?
Hi, I'm hoping someone can help me with properly configuring a session/ transactions for multiple uses as I haven't been able to get it quite right. I've created a python module to contain my model classes and a connection class. This module wil be imported by both single-use scripts (run, then quit), long-running background scripts (run passively while waiting for a new event), and imported into a TurboGears app. I'm using PostgreSQL 8.4, SQLAlchemy 0.5.5, and TurboGears 2.0. After getting different types of transaction errors, e.g. InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block InvalidRequestError: Can't reconnect until invalid transaction is rolled back A transaction is already begun. Use subtransactions=True sqlalchemy.exc.InvalidRequestError: A transaction is already begun. Use subtransactions=True to allow subtransactions. I settled on this in my db connection module, which is called by all my scripts: class MyDatabase: def __init__(self, database_connection_string=None): self.database_connection_string = database_connection_string self.engine = create_engine(self.database_connection_string, echo=False) self.metadata = MetaData() self.metadata.bind = self.engine try: db # singleton except NameError: db = MyDatabase() engine = db.engine metadata = db.metadata Session = scoped_session(sessionmaker(bind=engine, autocommit=True, autoflush=False)) Then in each script I import this module, create a new session: session = Session() and query like this: if session.autocommit: session.begin() do stuff that modifies the db, i.e. not just select statements session.commit() I was hoping this is fairly bullet-proof, but unfortunately my TurboGears app still hangs (with no error messages) after running for some period of time, though I think it hangs when multiple queries hit the database at the same time. I'm suspecting some kind of deadlock, but that's just a guess. I'm at a loss as to where the problem lies, but I wanted to check with the experts here that I'm using SA appropriately. Any help/suggestions greatly appreciated. Cheers, Demitri --~--~-~--~~~---~--~~ 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: Defining custom types
Hi Michael, Thanks for clarifying that for me. For anyone interested, this is what I ended up with: from sqlalchemy import String sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fibertype'] = String elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fibertype'] = String Cheers, Demitri On 15 Oct 2009, at 16:06, Michael Bayer wrote: thatsanicehatyouh...@mac.com wrote: Hello, On 12 Oct 2009, at 21:04, Michael Bayer wrote: On Oct 12, 2009, at 4:26 PM, thatsanicehatyouh...@mac.com wrote: Hello, I have a custom type defined in my postgresql database, and this is giving me the warning: /Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/ site- packages/sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'fibertype' of column 'fiber_type' self.dialect.reflecttable(conn, table, include_columns) From an earlier email from Michael (13 September 2009), I saw that I can do something like this: sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fiber_type'] = fibertype elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fiber_type'] = fibertype But of course fibertype needs to be defined. How can I define this? The definition in the database is simply: CREATE TYPE fibertype AS ENUM ( 'A', 'B', 'C' ); what happens if you put fibertype: FiberType in the dictionary as well ? all its doing is looking in there for something to use. I'm not sure what you mean here. I take this to mean: postgres.ischema_names['fibertype'] = FiberType but in either case, the value of the dictionary here is undefined. That's the thing I'm unsure of how it's to be defined. Do you mean that it should simply be any value, e.g. postgres.ischema_names['fibertype'] = FiberType ah no it should be either a default type like String or a custom type like a TypeEngine or TypeDecorator subclass of your design. --~--~-~--~~~---~--~~ 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] Defining custom types
Hello, I have a custom type defined in my postgresql database, and this is giving me the warning: /Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site- packages/sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'fibertype' of column 'fiber_type' self.dialect.reflecttable(conn, table, include_columns) From an earlier email from Michael (13 September 2009), I saw that I can do something like this: sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fiber_type'] = fibertype elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fiber_type'] = fibertype But of course fibertype needs to be defined. How can I define this? The definition in the database is simply: CREATE TYPE fibertype AS ENUM ( 'A', 'B', 'C' ); Cheers, Demitri --~--~-~--~~~---~--~~ 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: Remove need for session in method?
Hi Tomasz, Thanks for your help - that solved the problem. On 26 Sep 2009, at 10:20, Tomasz Jezierski - Tefnet wrote: [...code snipped...] First... why don't you define this method with class definition? I don't use reflection but I think that it is possible... It's a chicken-and-the-egg problem. I can't use the other classes (that I include in the join statement, for example), until they are defined, but as many tables are interrelated, that's not possible to do up front. Unless there is a way to do it that I'm missing. Second... you could probably convert it to column_property I'm not sure how I would do that... And back to your question.. 1. you can have globally accessed session (like it is done in Pylons via meta.Session) http://wiki.pylonshq.com/display/pylonsdocs/Using+SQLAlchemy+with+Pylons Not a bad idea, but the class is being used by many scripts as well as a TurboGears app, so I can't make that assumption. 2. you can get session for instance using sqlalchemy.orm.session.Session.object_session http://www.sqlalchemy.org/docs/05/reference/orm/sessions.html?highlight=session#sqlalchemy.orm.session.Session.object_session This is exactly the method I was looking for and have used. I expected it to be in the Base class and didn't think to look in Session. 3. You can have session-aware class http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SessionAwareMapper Thanks for that link. Unfortunately, my eyes kind of glazed over when I tried to go through it... Cheers, Demitri --~--~-~--~~~---~--~~ 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] Custom attribute in table class
Hi,
I'm trying to create a custom/derived attribute on a table class. This
is what I have defined:
class Plate(Base):
__tablename__ = 'plate'
__table_args__ = {'autoload' : True}
class Design(Base):
__tablename__ = 'design'
__table_args__ = {'autoload' : True}
class Pointing(Base):
__tablename__ = 'design'
__table_args__ = {'autoload' : True}
I then map these together:
Plate.design= relation(Design, primaryjoin=Plate.design_pk ==
Design.pk, backref=plate)
Design.pointings = relation(Pointing,
primaryjoin=Design.pk ==
Pointing.design_pk,
backref=design)
(So a plate has one design, and a design can have multiple pointings.
Sorry for the jargon.)
All of this works. I'd like to be able to write a convenience method
to access data in plugging directly from plate, for example,
instead of doing:
plate.design.pointings[0].center_ra
I'd like to be able to say:
plate.ra
How can I create this attribute? I've tried something like:
Plate.ra = column_property(Plate.__table__.design.pointings
[0].center_ra)
but I get:
AttributeError: 'Table' object has no attribute 'design'
For extra credit, is it possible to write a method on Plate that I can
use like this?
plugging_no = 0
plate.ra(plugging_no)
It's easy to do these things in the Plate class definition, but at the
time I define it the other classes/tables are not defined (nor the
mappings).
Cheers,
Demitri
--~--~-~--~~~---~--~~
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: Deleting multiple objects
On 19 Aug 2009, at 18:32, Mike Conley wrote: The delete method of query supports bulk deletes. In your case it might be something like session.query(Users).filter(User.officeid==office.id).delete() Any query can be used; there are probably more elegant ways to take advantage of the relation 'users' also. Make sure you read the documentation for Query.delete() to understand the impact of the 'synchronize_session' keyword Thanks for the tip - I wasn't aware of query's delete method. Still, it strikes me as a bit inefficient to look up objects that I already have at hand. (Even if the engine has to look them up due to lazy loading, *I* have them at hand.) Ah well. Demitri --~--~-~--~~~---~--~~ 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] Deleting multiple objects
Hello, I have several objects from a relation that I'd like to delete. To me, this would be a natural syntax: session.delete(office.users) to delete all of the user objects. I get this error when doing this: raise exc.UnmappedInstanceError(instance) sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.orm.collections.InstrumentedList' is not mapped Took me a while to figure out that delete() didn't work like that. I use this instead: [session.delete(x) for x in office.users] Can session.delete() be made to work as above (or at the very least produce a better error message)? Cheers, Demitri --~--~-~--~~~---~--~~ 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] join + filter
Hi, I'm circling around an answer for a problem but am not quite getting it. I have two tables: Plugging and Cartridge. Plugging has a to-one relation to Cartridge, and the inverse relation is to-many. Cartridge has a field called number and plugging has a field called active. I want to build a query to retrieve a single plugging, and this works for me: plugging, cart = session.query(Plugging, Cartridge).filter(Cartridge.number == cartNo).filter(Plugging.cartridge_pk == Cartridge.pk).filter(Plugging.active == True).one() This seems overly complicated - I shouldn't need to specify the primary keys (pk) since the foreign keys are already defined (I'm using autoload with postgresql). My first attempt at a query was this: plugging = session.query(Plugging).filter(Plugging.cartridge.number == cartNo).one() ...but that turned out to be too optimistic (ignore for the moment that I left out the active filter). What is the best practice for simplifying the first query? Cheers, Demitri --~--~-~--~~~---~--~~ 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 with fields from different tables
Hi, I am doing a search across two tables for matching records, and I'm not quite sure how to work with the results of the query. For example: matches = session.query(TableA).from_statement(SELECT tableA.id, tableB.id FROM tableA, tableB WHERE etc) To me this doesn't really fit the model. The query(TableA) part isn't right since I'm not returning rows of TableA. Is there a way I can create arbitrary queries like this? Cheers, Demitri --~--~-~--~~~---~--~~ 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: Testing the state of an object in a session.
Hi Michael, On 1 May 2009, at 20:50, Michael Bayer wrote: obj in session.new obj in session.dirty obj in session Cheers for that. I was actually on the right page in the documentation when I was looking for that. http://www.sqlalchemy.org/docs/05/reference/orm/sessions.html?highlight=session#sqlalchemy.orm.session.Session A modest suggestion - it might be useful to mention these methods right below the paragraph there where the terms transient, pending, etc. are introduced. Thanks again, Demitri On May 1, 2009, at 6:55 PM, thatsanicehatyouh...@mac.com wrote: Given a session object, is there a way to test if an object in the session is pending (or for that matter, transient, persistent, or detached)? --~--~-~--~~~---~--~~ 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] Testing the state of an object in a session.
Hi, Given a session object, is there a way to test if an object in the session is pending (or for that matter, transient, persistent, or detached)? Thanks! Demitri --~--~-~--~~~---~--~~ 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: Auto-generating class definitions
Hi Huy, Thanks for your comments! On 22 Apr 2009, at 05:33, huy wrote: I guess it depends if you are going the standard SA table/mapping way or the sqlalchemy.ext.declarative way So this is a good question to ask. As I'm just starting out with SA, I have no legacy code to update, and thus have started with the 0.5.x version where the documentation recommends using the declarative way. I don't know how this differs from the old way (besides which base class to use). What is the difference, and should I be using that? - I want to use reflection whenever possible. Not sure exactly which reflection you want here but my experience with a database of about 30+ tables, it's so much faster for development to have a static SA table definitions file then to have it reflect on every server reload. Of course - I want to generate static definitions in a single file, and then import that file from my python scripts. What I'm trying to avoid is when I make modifications to my db schema that I don't have to tune the class/table definitions by hand in this file. - I want to create a second python script that will contain one class definition for each in the first file. For example, let's say I have a table called plate. The first file will contain the full definition for a class called Plate_. The second file might contain: class Plate(Plate_): pass The second definition is a subclass of the first where I can put custom logic (if I need any) for each class. This is the class I will use in my scripts. I will then import this file from the many scripts I need to write that use this database. Just wondering why you need both ? (unless you are going the sqlalchemy.ext.declarative way.) Let's say here the Plate class represents the table plate in the database. I want to write some custom logic into the Plate class, for example a method is_finished. This is not a field in the table, but a calculation that could depend on both data from the db and external information passed to it at runtime. The problem comes when I want to regenerate the static db definitions - my custom (non-database) definitions would be overwritten. By keeping them separate, I can regenerate the static definitions any time. I'm not sure of the meaning of your second remark about using the declarative method? How does this change things in what I'm trying to do? If you are using the standard table/mapping, your model classes don't have to extend explicitly an SA base class. Also, SA can work with a simple class definition like class Plate(object): pass and it auto injects everything itself, when you do the mapping. It's not like java where you generate setters and getters for every database column. That's definitely nice. The WebObjects code that was generated did create all of the setters and getters (which I see I don't need), but also defined all the relationships between the objects. I think this was the part that the latest autocode was missing. Then my subclass of that object would contain my custom logic. I'm a little unclear about that object definition above - how does this declaration talk to SA? I think it's good to do some things manually when you first start out. Heck, it's not really that much code. I guess you come from WO which generated pretty much everything. Is that a bad thing? :) If a program can do it, I don't want to! My mindset is that I'd really prefer to be able to get up and running very quickly to be able to do the most common stuff, and only have to dig into the specifics when I need to optimise or do something unusual. I've been doing DBI programming in Perl for a long time, but this just thinly wrapped SQL. SA is really promising to remove the tedious SQL coding, but I think it can be made a bit easier (much like Elixir has done). Thanks again for taking to the time respond to my questions (and if you've got this far, reading through all this!). I appreciate the help. I will take a closer look at autocode to see if it can't do what I need with minimal patching. Cheers, Demitri --~--~-~--~~~---~--~~ 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] Auto-generating class definitions
Hello, I'm trying to set up database access using SQLAlchemy, and wanted to get some advice from the experts on how to proceed. I'm using PostgreSQL as my database (shouldn't be important, I know) and will specify foreign keys in the database. I do not want to create or modify database tables via python - I will use other tools for that. This is what I'd like to do: - Have a script that will connect to the database, read the schema, and generate a single file. This will be a python script that generates the definitions of all the classes, relationships, etc. - I want to use reflection whenever possible. - I want this output file to be considered read only such that when I modify the database, I can rerun the script and regenerate it with the changes. - I want to create a second python script that will contain one class definition for each in the first file. For example, let's say I have a table called plate. The first file will contain the full definition for a class called Plate_. The second file might contain: class Plate(Plate_): pass The second definition is a subclass of the first where I can put custom logic (if I need any) for each class. This is the class I will use in my scripts. I will then import this file from the many scripts I need to write that use this database. (If anyone here has used WebObjects they'll know what I'm trying to do here.) I've looked at Elixir, SQLSoup, and sqlautocode. I don't think any of these do quite what I'm trying to do here. I've only started to play with SQLAlchemy, and I'm a little frustrated at how much code I need to write to start using it, code that can certainly (and should) be generated. I'd appreciate any advice on how best to approach this. Also, please let me know if I'm missing something from existing tools. Cheers, Demitri --~--~-~--~~~---~--~~ 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] Mapping and reflection question
Hello,
I have a question about SQLAlchemy (well, I have a lot of questions,
but I'll try to space them out a bit!). I'm very new to it (and
python) but not databases and ORMs.
I like that I can use reflection to define tables, and I really want
to use that since I don't want to update python classes when I modify
a table. I am defining my table like this:
metadata = MetaData()
metadata.bind = engine
platesTable = Table('plate', metadata, autoload=True)
Next, I want to map my plate table to a lookup table:
surveyTable = Table('survey', metadata)
x = sql.join(platesTable, surveyTable, platesTable.c.survey_pk ==
surveyTable.c.pk)
mapper(??plate class??, x, properties={'pk':[platesTable.c.survey_pk,
surveyTable.c.pk]})
The problem is that I don't know what to put in for the plate class
since I used reflection and haven't defined one. Does this mean that I
have to define the class by hand and can't have it done automatically?
Can the autoload figure these relationships out?
Thanks in advance for any help!
Cheers,
Demitri
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
