RE: [sqlalchemy] flush(), save(), delete()
Jo wrote: [SNIP] and- In [13]: aa=Anagrafica.get(111) In [14]: aa.delete() In [15]: aa.flush() - but in version 0.6 I can't find flush(), save(), delete(). Where are them? thank you j These methods were added to your objects by the old assign_mapper extension. This extension no longer exists, and the methods on the Session should be used instead. For example, instead of aa.delete(), you would say session.delete(aa). If you want to preserve your old API, you could create a base class for your mapped objects that implements each of the old methods. A delete method might look like this (untested): class Base(object): def _get_session(self): return sqlalchemy.orm.object_session(self) def delete(self): session = self._get_session() session.delete(self) The flush method would correspond to session.flush([self]), but you should read the deprecation warning about passing a list of objects at http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or m.session.Session.flush. Assuming that the save() method adds the object to the current contextual (scoped) session, it would be as simple as: def save(self): session = Session() session.add(self) However, I personally wouldn't add that one, as it ties your class to the scoped session mechanism which may not always be what you want. 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.
RE: [sqlalchemy] further restricting a query provided as raw sql
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mariano Mara Sent: 15 April 2010 16:20 To: sqlalchemy Subject: Re: [sqlalchemy] further restricting a query provided as raw sql Excerpts from Chris Withers's message of Thu Apr 15 11:46:05 -0300 2010: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris My answer will be generic since I don't know id SA provide a better way to deal with it (I haven't face this situation yet). You will have to provide the values not as binded parameters but hardcoded instead: SELECT somestuff FROM somewhere WHERE some_date = :from_date AND some_date = :to_date AND somefield in (%s) % (,.join([str(x) for x in a])) if you don't like this kind of hack, depending on your database, you can create a temp table, insert all the values in it and join with your real table. ...and make sure that you properly escape all your values to prevent SQL injection. I guess another option would be to detect when lists are passed in, then replace ':values' with ':value0, :value1, :value2' etc. in the SQL. As long as you can be sure that the string ':values' doesn't appear anywhere else in the SQL, this wouldn't be too bad. 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.
RE: [sqlalchemy] cls._state / cls._state.get('original') class 'sqlalchemy.orm.attributes.CommittedState'
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of jose soares
Sent: 16 April 2010 11:03
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] cls._state /
cls._state.get('original') class
'sqlalchemy.orm.attributes.CommittedState'
jo wrote:
Hi all,
I cannot find anymore the attribute _state :
if (not cls._state or not cls._state.get('original') or
(cls._state['original'].data.get(k) != data.get(k:
Could someone please help me?
thank you
j
To explain better my problem, in version 0.3 my models have the
attribute _state where I find the
class 'sqlalchemy.orm.attributes.CommittedState'
(Pdb) self._state
{'original': CommittedState: {'anagrafica_dato_fiscale':
u'02241850367/02241850367', 'anagrafica_id_operatore': 1,
'anagrafica_cap': None, 'anagrafica_telefono': None,
'anagrafica_email':
None, 'anagrafica_nome': u'AZ.USL MODENA distr. PAVULLO',
'anagrafica_cod_nazione_nascita': None, 'anagrafica_localita': None,
'anagrafica_tipo': u'S', 'anagrafica_cod_cittadinanza': None,
'anagrafica_tipo_persona': u'S', 'anagrafica_ts_ultima_modifica':
datetime.datetime(2010, 3, 3, 16, 4, 22, 50891,
tzinfo=psycopg2.tz.FixedOffsetTimezone object at 0xc13488c),
'anagrafica_id': 8, 'anagrafica_data_nascita': None,
'anagrafica_id_comune_nascita': None, 'anagrafica_id_comune': 15090,
'anagrafica_cod_professione': None, 'anagrafica_indirizzo': u'?',
'anagrafica_cod_titolo_studio': None}, 'modified': False}
I can't find this attribute anymore on version 0.6
j
I haven't used this, so I don't know if this helps, but you may be
interested in a couple of the functions described at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html#attribute-util
ities
In particular, either the instance_state() function or the get_history()
function might be useful. I'm not sure there's much documentation for
either the History class or the InstanceState class so you'll have to
read the code in sqlalchemy.orm.state and sqlalchemy.orm.attributes
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.
RE: [sqlalchemy] Is the mapper must have a primary_key?
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Gaicitadie
Sent: 16 April 2010 20:06
To: sqlalchemy
Subject: [sqlalchemy] Is the mapper must have a primary_key?
#!/usr/bin/python
# -*- coding: UTF-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
tid = Column(Integer)
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = Base.metadata
metadata.create_all(engine)
[SNIP]
It seems must make a primary_key for table，but my table need't
primary_key，what can i do？
The ORM part of SQLALchemy requires that you have some combination of columns
that uniquely identify a row in the database. This is so that when you load an
instance from the database, then modify it and flush your changes back to the
database, the updates actually get applied to the correct row. It also means
that if you load rows from the same table more than once in the same Session,
you always get the same instance back for a given row. Note that the columns
don't have to actually be a primary key in the database.
If you are treating the database as read-only and you have rows in your table
which really are identical, you may be better off just using the lower-level
SQL expression language part of SA, which doesn't have these constraints.
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.
RE: [sqlalchemy] Storing Nested Lists
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of greg Sent: 25 April 2010 22:59 To: sqlalchemy Subject: [sqlalchemy] Storing Nested Lists Hi All, I'm new to sqlalchemy. I've been reading the documentation and group archives, but can't really find an answer to my question. I suspect it's a question of terminology, and that I don't really know the term for what I'm looking for. Can I map a nested list to one column, and have my nested list returned to me intact? A simple example is a list like: ['a','b','c', ['x','y','z',['m','n','o']]] If anyone can point me in the right direction, I'd much appreciate it. Thanks. If you make the column a PickleType: http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#sqlalchem y.types.PickleType ...it should behave as you want. 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.
RE: [sqlalchemy] session lifecycle and wsgi
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 28 April 2010 14:37 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] session lifecycle and wsgi Hi All, I'm still trying to get an answer on this... Am I right in understanding that the basic session lifecycle should be: try: use session session.commit() except: log() session.rollback() finally: session.remove() The structure I've traditionally used with transactions has been: try: use session except: log() session.rollback() else: session.commit() Is this okay? Why would the first setup be preferable? (ie: what's wrong with my location of the commit() call?) What happens when the remove() call is omitted()? Have you read http://www.sqlalchemy.org/docs/session.html#lifespan-of-a-contextual-ses sion - it describes typical usage of a scoped session in a web application. In your traditional structure, you could get an exception during session.commit() which would not be handled in your exception handler. I believe (but I'm not certain) that after any kind of database exception, it is recommended that you roll back the existing transaction, as it is likely to be invalid anyway. Session.remove() ensures that the current session is removed from the scoped session registry. If you don't do this, I think that the next time this thread calls Session(), it'll get the old session back again, rather than creating a new one. 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.
RE: [sqlalchemy] declarative commit hook - onCommit()?
Daniel Robbins wrote: On Wed, Apr 28, 2010 at 10:04 AM, Chris Withers ch...@simplistix.co.uk wrote: Daniel Robbins wrote: Let's say that when a database record is added or updated, I need to perform some arbitrary action (in my case, ensuring that data in other tables is consistent with what is being committed.) What mechanisms are suggested for this? Mapper extesions: http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.htm l#sqlalchemy.orm.interfaces.MapperExtension Thanks, Chris. Right now I am not defining a mapper, just a bunch of declarative classes. Can I still use MapperExtensions? The declarative docs include an example of using a MapperExtension: http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mapper-con figuration 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.
RE: [sqlalchemy] Use a foreign key mapping to get data from the other table using Python and SQLAlchemy.
Az wrote:
[SNIP]
The following code maps these classes to respective database tables.
# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
customers_table = Table('customers', metadata,
Column('uid', Integer, primary_key=True),
Column('name', String),
Column('email', String)
)
orders_table = Table('orders', metadata,
Column('item_id', Integer, primary_key=True),
Column('item_name', String),
Column('customer', Integer, ForeignKey('customers.uid'))
)
metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Orders, orders_table)
Now if I do something like:
for order in session.query(Order):
print order
I can get a list of orders in this form:
Item ID 1001: MX4000 Laser Mouse, has been ordered by customer no.
12
=
What I want to do is find out customer 12's name and email address
(which is why I used the ForeignKey into the Customer table). How
would I go about it?
=
You need to add a relationship between the two classes. This is
documented at
http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-relationship.
The documentation is using the declarative form. If you want to continue
to use the distinct table definitions followed by mapper definitions, it
would look something like this:
mapper(Orders, orders_table, properties={
'customer_object': relationship(Customer, backref='orders')
})
This will add a 'customer_object' property to the Orders class which
returns the corresponding Customer object. The backref='orders'
parameter means that the Customer object will also get an 'orders'
property which will be a list of all orders owned by the Customer.
You might find it more convenient if your existing 'customer' column was
actually called something like 'customer_id', then you could call your
relationship property 'customer' instead. If you didn't want to rename
the actual column in the database, you can still ask SQLAlchemy to use a
different name for the column, as demonstrated in
http://www.sqlalchemy.org/docs/mappers.html#customizing-column-propertie
s
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.
RE: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
Kent wrote: [SNIP] I'm fine with how SQLA is designed, it isn't really a SQLA issue, I was just appealing to you to see if you could think of a workaround I believe the problem is in the framework tools we are using, whether it is Zope or TG. (I've posted to zope group now to see if they intended to support savepoints and how, etc.). Since that framework won't allow me to issue the command session.commit(), I cannot release the savepoints until the zope transaction commits, and by then I'm getting a python max recursion problem because there are so many outstanding savepoints for it to release. From a quick reading of the 'transaction' package source, it looks like you should be able to create savepoints and roll them back something like this: savepoint = transaction.savepoint() try: # ... except: savepoint.rollback() raise I've no idea if that will confuse SA's accounting mechanisms though - I imagine you'd probably need to clean up some objects in the session. If you have a look at the tg.configuration module, the transaction middleware is added based on the config.use_transaction_manager value, so you could set that to False and implement whatever transaction management features you want in your own middleware. 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.
RE: [sqlalchemy] reflecting existing databases with no a priori knowledge of their structure
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Harry Percival
Sent: 03 June 2010 16:24
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] reflecting existing databases with no a
priori knowledge of their structure
Hi All,
I'm building a tool to extract info from databases. The
user/programmer doesn't have any advance knowledge of the structure of
the database before they load it, and i want to dynamically generate
mapped classes for the database.
i just want to check there isn't some helpful sqlalchemy stuff that
can make my life easier, cos it feels harder than it should be.
sqlsoup seems to expect you to know table names ahead of time. i can't
find a way of extracting a list of table names from db =
SqlSoup(engine) and i'm finding myself generating classes on the fly
using the type() function.
stuff like:
meta.reflect(bind=engine)
tables = meta.raw_tables
class MyTable(object):
pass
for t in tables:
tempclass =
type('Table%d'%counter,(MyTable,),{'engine':self.engine})
mapper(tempclass,t)
then i use a bunch of classfunctions hanging off MyTable to do things
like return select alls ... anyways, this feels harder than it should
be. am i missing something? or is sqlalchemy simply not really used
much to work with existing / arbitrary databases?
I'm not quite sure what you're asking for. Once you've used meta.reflect to
reflect all your tables, you can pass that metadata instance to the SqlSoup
constructor. So you now have all the table names available in MetaData.tables
(or MetaData.sorted_tables), and you can access the mapped classes via
SqlSoup.entity(table_name).
For example:
import sqlalchemy as sa
from sqlalchemy.ext.sqlsoup import SqlSoup
meta = sa.MetaData('db://user:passw...@host/database')
meta.reflect()
db = SqlSoup(meta)
for table in meta.sorted_tables:
cls = db.entity(table.name)
print cls
print cls.get(1)
Hope that helps,
Simon
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] SA on MySQL 3.23
Hi,
According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23 should be
supported in some form. However, with SA 0.6.1 and MySQL 3.23.58, I get
the following error:
import sqlalchemy as sa
e = sa.create_engine('mysql://user:passw...@host')
e.execute('select Hello World')
Traceback (most recent call last):
File stdin, line 1, in module
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1714, in execute
connection = self.contextual_connect(close_with_result=True)
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1742, in contextual_connect
self.pool.connect(),
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
157, in connect
return _ConnectionFairy(self).checkout()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
321, in __init__
rec = self._connection_record = pool.get()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
176, in get
return self.do_get()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
670, in do_get
con = self.create_connection()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
137, in create_connection
return _ConnectionRecord(self)
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
217, in __init__
l.first_connect(self.connection, self)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/strategies.py,
line 145, in first_connect
dialect.initialize(c)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/dialects/mysql/base.py,
line 1755, in initialize
default.DefaultDialect.initialize(self, connection)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
138, in initialize
self.returns_unicode_strings =
self._check_unicode_returns(connection)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
183, in _check_unicode_returns
unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60))
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
173, in check_unicode
]).compile(dialect=self)
File
/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/cursors.py, line 166, in execute
self.errorhandler(self, exc, value)
File
/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/connections.py, line 35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, You have an error in your
SQL syntax near '('test unicode returns' AS CHAR(60)) AS anon_1' at line
1)
According to http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
the CAST function was added in 4.0.2.
Is there any way that I can avoid this error? Perhaps with some engine
or dialect option that configures the returns_unicode_strings attribute
without running the test?
Thanks a lot,
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.
RE: [sqlalchemy] SA on MySQL 3.23
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer
Sent: 03 June 2010 19:38
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] SA on MySQL 3.23
On Jun 3, 2010, at 1:15 PM, King Simon-NFHD78 wrote:
Hi,
According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23
should be
supported in some form. However, with SA 0.6.1 and MySQL
3.23.58, I get
the following error:
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, You have an
error in your
SQL syntax near '('test unicode returns' AS CHAR(60)) AS
anon_1' at line
1)
According to
http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
the CAST function was added in 4.0.2.
Is there any way that I can avoid this error? Perhaps with
some engine
or dialect option that configures the
returns_unicode_strings attribute
without running the test?
heh wow, that little test we've added is proving to be quite
a PITA.OK so in this case its the CAST thats barfing ?
the options we could do here are:
1. have cast() do nothing with the MySQL dialect if the MySQL
version 4.0.2 (is there some MySQL-specific syntax that
works maybe ?)
2. have the MySQL dialect not run _check_unicode_returns if
the version 4.0.2
3. put the unicode checks in a try/except and default the
returns to False if something didn't work
since i dont have an old MySQL installed here, do you need me
to give you patches for these so you can test ?
I'll happily try any suggestions you've got :-)
I couldn't see anything in the MySQL docs that suggested an alternative
to the CAST function, so it seems reasonable to just omit it for older
MySQL servers. I applied the attached patch, and it at least allowed me
to connect to the server and issue basic queries, but I haven't done any
more testing than that.
Option 1 sounded best to me just because I didn't know if there would be
any other places that SA might implicitly run a query that included a
CAST. I suppose it changes the semantics of the query though...
I've tried to run the unit tests, but I get lots of errors and failures
that I assume are expected on such an old version of MySQL.
Cheers,
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.
mysql_no_cast.patch
Description: mysql_no_cast.patch
RE: [sqlalchemy] SA on MySQL 3.23
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: 04 June 2010 14:42 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] SA on MySQL 3.23 On Jun 4, 2010, at 6:54 AM, King Simon-NFHD78 wrote: I'll happily try any suggestions you've got :-) I couldn't see anything in the MySQL docs that suggested an alternative to the CAST function, so it seems reasonable to just omit it for older MySQL servers. I applied the attached patch, and it at least allowed me to connect to the server and issue basic queries, but I haven't done any more testing than that. Option 1 sounded best to me just because I didn't know if there would be any other places that SA might implicitly run a query that included a CAST. I suppose it changes the semantics of the query though... I've tried to run the unit tests, but I get lots of errors and failures that I assume are expected on such an old version of MySQL. its not entirely my usual style to have an operator emit nothing on a given platform instead of failing, but because this is such an old MySQL version and cast is a little bit of a crossover operator it isn't bothering me much here. I can commit your patch with an extra artificial compiler test in dialect/test_mysql.py to ensure it does what's expected; if you want to tool around with it a bit this week, let me know that we're good with it. If you're more comfortable with a version that just doesn't call _check_unicode_returns, or that catches the exception, either would be fine with me. I just sent the first thing I tried that seemed to work. I agree that silently converting CAST to nothing might mask other bugs, and so probably isn't ideal. Which would be your preference then? Catching the exception, or not calling the method in the first place? I'll make a patch for whichever you prefer and test it next week. Thanks again, 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.
RE: [sqlalchemy] help please
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Aref
Sent: 10 June 2010 02:40
To: sqlalchemy
Subject: [sqlalchemy] help please
Hello All,
I just began learning sqlalchemy and am not quite used to it yet so
please excuse my ignorance and which might be a trivial question to
some of you.
I am writing a database module and need to load a table and possibly
modify a record in the table. I can get the connection established and
everything works fine. The problem I am running into is that I do not
necessarily know the column name before hand to code it in the update
method. I want to be able to find out to send a generic column name
which will be updated (gets the column name dynamically).
I tried the following:
columns=['ProjectID', 'Program', 'progmanger']
test = str('table.c.'+columns[1])
update = table.update(test=='project-name', values = {test:'program'})
print update
update.execute()
I get a error when I try to run it. It does not recognize the column
for some reason even though if I print test everything seems to be OK.
I get 'project.c.Program'
Is there something I am missing here? How can I send the project and
column name to the update method dynamically?
Thank you so much in advance for any help or insight you
could provide.
The table.c object supports dictionary-style access, so you should be
able to use something like this:
colname = 'Program'
column = table.c[colname]
update = table.update(column=='project-name', values =
{test:'program'})
However, in general, if you want to get a named attribute of an object,
and the name is stored in a variable, you can use Python's getattr
function. This code should also work:
colname = 'Program'
column = getattr(table.c, colname)
update = table.update(column=='project-name', values =
{test:'program'})
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.
RE: [sqlalchemy] Referential integrity actions are not doing what I want
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of exhuma.twn
Sent: 22 June 2010 14:27
To: sqlalchemy
Subject: [sqlalchemy] Referential integrity actions are not
doing what I want
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all? A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
There are various ways of configuring SA's behaviour when you delete
objects with relationships. You may want to refer to these pages in the
docs:
http://www.sqlalchemy.org/docs/mappers.html#using-passive-deletes
http://www.sqlalchemy.org/docs/session.html#cascades
http://www.sqlalchemy.org/docs/ormtutorial.html#deleting
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.
RE: [sqlalchemy] cross-database joins with MySQL
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers
Sent: 29 June 2010 10:28
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] cross-database joins with MySQL
Michael Bayer wrote:
We have engines set up like:
engine1 = create_engine('mysql://username:passw...@server/db1')
engine2 = create_engine('mysql://username:passw...@server/db2')
..and then have them bound to separate sessions, with
separate model classes mapped to them.
Now, mysql supports cross database joins, eg:
select t1.colwhatever
from db1.table1 as t1,db2.table2 as t2
where t1.something=t2.something
Is it possible to express that in SQLAlchemy, particularly
at the ORM layer with the multiple session/engine/model setup
described above?
(I suppose the case to test would be, if ModelA is bound
to engine1 and ModelB is bound to engine2, how would we do:
session.query(ModelA,ModelB,ModelA.something==ModelB.something)
...or something similar, if the above isn't possible?
its not possible across two distinct database connections,
no. Only the database can do joins, and that requires a
single connection session to do so.
Right, but how can I create an engine such that it can be
used to access
two databases? Is it as simple as setting the __tablename__ as
'db.tablename' rather than just 'tablename'?
Chris
You want the 'schema' parameter to the Table:
http://www.sqlalchemy.org/docs/metadata.html#specifying-the-schema-name
(Probably need to use __table_args__ if you are using declarative)
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.
RE: [sqlalchemy] models in different packages, often declaratively defined
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 01 July 2010 19:17 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] models in different packages, often declaratively defined Hi All, Suppose I have packageA that defines: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base) __tablename__ = 'user' ... Now, I have a packageB that defines: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Something(Base) ... I want Something to have a foreign key that points at User. How do I do that? The tables for packageA and packageB should exist in the same database (the extraction of User into packageA is just so that all our projects that need users get the same schema for the 'user' and related tables and functionality for users). I guess things could be engineered such that one MetaData instance is shared between all the bases (how would that be done though? I'd need to get the MetaData instance into each of the packages before declarative_base is called...) Moreover, how do I get all the Base's to share a _decl_class_registry? (I'm still hazy on why the information in _decl_class_registry can't go into MetaData, rather than having two registries...) Any ideas gratefully received... Chris If packageB depends on packageA, I would have packageB import the metadata or declarative Base class from packageA. Otherwise, I would create a new package, (called something like 'common'), which creates the metadata and declarative Base class. packageA and packageB would import those items from the common package. I imagine that by having a single declarative Base class, the _decl_class_registry problem will disappear. I also assume that the reason that isn't stored in the MetaData is that MetaData is an object provided by the underlying sql library, whereas 'declarative' is an extension to the ORM, and the MetaData class shouldn't know anything about it. 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.
RE: [sqlalchemy] Using the declarative base across projects
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of
thatsanicehatyouh...@mac.com
Sent: 07 July 2010 20:33
To: sqlalchemy@googlegroups.com
Subject: 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
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.
RE: [sqlalchemy] Comparable properties
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 08 July 2010 09:28 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Comparable properties Oliver Beattie wrote: @property def is_visible(self): return (self.enabled and not self.is_deleted) This can clearly be mapped quite easily to SQL expression `Klass.enabled == True Klass.is_deleted == False` You could always add a class-level attribute that stored this... @property def is_visible(self): return (self.enabled and not self.is_deleted) visible = enabled==True is_deleted==False You may need to wrap that into a method with the classproperty decorator... But, it'd be nice to have one attribute of the object fulfil both roles, and I don't know how to do that :-S Chris I think the 'Derived Attributes' example does what you want: http://www.sqlalchemy.org/docs/examples.html#module-derived_attributes http://www.sqlalchemy.org/trac/browser/examples/derived_attributes/attri butes.py As far as I can tell, it uses some Python descriptor magic to allow your property to work both at the instance and at the class level (so 'self' will either be the instance or the class). Accessing Klass.is_visible returns the SQL expression construct, but instance.is_visible works as normal. You'd be more restricted in what you can write inside your property definition though. For example, you can't use plain Python 'and', or assume that 'self.enabled' evaluates to True or False. I think something like this would work though: @hybrid def is_visible(self): return (self.enabled == True) (self.is_deleted == False) 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.
RE: [sqlalchemy] Problem with Joined Table inheritance
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Jules Stevenson
Sent: 13 July 2010 15:01
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Problem with Joined Table inheritance
Apologies for any stupidity, but I'm struggling with some joined table
inheritance, I have the following code:
widgets_table = sa.Table('web_widgets', meta.metadata,
sa.Column('widget_id', sa.types.Integer, primary_key=True),
sa.Column('title',sa.types.String(length=255)),
sa.Column('widget_type', sa.types.String(30), nullable=False),
sa.Column('position', sa.types.Integer),
sa.Column('page_id', sa.types.Integer,
sa.ForeignKey('web_pages.id'))
)
video_widget_table = sa.Table('web_video_widget', meta.metadata,
sa.Column('widget_id', sa.types.Integer,
sa.ForeignKey('web_widgets.widget_id'), primary_key=True),
sa.Column('teaser', sa.types.String(length=1)),
sa.Column('body',sa.types.String(length=21845)),
sa.Column('image', sa.types.String(length=256))
)
class ArkWebWidget(object):
def __init__(self):
pass
class ArkWebVideoWidget(object):
def __init__(self):
pass
orm.mapper(ArkWebWidget, widgets_table,
polymorphic_on=widgets_table.c.widget_type,
polymorphic_identity='widget'
)
orm.mapper(ArkWebVideoWidget, video_widget_table,
inherits=ArkWebWidget,
polymorphic_identity='video_widget'
)
---
However, when I run this I get an error:
...
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\__init__.py, line 818, in mapper
return Mapper(class_, local_table, *args, **params)
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\mapper.py, line 207, in __init__
self._configure_inheritance()
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\mapper.py, line 231, in _configure_inheritance
(self.class_.__name__, self.inherits.class_.__name__))
sqlalchemy.exc.ArgumentError: Class 'ArkWebVideoWidget' does
not inherit from 'A
rkWebWidget'
And I'm really not sure what I've done wrong, it seems ok based on
what is written in the docs?
Any pointers much appreciated.
Jules
I think the error message is quite explicit - you need to make your
ArkWebVideoWidget class inherit from ArkWebWidget. At the moment, it
inherits from 'object'.
http://www.sqlalchemy.org/docs/mappers.html#mapping-class-inheritance-hi
erarchies
Notice that the Manager and Engineer classes both inherit from Employee.
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.
RE: [sqlalchemy] Re: open session blocks metadata create_all method
On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha fah...@email.unc.edu wrote: Hi, When calling create_all on a metadata instance after a session has alrady been opened causes the create_all to hang, I assume because the session is blocking the create_all. Is there some way to get create_all to use the existing session, or any other graceful way around this? Thanks. I guess another option is to close and then reopen the session after the create_all has been called, but I'd prefer not to do that if possible. Puting a session.close() before the create_all fixes the problem. I assume this means that create_all doesn't work in the middle of a transaction, or something like that? You can tell meta.create_all() to use the same underlying DB connection as the session by using the session.connection() method with the 'bind' parameter to create_all(). Ie. connection = session.connection() meta.create_all(bind=connection) See the docs at http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s essions and http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche my.schema.MetaData.create_all 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.
RE: [sqlalchemy] Re: To select only some columns from some tables using session object, relation many-to-many
Alvaro Reinoso wrote:
It works out, thank you! How could I just retrieve some columns from
both tables? For example, if I try to select some columns from Item
and Channel, I get class 'sqlalchemy.util.NamedTuple' when I'd
like to get a channel type with its items:
result = session.query(Channel.title,
Item.title).join('items').filter(Item.typeItem == zeppelin/
channel).order_by(Channel.titleView).all()
I just need some values many times, I don't need to retrieve the whole
object.
Thanks in advance!
It sounds like you are looking for deferred column loading:
http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading
You can mark certain columns as not to be loaded until they are
accessed. This can be done at mapper definition time as well as at query
time.
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.
RE: [sqlalchemy] Which columns changing during orm commit?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Hipp Sent: 19 August 2010 23:39 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Which columns changing during orm commit? On 8/19/2010 5:24 AM, Chris Withers wrote: Michael Hipp wrote: SQLAlchemy seems pretty smart about updating only the changed columns in an orm object... If I have an orm object. Something changes one of the columns. Just before I commit() the session, is there a way to tell which columns will be updated vs those that are unchanged? Any way to ascertain the before/after values on those changed columns? Here's the basics: http://www.sqlalchemy.org/docs/session.html#session-attributes These examples should fill in the rest: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows http://www.sqlalchemy.org/docs/examples.html?#module-versioning Thanks. But I believe all those items deal with which orm objects (rows) are changed. I'm asking about columns within an orm object that might be changed. Did I miss something? Thanks, Michael You could use mapper.iterate_properties [1] to loop over all the properties of your object, and for each one call attributes.get_history [2] to find out if it has changed. I'm not sure if it's the best way, but it should work. The return value from get_history isn't documented, but the source is pretty simple. If you only want to know if the attribute has changed, you can call the 'has_changes' method. You can look at the 'added' and 'deleted' properties to get the before and after values. Hope that helps, Simon [1] http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm .mapper.Mapper.iterate_properties [2] http://www.sqlalchemy.org/docs/reference/orm/mapping.html#attribute-util ities -- 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] Session.merge and multiple databases
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Raf Geens Sent: 25 August 2010 16:48 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Session.merge and multiple databases On 25/08/2010 17:15, Michael Bayer wrote: On Aug 25, 2010, at 10:50 AM, Raf Geens wrote: Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces will be needed. Alternatively, you might want to look at your SQL output and see what primary key is attempting to be updated: Thanks for the quick reply. I've looked at the SQL output of the last commit and it appears to match on the correct primary key. However, the Individual has a version_id_col defined in the mapper, and it's trying to match on the wrong value there, which seems to cause the update to fail. I'll try to reproduce it in a full script. Raf The version_id_col is likely not to work - the whole point of the column is that SA adds the current version to the WHERE clause, and then checks to see if any rows were updated. If they were, the object was still at the same version that SA loaded from the database. If no rows were updated, it assumes it was because someone else modified the object and incremented the version number (hence the ConcurrentModificationError). SA increments the version number every time a change to the object is flushed to the database. So when your object is modified in B, the version number no longer matches the version in A and no rows match the criteria. I don't know how you fix this if you want to continue using the version_id_col feature - is there any chance that you could do without it (perhaps by implementing similar functionality in a SessionExtension which only gets attached to the primary session)? 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.
RE: [sqlalchemy] update a relation from its id
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of chaouche yacine Sent: 02 September 2010 11:02 To: sqlalchemy googlegroups Subject: [sqlalchemy] update a relation from its id Hello group, Suppose A has a ManyToOne relation to B (A is a child of B). I want to perform something like : a.b_id = b.id assert a.b == b How do I do this in sqlalchemy ? Hi, This is answered in the FAQ: http://www.sqlalchemy.org/trac/wiki/FAQ#Isetthefoo_idattributeonmyinsta nceto7butthefooattributeisstillNone-shouldntithaveloadedFoowithid7 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.
RE: [sqlalchemy] Re: Python's reserved keywords as column names
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Andrey Semyonov
Sent: 10 September 2010 14:35
To: sqlalchemy
Subject: [sqlalchemy] Re: Python's reserved keywords as column names
On 10 сен, 17:15, King Simon-NFHD78 simon.k...@motorola.com wrote:
Hi Andrey,
See the section in the docs 'Attribute Names for Mapped Columns':
http://www.sqlalchemy.org/docs/orm/mapper_config.html#attribute-
names-fo
r-mapped-columns
Hope that helps,
Simon
Well, this leads to the only way to map in my case named
'Declarative'. Because it would fail on
mapper(Class, table, properties = { '_from': table.c.from })
Could non-declarative way for mapping python's reserved keywords as
column names be scheduled as a bug or enhancement request ?
--
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.
The 'c' collection on a Table object allows dictionary-style access, so you
should be able to use:
mapper(Class, table, properties = { '_from': table.c['from'] })
Even if that didn't work, you could always use Python's getattr function:
mapper(Class, table, properties = { '_from': getattr(table.c, 'from') })
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.
RE: [sqlalchemy] Simple Join failing
Warwick Prince wrote:
Hi All
I have what I hope is a very simple question;
Just started experimenting with joins, so I tried a very basic test
and got a fail that I don't understand. It appears that SA is
creating bad SQL, but I'm sure it's something I'm missing.. Here's
what I did;
I have two tables. products and product_prices. There is a one to
many relationship based on Foreign Keys of Group and Code Both
tables have columns Group and Code and they are also the primary of
each.
I do this;
e = an Engine (MySQL connector)
m = MetaData(e)
prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly
# I want to build up my query generatively, so..
# Note that I'm selecting specific columns, and both sets of Foreign
Keys are in the selected columns (not that I believe I should need to
do that)
q = prod.select().with_only_columns(['products.Group',
'products.Code', 'product_prices.Group', 'product_prices.Code',
'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
q = q.join(price)
# I get this error;
ArgumentError: Can't find any foreign key relationships between
'Select object' and 'product_prices'.(They do exists BTW)
So, I remove my .with_only_columns and try again
q = prod.select()
Here you are creating a Select object (ie SELECT all columns FROM
products)
q = q.join(price)
Now you are joining that Select object with another table
ie. (SELECT all columns FROM products) JOIN price ON join condition
The extra parentheses are there because you are joining a SELECT with a
table.
Instead, you want to join the tables together:
prod.join(price)
To select from that, you can use the standalone select function:
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
xpression.select
eg.
select([products.c.Group, products.c.Code, price.c.ListPriceEx],
from_obj=[prod.join(price)])
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.
RE: [sqlalchemy] Simple Join failing
Warwick Prince wrote: Hi Simon Thanks for that - I knew it was something wrong with the approach but simply could not pick it!Back to the test bench for another go :-) Cheers Warwick P.S. OK - I have to ask - when and how (why?) do I do the .join on the query? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. 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.
RE: [sqlalchemy] In-memory object duplication
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of jln
Sent: 15 March 2011 16:37
To: sqlalchemy
Subject: [sqlalchemy] In-memory object duplication
[SNIP]
statuses = OneToMany('DocumentStatus', inverse='doc', cascade='all,
delete-orphan', order_by=['timestamp'])
So, when I create a new DocumentStatus object, Document.statuses
lists
two of them, but not actually persisted to the database. In other
words, leaving my Python shell, and starting the model from scratch,
there actually is only one child object (corroborated by squizzing
the
database directly). Here's my DocumentStatus.create() class method:
@classmethod
@logged_in
@log_input
def create(cls, doc, status, person=None, date=None):
person=validate_person(person)
if person:
status = DocumentStatus(doc=doc, status=status,
person=person, date=resolve_datetime(date))
if status:
doc.statuses.append(status)
doc.flush()
out = 'Document status created'
success = True
else:
out = 'Document status not created'
success = False
else:
out = 'Person does not exist'
success = False
log_output(out)
return success
I simply don't know why this is happening or, as I said, how to
search, intelligently, for an answer.
I don't know Elixir, but I assume that the inverse='doc' line in the
relationship sets up an SQLAlchemy backref. If so, then setting
status.doc (presumably done in DocumentStatus.__init__) will
automatically populate doc.statuses at the same time.
So when you do doc.statuses.append(status) a bit later on, you're adding
it to the list a second time.
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 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] trouble with metaclass
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of farcat
Sent: 16 March 2011 21:01
To: sqlalchemy
Subject: [sqlalchemy] trouble with metaclass
I have an error i cant figure out (likely a beginners error):
#
Base = declarative_base()
class tablemeta(DeclarativeMeta):
def __new__(mcls, name):
return DeclarativeMeta.__new__(mcls, name, (Base,), {})
def _init__(cls, name):
temp = dict()
temp[__tablename__] = _ + name
temp[id] = Column(Integer, primary_key = True)
temp[text] = Column(String(120))
DeclarativeMeta.__init__(cls, name, (Base,), temp)
if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
table1 = tablemeta(table1) #= ERROR
row1 = table1(text = detextenzo)
row2 = table1(text = detextenzoennogeenbeetje)
session.commit()
list = session.query(table1).all()
for l in list:
print str(l)
print done
#
the error is:
#
Traceback (most recent call last):
File D:\Documents\Code\NetBeans\test\temp\src\temp.py, line 33,
in
module
table1 = tablemeta(table1)
TypeError: __init__() takes exactly 4 arguments (2 given)
#
I do not understand what __init__ i am miscalling: I call
tablemeta.__init__ with 2 (1 implicit) as defined and
DeclarativeMeta.__init__ with 4 as defined?
please help ...
I'm not sure if it's the cause of your problem, but you have a typo in
tablemeta - your __init__ only has 1 underscore at the beginning...
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 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 object duplication
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Jacques Naude Sent: 17 March 2011 12:32 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] In-memory object duplication Hi, Simon Thanks for the quick response. Elixir doesn't use __init__ - there's something automatic going on there. My create(), in essence, does the job of __init__, which means you might still be hitting the nail on the head. I haven't had the time to test it out yet, but I will. (Why, though, would the double entry not be persisted to the database too?) The entry only appears once in the database because SQAlchemy works hard to ensure that a single object instance corresponds to a single row in the database. It doesn't really make sense (in the standard one-to-many model) for a particular child to appear more than once in a parent-child relationship. By default, SA uses a list as the collection implementation for relationships, and doesn't care if you add the same instance more than once. If it bothers you, you could use a set instead: http://www.sqlalchemy.org/docs/orm/collections.html#customizing-collecti on-access Simon -- 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] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key
This is just the way Python works - code inside a module is only executed when that module is imported. If you don't import myapp.models.notes, then the class definitions never get executed. One solution is to import all the sub-modules in your bootstrap.py before calling create_all. Another is importing the submodules inside the myapp/models/__init__.py Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of eric cire Sent: 30 March 2011 14:57 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key After investigating further, i have the impression that the problem occurs when models are in different modules (notes.py users.py in this case) but if the models are in the same module eg. myapp.models.__init__.py, the tables are created. I'd still like to know why this is happening because i don't intend to put al my models in the same module.. Thanks, On Wed, Mar 30, 2011 at 3:36 PM, 371c 371c@gmail.com wrote: Hi, I have the following setup: myapp.models.notes.py Note model defined here using declarative base myapp.models.users.py User model defined here using declarative base myapp.models.meta.py Base and DBSession defined here to avoid circular imports... myapp.lib.bootstrap.py Called to initialize the database with some initial data. The following is done: create an engine (sqlite:///notes.db) call Base.create_all(bind=engine) The Base class is the same for the models and the bootstrap.py module, but i still get a noreferencedtableerror... it basically doesn't create the database tables when bootstrap.py is called.. Any ideas ? Regards, -- 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. -- 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] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key
Something else must be importing those modules when run from pylons. If you really want to know how they are getting imported, stick something in the module which will raise an exception when it is imported (eg type blah blah blah at the top of the module) and look at the traceback. Cheers, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of 371c Sent: 30 March 2011 16:00 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key Actually that helps alot and i had infact resolved to and was in the process of doing so (i know that about python modules.. ;) but i might be missing something) But, why does this work in the context of an application (eg. pylons app). Basically, calling Base.create_all() in some init_db method of an application works without having to import all the modules in, say, myapp.models.__init__.py Suggestions are welcome, though i'm considering the question answered Thanks alot and Regards, On Mar 30, 4:39 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: This is just the way Python works - code inside a module is only executed when that module is imported. If you don't import myapp.models.notes, then the class definitions never get executed. One solution is to import all the sub-modules in your bootstrap.py before calling create_all. Another is importing the submodules inside the myapp/models/__init__.py Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of eric cire Sent: 30 March 2011 14:57 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key After investigating further, i have the impression that the problem occurs when models are in different modules (notes.py users.py in this case) but if the models are in the same module eg. myapp.models.__init__.py, the tables are created. I'd still like to know why this is happening because i don't intend to put al my models in the same module.. Thanks, On Wed, Mar 30, 2011 at 3:36 PM, 371c 371c@gmail.com wrote: Hi, I have the following setup: myapp.models.notes.py Note model defined here using declarative base myapp.models.users.py User model defined here using declarative base myapp.models.meta.py Base and DBSession defined here to avoid circular imports... myapp.lib.bootstrap.py Called to initialize the database with some initial data. The following is done: create an engine (sqlite:///notes.db) call Base.create_all(bind=engine) The Base class is the same for the models and the bootstrap.py module, but i still get a noreferencedtableerror... it basically doesn't create the database tables when bootstrap.py is called.. Any ideas ? Regards, -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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. -- 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] Two Objects, One Table and the inverse
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 05 April 2011 18:38 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Two Objects, One Table and the inverse On Apr 5, 2011, at 12:30 PM, Israel Ben Guilherme Fonseca wrote: Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? that's not possible with standard SQLAlchemy expression constructs anyway. Normally you'd use Address.street to get clause elements against Address.You can use hybrids to create this effect fully (see http://www.sqlalchemy.org/docs/07/orm/extensions/hybrid.html ) Out of interest, if Person and Address were standard mapped classes with a one-to-one relationship between them, could that query be made to work: session.query(Person).filter(Person.address.street==Something) I guess it would have to be equal to: session.query(Person).join(Person.address).filter(Address.street==Somet hing) In order for that to work, Person.address would have to be a smart wrapper for the Address class that adds the join condition into any attribute comparison operations. Good idea, or silly idea? (Or perhaps it already works...) Simon -- 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] Create a one-to-many relationship using association object with two foreign key primary keys
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of frankentux
Sent: 14 April 2011 14:42
To: sqlalchemy
Subject: [sqlalchemy] Create a one-to-many relationship using
association object with two foreign key primary keys
I have packages and repos. A package can be in many different repos
and a repo has many packages. I want to have an additional
relationship to capture the 'status' of a particular package in a
particular repo. This would be a many-to-many relationship with an
additional field, so I guess I have to use an Association object, as
described by the docs.
When I create a 'normal' association object, it works fine. However,
as a next step I would like to add any number of comments to the
association object - i.e. in my case (below), I would like a PackRepo
object to have any number of comments - as a classic one-to-many.
However, given that PackRepo itself has no 'id' but rather uses the
foreign key relationships to package.id and repo.id as primary keys,
I
don't know how to create the relationship to the package_repo table
when I'm building the comments_table - I can't simply say
packagerepo.id because packagerepo doesn't _have_ an id - it has two
foreign key primary keys as described above.
Any ideas of what to do?
package_table = Table('package',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
repo_table = Table('repo',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
### PROBLEM - HOW TO CREATE RELATIONSHIP TO package_repo ###
# Column('packagerepo_id', Integer, ForeignKey(### how to declare
this ###)),
Column('msg',String))
You just need to add a column to your comment_table for each key column
in the target table. Something like this:
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
Column('package_id', Integer, ForeignKey('package_repo.package_id'),
Column('repo_id', Integer, ForeignKey('package_repo.repo_id'),
Column('msg',String))
I *think* SA will automatically work out the relationship condition
based on those two foreign keys.
Hope that helps,
Simon
package_repo_table = Table('package_repo', metadata,
Column('package_id',Integer,ForeignKey('package.id'),primary_key=True
),
Column('repo_id',Integer,ForeignKey('repo.id'), primary_key=True),
Column('status',String,default='builds'))
mapper(Package, package_table, properties={
'repos':relationship(PackRepo)
})
mapper(PackRepo, pack_repo_table, properties={
'repo':relationship(Repo),
'comments': relationship(Comment)
})
mapper(Comment,comment_table)
mapper(Repo, repo_table)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: Context based execution
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of bool Sent: 15 April 2011 14:41 To: sqlalchemy Subject: [sqlalchemy] Re: Context based execution Hi, Thanks a lot. Can someone answer this question also = @compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? The implementation of the compiler extension is very short - you can see it at http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/ext/compiler.py. It looks like it modifies the target class to add _compiler_dispatcher and _compiler_dispatch attributes to it (or update them if it already has them). The SA statement compiler must look at these attributes to determine how to compile the statement. The registration happens as soon as the @compiles(Select) decorator is evaluated. If it is at module-global scope (rather than being buried inside another function), it'll happen when the module is imported. 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 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] API that allows me to do additional database operations just before insert execution for SQL Expression
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of bool Sent: 19 April 2011 14:16 To: sqlalchemy Subject: [sqlalchemy] API that allows me to do additional database operations just before insert execution for SQL Expression Is there any API that allows me do some processing (I want to do additional updates based on the insert statement) just before executing an insert statement using SQL Expression? I dont want to do this during compile time (@Compiles(Insert)) as I will be doing some database updates and this is not desirable for every compilation e.g., just a simple print should not do this additional processing. SA 0.7 generates events both at the ORM level and at the SQL level. See: http://www.sqlalchemy.org/docs/07/core/event.html http://www.sqlalchemy.org/docs/07/core/events.html For example, there is a 'before_execute' event which you could listen for, and look for INSERT clauses. If you can't upgrade to 0.7, you might be able to use a ConnectionProxy: http://www.sqlalchemy.org/docs/07/core/interfaces.html 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 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] Best design for commits?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Aviv Giladi Sent: 20 April 2011 15:53 To: sqlalchemy Subject: [sqlalchemy] Best design for commits? Hey guys, I have a Pylons back-end running on SQLAlchemy. I have a script that reads a tree of XML files from an HTTP server (it downloads an xml X, and then downloads that X's children, and then iterates the children, and so forth in recursion). Each xml file represents an SQLAlchemy model. The problem is that I have thousands of these xml's (sometimes 5000, sometimes 26000). I was able to optimize the download process with HTTP pooling, but I cannot seem to think of the best approach as to committing the models to the DB. Every time an xml file is downloaded, I create an orm object for it and add it to my session. Problem 1: some xml's will exists multiple times in the tree so I am checking that there is no duplicate insertion. Is the check in my code optimal or should I keep an indexed collection on the side and use it to check for duplicates? Problem 2: my autocommit is set to False because I don't want to commit on every add (not because its bad design, but because of performance). But I also don't want to iterate the entire tree of thousands of categories without committing at all. Therefor, I created a constant number upon which my code commits the data. Is this a good approach? What would be a good number for that? It might be important to mention that I do not know in advance how many xml's I am looking at. Here is what my pseudo-code looks like now (ignore syntax errors): count = 0 COMMIT_EVERY = 50 def recursion(parent): global count, COMMIT_EVERY pool = get_http_connection_pool(...) sub_xmls = get_sub_xmls(pool, parent) if sub_xmls == None: return for sub_xml in sub_xmls: orm_obj = MyObj(sub_xml) duplicate = Session.query(MyObj).filter(MyObj.id == orm_obj.id).first() if not duplicate: Session.add(orm_obj) count = count + 1 if count % COMMIT_EVERY == 0: Session.commit() recursion(orm_obj.id) recursion(0) I'm not sure I can comment on the overall approach, but there are a couple of things that might help you. 1. If you use Query.get rather than Query.filter, you won't actually query the database when the object already exists in the session. You'll probably need to clear the session every now and then (I don't think flush() or commit() clear it, but I could be wrong) 2. You may want to distinguish Session.flush() from Session.commit() - you could flush every N new objects, and only commit once at the very end. 3. If you know you are the only person writing to the database, consider setting expire_on_commit=False on your session. Otherwise I think accessing orm_obj.id after Session.commit() will trigger another (possibly unnecessary) query to the database. 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 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] Appending a where clause to a query
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Luka Novsak
Sent: 27 April 2011 05:32
To: sqlalchemy
Subject: [sqlalchemy] Appending a where clause to a query
The docs on Select's where() method say:
return a new select() construct with the given expression added to
its WHERE clause, joined to the existing clause via AND, if any.
Note: return a new select() construct
But this doesn't seem to happen.
This is my code:
def posts_per_dow(self, start_date=None, end_date=None):
q = select([func.date_part('isodow', t_posts.c.created_at),
func.count(t_posts.c.id)],
t_posts.c.user_id==self.id).group_by('1').order_by('1')
if start_date: q.where(t_posts.c.created_at=start_date)
if end_date: q.where(t_posts.c.created_atend_date)
Only the first where clause is actually used when I execute the query.
If I'm just going about it wrong, then how do I append a where clause
like this?
You need to store the return value of the 'where' method. eg:
if start_date:
q = q.where(t_posts.c.created_at=start_date)
if end_date:
q = q.where(t_posts.c.created_atend_date)
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 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] Re: Trying to query a relationship of a relationship
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Jules Stevenson
Sent: 09 June 2011 08:53
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Trying to query a relationship of a
relationship
Sorry, for the spamming, code typo (was trying to simplify it),
should read:
invoices = query(ArkInvoice).\
join(ArkInvoice.project).\
join(ArkProject.client).\
options(sa.orm.contains_eager(ArkInvoice.project.client)).\
filter(ArkInvoice.project.client.id == id)
I think you probably want something like this (all untested):
invoices = (session.query(ArkInvoice)
.join(ArkInvoice.project)
.join(ArkProject.client)
.filter(ArkClient.id == id)).all()
If you need contains_eager (which is purely an optimisation allowing you
to access invoice.project without a subsequent query), I think it would
look like this:
invoices = (session.query(ArkInvoice)
.join(ArkInvoice.project)
.join(ArkProject.client)
.options(contains_eager(ArkInvoice.project),
contains_eager(ArkProject.client))
.filter(ArkClient.id == id)
.all())
However, if you are actually going to be working with the client,
project and invoice objects after this query, you may find it easier to
start from the client:
client = (session.query(ArkClient)
.options(joinedload_all('projects.invoices'))
.filter(ArkClient.id == id)
.one())
After this query, you could access client.projects and
client.projects[n].invoices without further database queries.
See http://www.sqlalchemy.org/docs/orm/loading.html for a description of
joinedload_all.
I 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 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] Filtered backref
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Joril Sent: 08 June 2011 22:41 To: sqlalchemy Subject: [sqlalchemy] Filtered backref Hi everyone! Is it possible to have a many-to-one declarative relation between two classes and a _filtered_ backref? I'm trying to build a tagging system for my bloglike application, and to allow a user to apply private tags to posts of other people. My classes are: Owner Post TagAssociation Tag A Post has an Owner, while TagAssociation has a Tag, a Post and an Onwer Between TagAssociation and Post there's a many-to-one, and I'd like to configure a tags backref so that it would handle only the TagAssociations having the same Owner as the Post... Is this possible? Many thanks! The 'relationship' function takes optional primaryjoin and secondaryjoin parameters that control the join conditions for the relationship. So I think you should be able to do something like this: import sqlalchemy as sa class TagAssociation(Base): # columns including owner_id and post_id class Post(Base): # columns including id and owner_id tags = relationship( TagAssociation, primary_join=(sa.and_(id == TagAssociation.post_id, owner_id == TagAssociation.owner_id))) I think you would have to treat this relationship as readonly, so you might need/want to add viewonly=True. 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 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] question re using the session object
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of robert rottermann Sent: 14 June 2011 10:53 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] question re using the session object hi there, for a zope website I am using sqlalchemy. Now I am unsure how to use the session object. What I do now is: from sqlalchemy.orm import scoped_session ... Session = scoped_session(session_factory, scopefunc) session = Session() this session object I import into all classes where ever I need it. Now my question: is it ok to use this single instance troughout the life of the Zope severer, or should I call Session() whenever I need a session? thanks robert You definitely shouldn't use your 'session' instance throughout the application - it won't be thread-safe. Scoped sessions are described at http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-contextual, but basically, you have two choices. You can: a) Call Session() whenever you need a session. SA will ensure that if you call it twice within the same scope (which is typically the current thread), the same instance will be returned. b) Use your Session directly - it implements the same interface as the real session, and forwards all requests on to the underlying thread-local session. 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 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] General questions of a newbee
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Knack
Sent: 14 June 2011 18:43
To: sqlalchemy
Subject: [sqlalchemy] General questions of a newbee
Hi guys,
I've done some programming, but I'm new to RDBMS and ORMs. I've read
some documentation, but before diving in deeper and doing some
tutorials, I'm trying to understand what can be done with SQLAlchemy
and get a coarse understanding of how it works.
Imagine some tables which are all related (like 'created by') to a
user by a foreign key. If I query all tables by a certain user, I
assume SQLAlchemy loads and creates all objects which have references
in the column 'created by' to the certain user. Like a 'manual' eager
loading. If I use the objects properties to follow the relations,
does
SQLA need to perform any more DB accesses? Or are the referenced
objects directly referenced (maybe with properties that stores the
direct reference after resolving after the first call)?
How about backrefs? Would every call to those require a new SQL query
under the hood? Or are those 'stored' in the ORM after the first call?
I guess this would impact how to model parent-children relations. On
the one hand it seems like an easy life to me if the parents don't
need references to the children in the database, as children could be
added without modifing the parents. One the other hand, how's the
performance impact if you need to get the children by backref calls?
SQLAlchemy gives you a lot of control over when related objects are
accessed - the full details are at
http://www.sqlalchemy.org/docs/orm/loading.html.
When you configure a relationship between 2 classes, the default load
behaviour is known as lazy loading. This means that the related object
will only be loaded when you first access the property on the parent.
Once an object is loaded, it is stored in the SQLAlchemy session object.
Subsequent requests for that same object (ie. same type and primary key)
will get the object from the session rather than going to the database.
That's not a very clear explanation - perhaps an example would help.
Imagine you were modelling a blog, and you had Post items and User
items. Posts have a 'created_by_id' foreign key to the User table, and a
'created_by' relationship which gives you the actual User object. Now
imagine that you have 3 posts in the database, created by 2 different
users.
Here's what happens when you load all the posts and then access their
'created_by' property, in the default configuration.
posts = session.query(Post).all()
...runs something like 'SELECT * from post'
print posts[0].created_by
...SA looks at the created_by_id on posts[0], then checks to see if it
already has a User with that id in the session. It doesn't, so it
retrieves it from the database (SELECT * from user where id = :id),
stores it in the session, and returns it to you.
print posts[1].created_by
...SA checks posts[1].created_by_id again. It is the same as
posts[0].created_by_id. SA already has that user in the session, so it
returns the same user without going to the database.
print posts[2].created_by
...this post was created by a different user, which isn't already in the
session, so SA goes to the database again.
The posts themselves have now been stored in the session, so if you
wrote the following:
post = session.query(Post).get(1)
...SA would see that post 1 already exists in the session and not go
back to the database. Note that this only works for the 'get' method -
if you try to do any other kind of query, SA will still run the query.
However, when it's reading the rows back, it will try to match those
rows up with objects already in the session. If it finds a match, the
instance from the session will be returned. This ensures that (for a
given session) you will only ever have one instance representing a row
in the database.
If you were working with a large number of posts and users, it would be
very inefficient to (potentially) run a new query for each post just to
get the user that created it. SQLAlchemy allows you to request a
different loading strategy:
posts = (session.query(Post)
.options(joinedload('created_by'))
.all())
...issues something like:
SELECT *
FROM post
LEFT JOIN user ON post.created_by_id = user.id
ie. the users will be loaded in the same query as the posts. After this,
SA will not need to go back to the database when you access the
'created_by' property, even the first time.
Backrefs are not really any different from forward references, and the
same conditions apply. I think there may be a slight caveat though. If
you wrote:
posts = session.query(Post).all()
user = posts[0].created_by
print user.posts
...I don't think SA has any way of knowing that all the posts from the
DB have been already been loaded into the session. It will run something
like 'SELECT * from post where
RE: [sqlalchemy] db name from session?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Chris Withers Sent: 15 June 2011 10:48 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] db name from session? Hi All, If I have a session object, what's the correct way to get the name of the db that session is attached to? cheers, Chris A session can be bound to multiple databases, so I'm not sure it's as simple as you'd like. Session has a get_bind method that will return the engine (or perhaps connection, depending on how the session was configured): http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.S ession.get_bind ...but even then, I'm not sure you can necessarily go from an engine to a db name. What do you even mean by db name? Schema name? Host? Dialect? Filename? Simon -- 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] Accessing several databases
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Julian J. M.
Sent: 16 June 2011 11:43
To: sqlalchemy
Subject: [sqlalchemy] Accessing several databases
Hello,
I'm intending to use sqalchemy with orm for loading and storing my
application's project files. Each sqlite database would be a project
file, that will have several tables.
I'd like to work with projects like this:
project1=AppProject(/tmp/pr1.sqlite);
project2=AppProject(/tmp/pr2.sqlite);
item1 = project1.getItem(5) # item1 should be and object of a mapped
class.
item1.value=test
anotheritem = project1.getNewItem()
anotheritem.value=this is new
# this should flush and commit the underlying session for project1,
#modifying item with id 5, and adding a new one
project1.commitEverything()
item2 = project2.getItem(8)
item2.value = another test
project2.commitEverything()
The problem i'm facing is how to create the engine, metadata, mapper,
session, and the orm classes for each AppProject instance.
I'm not sure if this is supported or even a good idea.
Thanks,
Julian J. M.
I think this should be pretty easy with a separate SQLAlchemy Session
per project. You would define all your mappers and so on without any
reference to a specific database:
##
# your_db_module.py
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class ProjectItem(Base):
__tablename__ = 'project_item'
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
# other columns etc.
Then your AppProject class would look something like this:
##
# appproject.py
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from your_db_module import ProjectItem
class AppProject(object):
def __init__(self, filename):
self.engine = sa.create_engine('sqlite://' + filename)
self.session = saorm.Session(bind=self.engine)
def get_item(self, id):
return self.session.query(ProjectItem).get(id)
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 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] mapping a class linked with two other classes (AttributeError: 'str' object has no attribute '_sa_instance_state')
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Jules Stevenson
Sent: 16 June 2011 08:44
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] mapping a class linked with two other classes
(AttributeError: 'str' object has no attribute '_sa_instance_state')
Hi List,
I have a user class, a contact class, and a googleID class.
the contact class has can have a googleID per user in the system. I'm
trying to map it out as follows:
# ArkContact - clientprojectshot module
orm.mapper(ArkContact, contacts_table, properties={
'notes': orm.relation(ArkNote,
secondary=contact_notes_table,
backref='contacts',
single_parent=True,
cascade=all, delete, delete-orphan),
'users': orm.relation(ArkUser,
secondary=user_contact_table,
backref='contacts'),
'google_UID': orm.relation(ArkUserContactGUID,
cascade=all, delete,
backref='user')
})
#user contact google_GUID
user_contact_UID = sa.Table('user_contact_UID_table', meta.metadata,
sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'),
primary_key=True),
sa.Column('contact_id', sa.types.Integer,
sa.ForeignKey('contacts.id'), primary_key=True),
sa.Column('google_UID', sa.types.String(length = 1024))
)
class ArkUserContactGUID(object):
def __init__(self):
pass
orm.mapper(ArkUserContactGUID, user_contact_UID)
This raises two issues, the first is that an instrumented list is
returned for the google_UID paramter on the contact object, whereas
there should only ever be one (since as an operator there is only
ever
one user signed in - you).
For one-to-one relationships, you should supply uselist=False to your
relationship:
http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-one
The second is it outright errors :), presumably because my mapping is
off:
File 'C:\\ark\\ark\\controllers\\contacts.py', line 368 in
initial_sync
contact_sync.initial_sync()
File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 121 in initial_sync
self.add_contact_to_google(contact)
File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 259 in
add_contact_to_google
data.google_UID.append(entry.get_id())
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 952 in append
item = __set(self, item, _sa_initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 927 in __set
item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 618 in fire_append_event
item, initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\attributes.py',
line 741 in fire_append_event
value = fn(state, value, initiator or self)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\unitofwork.py',
line 35 in append
item_state = attributes.instance_state(item)
AttributeError: 'str' object has no attribute '_sa_instance_state'
Many thanks for any help!
Jules
You're passing a string (presumably the result of entry.get_id()) where
SA is expecting an instance of a mapped class. I haven't looked at your
mapping in detail, but rather than this:
data.google_UID.append(entry.get_id())
you probably want something like this:
obj = ArkUserContactGUID(google_UID=entry.get_id())
data.google_UID.append(obj)
(If I've misunderstood your mapping, these class names are probably
wrong)
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 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] dynamically set table_name at runtime
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Cody Django
Sent: 20 June 2011 19:37
To: sqlalchemy
Subject: [sqlalchemy] dynamically set table_name at runtime
Hello!
I would like to dynamically set/change the table that is mapped in my
python object, as instantiated through the declarative style.
class Feature(Base, GeometryTableMixIn):
this is dynamically created to use a table and pk_column
determined at runtime
__table_args__ = {
schema: 'a_schema',
autoload: True,
autoload_with: Session.bind,
useexisting: True
}
wkb_geometry = GeometryColumn('wkb_geometry', Geometry(srid=4269))
def __init__(self, *args, **kwargs):
self.__tablename__ = kwargs['tablename']
self.pk_id = Column('%s' % kwargs['pk_id'], types.Integer,
primary_key=True, autoincrement=False)
super(Feature, self).__init__(*args, **kwargs)
This doesn't work:
InvalidRequestError: Class class 'javelin.model.feature.Feature'
does not have a __table__ or __tablename__ specified and does not
inherit from an existing table-mapped class.
Could this possibly be done through another approach? Suggestions
are
greatly appreciated.
Can you describe your use case? The solution presented in the
StackOverflow article seems like a hack at best. A cleaner way to do the
same thing might be:
def make_feature_class(tablename):
class Feature(Base, GeometryTableMixIn):
__table__ = tablename
# etc.
return Feature
...but the whole thing feels strange. What are you actually trying to
do?
Cheers,
Simon
--
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] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
Michael Bayer wrote: - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with- joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) In the Quick Select links at the top of the 0.5 docs, there's no link to the 0.7 docs. Is this deliberate or has it just been overlooked? Would it be worth putting some sort of big banner at the top of the older docs pointing out that they are old? FWIW, I *really* appreciate that you keep the old versions of the docs around - I have an application that I maintain using SA 0.3, and just last week I needed to refer back to the docs. I hope they never go away! (I know they still exist in the repository, but the website is so convenient...) Cheers, Simon -- 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 case of joinedload_all how do I order by on a columns of those relations
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Marc Van Olmen
Sent: 29 June 2011 04:19
To: sqlalchemy
Subject: [sqlalchemy] In case of joinedload_all how do I order by on
a columns of those relations
Hi
I'm trying to order by a column from a relationship.
Taken example from:
http://www.sqlalchemy.org/docs/orm/loading.html#routing-explicit-
joins-statements-into-eagerly-loaded-collections
In case of
query.options(joinedload_all('orders.items.keywords'))...
or
query.options(joinedload_all(User.orders, Order.items, Item.keywords))
I would like to do something like:
query.options(joinedload_all('orders.items.keywords')).order_by('user
.orders.items.keywords.name')
Tried this above but didn't work. Searched for some sample/tutorials
but with no luck.
thanks for any direction.
marc
I think this is in the FAQ:
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOU
TERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYL
IMITetc.whichreliesupontheOUTERJOIN
(That link has probably wrapped - search for ORDER BY on
http://www.sqlalchemy.org/trac/wiki/FAQ)
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 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] relationship problem
mik wrote:
Hello,
I am trying to use sqlalchemy with oracle, here is my code:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relationship
class Activite(object):
pass
class Famprod(object):
pass
engine = create_engine('oracle://login/paswd@db', echo=True)
metadata = MetaData(engine)
tActivite = Table('ACTIVITE', metadata,
autoload=True)
mapper(Activite, tActivite)
tFamprod = Table('FAMPROD', metadata,
autoload=True)
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
Session = sessionmaker(bind=engine)
session = Session()
famprod = session.query(Famprod).get((ED, 15))
print famprod.activite
and i get this error:
AttributeError: 'RelationshipProperty' object has no attribute
'parent'
The table famprod has a composite key, one of the key columns is the
key of activite.
Is there something wrong with my code ?
I have tried to manually define the tFamprod's keys and foreign key
without succes.
Thank you.
I think your problem is here:
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
You can't add relationship properties to mapped classes, unless they
were set up with the declarative extension
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html).
Without declarative, the code should look something like this:
mapper(Famprod, tFamprod, properties={
'activite': relationship(Activite),
})
(http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-many)
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 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] information about filed create_engine
Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon -- 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] Re: information about filed create_engine
Eduardo wrote On Jul 13, 7:11 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon !) PostgresSQL 2) I don't get any Python exception. So how do you know it's failing then? Simon -- 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] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- 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] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- 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] Re: information about filed create_engine
Eduardo wrote: On Jul 14, 10:49 am, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described onhttp://www.sqlalchemy.org/docs/core/engines.html#configuring- logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon My application only queries the database there are no inputs and therefore no transactions involved. What was the result of turning on SQL logging? Are you sure you're even pointing at the same database that you were when you ran the standalone script? Try printing the value of session.bind.url (or including it in HTTP response, if you don't have easy access to the stdout from your wsgi script) Simon -- 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] Re: information about filed create_engine
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Eduardo
Sent: 18 July 2011 14:12
To: sqlalchemy
Subject: [sqlalchemy] Re: information about filed create_engine
I dont get any log. The access strings from the local and wsgi
applications are identical so the script should connect to the same
database. I encountered problems with create_engine. What type of
exception can this method throw?
The application catches: TypeError, ValueError and OperationalError.
Is there any other Error or some universal sqlalchemy error that can
indicate me where the problem is?
Thanks
I'm sorry - I still don't understand your setup. How do you know that
you've encountered problems with create_engine if you're not getting
any kind of exception from it?
If you really think that create_engine is failing but the exception is
being caught silently, why not change your code so that you've got an
exception handler around create_engine:
try:
engine = create_engine(your_connection_string)
except Exception, e:
import traceback
log_file = open('/tmp/sqlalchemy_errors', 'w+')
log_file.write('Exception from create_engine\n')
log_file.write('%s\n' % e)
log_file.write(traceback.format_exc())
raise
But your life would be much easier if you learnt how to configure
SQLAlchemy's built-in logging features:
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging
What WSGI server and web framework are you using (if any)? It sounds
like they are hampering your efforts to debug this. You might find it
easier to run a very simple wsgi server such as the one in the wsgiref
module:
http://docs.python.org/library/wsgiref.html#module-wsgiref.simple_server
Simon
--
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] Re: information about filed create_engine
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Eduardo Sent: 18 July 2011 15:54 To: sqlalchemy Subject: [sqlalchemy] Re: information about filed create_engine Yes, I use wsgi server of the python library bottle and I don't have any problem but when I want to use the same script via the apache web server I get only a server error no exception could be caught not even by using the code snippet from you (Thanks by the way). I simply included many print lines that appear in the error log file. The create_engine fails (I know it from try and except) but I cannot catch any exception that sheds some light on the reason of the failure. If you are getting a generic server error from Apache, you'll normally find the reason in the Apache error log (the location depends on your installation, but typically it is something like /var/log/httpd/error_log. Does that shed any light on the problem? Simon -- 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] Re: information about filed create_engine
Eduardo wrote:
/.../.../python2.6/site-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/
dialects/postgresql/psycopg2.py, line 234, in dbapi
psycopg = __import__('psycopg2')
ImportError: No module named psycopg2
The module psycopg2 is already installed in the site-packages
directory. I even included the path in the system variable by :
sys.path.append('/.../.../python2.6/site-packages/') in the wsgi
script.Still it won't work.
Why?
OK, this is definitely no longer an SQLAlchemy issue and more of a
mod_wsgi issue - you might get more help over on their mailing list
(http://code.google.com/p/modwsgi/wiki/WhereToGetHelp).
I believe psycopg2 is not a pure python module - it has a binary
component. Was it compiled with the same version of python that mod_wsgi
was?
Try this wsgi script (based on one from
http://code.google.com/p/modwsgi/wiki/InstallationIssues)
import sys
from pprint import pformat
def application(environ, start_response):
status = '200 OK'
output = (sys.prefix: %r\nsys.path: %s\n
% (sys.prefix, pformat(sys.path))
response_headers = [('Content-type', 'text/plain'),
('Content-Length', str(len(output)))]
start_response(status, response_headers)
return [output]
It would be worth comparing the output from that with the values of
sys.prefix and sys.path when run from bottle.
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 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] Question about sqlalchemy inserts and deletes order in a transaction
ammar azif wrote: Hi, The code that I am working on deletes rows from table A that are based on a certain query and then recreates these rows based on entries supplied by a csv file. Table A is referenced by table B. My question is, how does sql alchemy manage inserts and deletes in a transaction and it what order are they done? It seems that deletes are done after inserts because I am getting unique constraint errors, although the rows are deleted before inserts are done. If my assumption is correct, how do I change this behaviour in SQLAlchemy. I do not want to add unique deferrable constraint into table A because its unique constraint key is being referred by table B, this is a limitation of postgres. Appreciate your feedback I assume you are using the ORM. (If you are using the low-level API, SQL statements are executed explicitly via something like connection.execute()) The ORM executes statements when you call session.flush(). If you call that after deleting your rows, you should be safe to insert new ones with the same IDs afterwards. SQLAlchemy does detect dependencies between rows, so for example it would know to insert rows into Table A before any rows in Table B that reference them. However, I don't think it necessarily performs deletions before insertions. Simon -- 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] how to get last record from a resultset
If you don't have something consistent to sort by, then I'm not sure that the last record is meaningful, is it? If you have 10 rows with the same voucher code and account code (and there is nothing else to uniquely identify them, such as a more precise timestamp, or an auto-incrementing ID), then as far as the result set is concerned, there is nothing special about the last row. The database could be giving them to you in any order. I'm sure I'm misunderstanding your situation - perhaps you could describe your schema and why you need this information, and then we might be more help. Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Krishnakant Mane Sent: 20 July 2011 15:16 To: sqlalchemy@googlegroups.com Cc: Timuçin Kızılay Subject: Re: [sqlalchemy] how to get last record from a resultset Well, there won't be a consistent result using sort because there might be 10 rows with same voucher code and same account code. That's exactly the challenge so I don't know how sort will help. If we can invert the entire resultset having the last record become first, then its worth while. But again, I don't want the entire set of rows in the first place. I just want that particular row. Happy hacking. Krishnakant. On 20/07/11 19:20, Timuçin Kızılay wrote: I think, reversing the sort and getting the first record will do. 20-07-2011 16:32, Krishnakant Mane yazmış: Hello all, Subject line says it all. Basically what I want to do is to get last record from a result set. I am dealing with a situation where given a date I need to know the last record pertaining to transaction on a given account. yes, it is an accounting/ book keeping software. So I thought there was some thing like .last() method for a resultset? Or even better do we have some thing like session.query(table).last() The problem is that my logic is in place but I know that performance wise it is very dirty to get the list of all records, just to loop till the end and throw away all the rest of the rows. So plese suggest how can I only get just that one (last) record? Happy hacking. Krishnakant. -- 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. -- 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] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of jos.carpente...@yahoo.com Sent: 26 July 2011 18:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Updating records in table not working I'm using Postgres as a database. I try to create new records or update existing records with data. The data is parsed from a csv file. Creating new records works fine. But when a record already exists, the update fails with: IntegrityError: (IntegrityError) duplicate key value violates unique constraint stock_item_pkey I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' I've also tried updata, but that fails too and als mentions a depreciated statement. The new data is going to a single table. The PrimaryKey is the item number (item with value itemno in snippet below). Since the item is unique, I don't let Postgres create an id. new = Item(item=itemno, ...) db.session.add(new) db.session.commit() I'm pretty new with SA and I might overlook something. How can I solve this? I *think* you should be able to use session.merge instead: http://www.sqlalchemy.org/docs/orm/session.html#merging temp = Item(item=itemno, ...) new = db.session.merge(temp) db.session.commit() (note that 'merge' returns a new object attached to the session) 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 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: RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Cheers, Simon -- 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] engine.echo not working as expected
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
Simon
--
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] update existing row
vitsin wrote:
hi,
can't figure out why raw SQL works fine, but update() is not working:
1.working raw SQL:
self.session.execute(update public.my_table set
status='L',updated_at=now() where my_name='%s' % (self.my_name))
2.non working update() from Alchemy:
s = aliased(MyTable)
query = self.session.query(s).filter(s.my_name==self.my_name)
sts = self.session.execute(query).fetchone()
sts.update(values={'status':'L'})
sts.update(values={s.status:'L'})
File /usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py,
line 2097, in _key_fallback
Could not locate column in row for column '%s' % key)
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'update'
But Column s.status exists ...
appreciate any help,
--vs
In your example, 'sts' represents a single row from the database. These
objects don't have an 'update' method, which is why you are getting that
error. It thinks you are trying to access a column called 'update'
instead.
You appear to be using the SQL Expression language (ie. MyTable is
created using sqlalchemy.Table). You can create an 'update' statement
using MyTable.update(). Examples are at:
http://www.sqlalchemy.org/docs/core/tutorial.html#inserts-and-updates
(You should be able to substitute conn.execute() with session.execute())
However, you might be interested in using the ORM part of SQLAlchemy:
http://www.sqlalchemy.org/docs/orm/tutorial.html
Your usage would then look something like this (assuming MyMappedClass
is the class mapped to MyTable):
s = MyMappedClass
query = self.session.query(s).filter(s.my_name == self.my_name)
sts = query.first()
sts.status = 'L'
self.session.flush()
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 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] data driven schema in sqlalchemy
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of espresso maker Sent: 05 August 2011 06:19 To: sqlalchemy Subject: [sqlalchemy] data driven schema in sqlalchemy Hi there, I have a data driven database schema that I am trying to implement in sqlalchemy. Here's how the tables look like: user user_id | | user_properties property_id | property_name | property_description user_properties_data user_id | property_id | property_value What I would like to do eventually is if I have u = User() , u. [some_propery_name] return the property_value if it exist for that user. Any suggestions on how to implement this? There's an example of something like this in the SQLAlchemy repository: http://www.sqlalchemy.org/docs/orm/examples.html#vertical-attribute-mapp ing http://hg.sqlalchemy.org/sqlalchemy/file/3e75f284f253/examples/vertical I don't think it's exactly what you've described, but hopefully it's a starting point. 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 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] Re: Error while using CAST
Does this work instead:
table.update().values(empno = cast(table.c.empno,Integer)).execute()
ie. a bare 'empno' inside your cast expression is just referring to a python
variable 'empno', which you've probably set to the value 'testing' at some
other point in your code. You need the column object table.c.empno instead
Hope that helps,
Simon
pravin battula wrote
Mike,
when i execute the below sql statement directly in the database using
sqlyog,it works fine but when tried with sqlalchemy it didn't.
update mytable set EmpMaster = cast(empno as UNSIGNED INTEGER)
On Sep 14, 8:23 pm, pravin battula pravin.batt...@gmail.com wrote:
Hi Mike,
I'm using Mysql 5.0 backend
On Sep 14, 8:20 pm, Mike Conley mconl...@gmail.com wrote:
Don't know what database you are using, but this looks like you
are trying
to cast the string 'testing' to an integer and the database
engine says you
can't do that.
--
Mike Conley
On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
pravin.batt...@gmail.comwrote:
Sorry for the spelling mistake.It shows an error as below.
OperationalError: (OperationalError) (1292, Truncated
incorrect
INTEGER value: 'testing') 'UPDATE test.mytable SET
`newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
On Sep 14, 6:48 pm, pravin battula pravin.batt...@gmail.com
wrote:
Hi,
I'm using cast to update values in a table by issuing
following
command.
table.update().values(empno = cast(empno,Integer)).execute().
Where as empno is an string field,i'm trying to convert the
data from
empno column from string to integer and then issuing the
below command
to alter the data type of the column by issuing following
command.
alter_column(table.c.empno,type=Integer).
It shows an error as
OperationalError: (OperationalError) (1292, Truncated
incorrect
INTEGER value: '1d') 'UPDATE test.mytable SET
`newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
Please do the needful
--
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.
--
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.
--
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] Bulk creation of columns
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of pravin battula
Sent: 21 September 2011 12:54
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Bulk creation of columns
Hi,
How can i create columns in bulk using create_column method?
I tried as below,.
migrate_engine = create_engine('mysql://root:root@localhost/
payroll', echo=False)
metadata = MetaData(bind = migrate_engine)
metadata.reflect(bind = migrate_engine, schema = 'payroll')
tableObj = metadata.tables.get('test.salary')
colList =
[Column('description',String(100)),Column('information',String(50))]
tableObj.append_column(*colList)
tableObj.create_column(*colList)
getting an error as TypeError:create() got multiple values for
keyword argument 'table'
Please do the needful.
create_column isn't an SQLAlchemy method as far as I know. Are you using
something like sqlalchemy-migrate
(http://code.google.com/p/sqlalchemy-migrate/)?
If so, you'll probably get more help on their mailing list.
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 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] Possible bug with subqueryload
Hi,
I think there may be a bug in the interaction between 'subqueryload' and
having a default 'order_by' defined on a mapped class. When the subquery
is run, it looks like the ORDER BY is being placed on the outer query,
whereas it should be on the inner query. The full test case is below,
but here are the 2 queries (produced using hg revision 62e97372a028):
Main query
--
SELECT master.id AS master_id, master.dummy AS master_dummy
FROM master
ORDER BY master.id DESC
LIMIT 2 OFFSET 0
Subquery
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
anon_1.master_id AS anon_1_master_id
FROM (SELECT master.id AS master_id
FROM master
LIMIT 2 OFFSET 0) AS anon_1
JOIN detail ON anon_1.master_id = detail.master_id
ORDER BY anon_1.master_id
Since the ORDER BY is not on the inner query, a different set of
'master' rows is referenced than in the main query.
Cheers,
Simon
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
dummy = sa.Column(sa.Integer)
__mapper_args__ = {'order_by': sa.desc(id)}
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
master = saorm.relationship(Master, backref='details')
def test():
dburi = 'sqlite://'
engine = sa.create_engine(dburi, echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = saorm.create_session(bind=engine)
session.begin()
# Insert 5 masters, each with 1 detail
for i in range(5):
master = Master(dummy=i)
master.details.append(Detail())
session.add(master)
session.commit()
session.close()
# Load back 2 masters, using subqueryload to load the detail
# rows. If you uncomment the '.order_by' line here, the test
# passes.
master_query = (session.query(Master)
#.order_by(sa.desc(Master.id))
.limit(2)
.options(saorm.subqueryload('details')))
# Display the details for each master
for item in master_query:
print 'Master %s: %s' % (item.id, item.details),
if len(item.details) == 0:
print 'FAIL'
else:
print 'PASS'
if __name__ == '__main__':
test()
--
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] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 16:24 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload Hi Simon - yeah that looks pretty buglike to me, mapper.order_by is not a frequently used feature so this one may need some adjustment. I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this and so far I'm targeting it at 0.6.9/0.7.3. Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. Thanks, Simon -- 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] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 19:37 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload On Sep 27, 2011, at 1:16 PM, King Simon-NFHD78 wrote: Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. It actually exists in Hibernate, but not for us.We do sort of have the infrastructure in place to make it possible, i.e.the subqueryload right now prepares a Query object at query time that fires off during load time, with a mapper option it would need to stick it as some kind of memo in each InstanceState, it would be very tricky to implement.Keeping that state and keeping it plugged into the InstanceStates, then what if the loader was fired after many of the other states have been garbage collected, just a lot of corner cases to deal with. it can be handrolled of course, the general technique when you want to construct objects such that they appear loaded is to use attributes.set_commited_value() to set an attribute such that the ORM sees it as what was loaded from the database. an example of that is where we first introduced the subquery concept here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading Ah yes, set_committed_value is exactly the sort of thing I was looking for. Thanks a lot, Simon -- 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: the return type of conn.execute(text())
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Faheem Mitha Sent: 27 January 2009 22:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] the return type of conn.execute(text()) Hi, Today I attempted to serialize the return value of the form result = conn.execute(text()) Till now I thought that the return type was a list of tuples, while in fact it is a list of objects of type class 'sqlalchemy.engine.base.RowProxy'. Hence cPickle refused to serialize till I did some conversion. Just wondering what the reason for this is. Regards, Faheem. The RowProxy object is more intelligent than a plain tuple. As well as accessing the values by index, you can use your original column objects or the name of the column to retrieve the values from it. You can also use attribute access rather than indexing. Eg. row['your_column_name'] row[your_column] row.your_column_name I imagine this would make it harder to pickle. Simon --~--~-~--~~~---~--~~ 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: Using orderedlist with a secondary table
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Nathan Harmston Sent: 30 January 2009 13:15 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Using orderedlist with a secondary table Hi, I am currently trying to make a relation between Document and Author, where there is a many to many association which is dealt with by a secondary table and I am trying to store the position of an author in the author list. [snip] So is there an easy way of performing this functionality using the ordered list or do I need to do something else? Many thanks in advance nathan You can't use the 'secondary' mapper argument if you want to be able to use columns in that secondary table. Instead, you need to map a class to your secondary table directly, such that it has 1-many relations with the Document and Author classes. You can then use the 'associationproxy' extension to hide the details. See the docs at http://www.sqlalchemy.org/docs/05/mappers.html#association-pattern and http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#as sociationproxy 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 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: polymorphic_identity not allowed to be zero for base class?
Would None (ie. SQL NULL) be a valid polymorphic identity?
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer
Sent: 30 January 2009 17:06
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: polymorphic_identity not allowed to
be zero for base class?
its likely a bug. there are some boolean tests for
polymorphic_identity in mapper.py which should be changed
to is None.
On Jan 30, 2009, at 11:58 AM, Gunnlaugur Thor Briem wrote:
Hi,
[trying to send this again, seems like previous copy
got lost in some moderation queue]
Messing with single-table inheritance in a declarative
model, with a non-abstract base class, I find that querying
fails if polymorphic_identity is 0 (zero). Example:
code begins
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Course(Base):
__tablename__ = 'course'
id = Column(Integer, primary_key=True)
course_type = Column(Integer)
__mapper_args__ = {'polymorphic_on':course_type,
'polymorphic_identity':0}
class MultiYearCourse(Course):
__mapper_args__ = {'polymorphic_identity':1}
engine = create_engine('sqlite:///:
memory:')
Base.metadata.create_all(bind=engine)
session = sessionmaker(bind=engine)()
myc = MultiYearCourse()
myc.name http://myc.name/ = uComputer Graphics
c = Course()
c.name http://c.name/ = uSociology
session.add(c)
session.add(myc)
session.commit()
print MYC: %s % myc
print C: %s % c
query = session.query(Course)
print Query: %s % query
print Results: %s % query.all()
code ends
That last line fails with an AssertionError:
output begins
MYC: __main__.MultiYearCourse object at 0xcf7d30
C: __main__.Course object at 0xcf7d70
Query: SELECT course.id http://course.id/ AS
course_id, course.course_type AS course_course_type
FROM course
Traceback (most recent call last):
File
/Users/gthb/Documents/workspace/test/src/sqlalchemytest.py,
line 31, in module
print Results: %s % query.all()
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/orm/query.py, line 1186, in all
return list(self)
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/orm/query.py, line 1341, in instances
rows = [process[0](context, row) for row in fetch]
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/orm/query.py, line 1942, in main
return _instance(row, None)
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/orm/mapper.py, line 1557, in _instance
_instance = polymorphic_instances[discriminator]
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/util.py, line 71, in __missing__
self[key] = val = self.creator(key)
File
/Users/gthb/Library/Python/2.5/site-packages/SQLAlchemy-0.5.2
-py2.5.egg/sqlalchemy/orm/mapper.py, line 1695, in
configure_subclass_mapper
raise AssertionError(No such polymorphic_identity
%r is defined % discriminator)
AssertionError: No such polymorphic_identity 0 is defined
output ends
But if I exchange the polymorphic identities, so the
base class gets the 1 and the subclass gets the 0, then it
runs just fine!
It seems to me that this can't be intentional - don't
see a reason for it, and the docs do not mention any
particular restrictions on values of polymorphic_identity.
Regards,
- Gulli
--~--~-~--~~~---~--~~
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: polymorphic_identity not allowed to be zero for base class?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: 30 January 2009 17:25 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: polymorphic_identity not allowed to be zero for base class? On Jan 30, 2009, at 12:19 PM, King Simon-NFHD78 wrote: Would None (ie. SQL NULL) be a valid polymorphic identity? it becomes that issue where we just need some kind of constant to represent THIS_IS_NOT_DEFINED, so that we know when polymorphic_identity is defined or not. it's a straightforward patch. Although we'd also need to adapt some expressions such as identity IN (x, y, z) to account for NULL, so, supporting NULL is a little more work. Thanks - I was only asking out of curiosity. I have no need for it personally. Cheers, Simon --~--~-~--~~~---~--~~ 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: classes in separate files
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of camlost Sent: 09 February 2009 09:18 To: sqlalchemy Subject: [sqlalchemy] classes in separate files Hi, could someone advice me, please, how to split classes into individual files? I'm studying the documentation: Creating Table, Class and Mapper All at Once Declaratively http://www.sqlalchemy.org/docs/05/ormtutorial.html#creating-table- class-and-mapper-all-at-once-declaratively. If I keep all the classes in the same file, the program works fine. If I try to split them into individual files, I'm getting errors like NoReferencedTableError: Could not find table 'users' with which to generate a foreign key Are there some Best practices of how to do this? Thanks. c. PS: All the classes are in their individual modules in a package called database: database /dbinit.py - common initialization (ex. Base = declarative_base()) /mailevt.py - class MailEvent /mailogrec.py - class MailLogEvent (foreign keys to Server, ObjAddress, MailEvent) /objaddr.py - class ObjAddress /server.py - class Server /user.py - class User /useraddr.py - class UserAddress (foreign key to User, ObjAddress) /vpnlogrec.py - class VpnLogRecord (foreign key to User, Server) /weblogrec.py - class WebLogRecord (foreign key to User, Server) Are all your classes using the same declarative_base? I think this is necessary so that the tables use the same metadata and things like foreign keys can be resolved. I would probably do this by creating database/base.py that contains something like: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() And then your other modules would import Base from there. Does that make any sense? Simon --~--~-~--~~~---~--~~ 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: altering tables
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of mhearne808[insert-at-sign-here]gmail[insert-dot-here]com Sent: 10 February 2009 19:13 To: sqlalchemy Subject: [sqlalchemy] altering tables [snip] I'd like to update the database (do the equivalent of an 'ALTER TABLE' SQL command) without destroying all of the data I already have. How do I do this? I have already tried using: metadata.create_all(engine,checkfirst=True) which can handle _new_ tables being added, it seems, but doesn't seem to update the tables for which I have altered the definitions using the Table object. I'm using SQLAlchemy '0.5.0beta3', with Python 2.5 on Mac OS X. Thanks, Mike You may like to have a look at the SQLAlchemy Migrate project: http://code.google.com/p/sqlalchemy-migrate/ 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 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: Separating session and db/models definitions
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of bsdemon Sent: 24 February 2009 20:22 To: sqlalchemy Subject: [sqlalchemy] Separating session and db/models definitions Hello. I have the following states of things: 1) I need to place some common database metadata description and models in one package 2) There are at least to applications, which will use this models: first app uses scoped session with their own scope_func, second app uses session from sessionmaker 3) I have some logic in models' methods that need session object to present (delete relations or etc.) I have no idea how to do it... Is there need for some kind of proxy to session, which will appear later, when app decide to instantiate it? Or I must define session in place with models? You can find out which session an object is loaded in using the sqlalchemy.orm.object_session function. So inside your methods, you should be able to say something like: session = orm.object_session(self) I think that should work no matter what session strategy you are using. 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 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: Creating SQL Expression
Could you use the python 'operator' module
(http://docs.python.org/library/operator.html)?
Eg. (untested):
import operator
operations = {
'+': operator.add,
'-': operator.sub,
# etc.
}
def combine_columns(op, *cols):
return operations[op](*cols)
sum_column = combine_columns('+', a, b)
I think that should work.
Simon
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Ashish Bhatia
Sent: 25 February 2009 13:26
To: sqlalchemy
Subject: [sqlalchemy] Re: Creating SQL Expression
The problem is still their.
The two seprate list of
columns = List of sqlalchem object
operator = ['+'','-']
using join to join them will convert the columns object to string
which is not desirable.
Any way to fix this.
On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
sorry its resolved and working
On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:
Hello ,
I am trying to make query like
select (a+b) from xyz;
to do this
xyz = sqlalchemy.Table('xyz',metadata)
a = sqlalchemy.Column('a', sqlalchemy.Integer)
xyz.append_column(a)
b = sqlalchemy.Column('b', sqlalchemy.Integer)
xyz.append_column(b)
column = [(a + b)]
select = sqlalchemy.select(from_obj=xyz,
columns=column,distinct=True)
This works fine for me.
Now when the columns a and b are dynamic (Enter by the
user in form of
string) and the operator too comes from user
columns_list = ['a','b']
operator = ['+']
like this i get the input
so i make the loop and make
for both the columns something like this
columns = []
for x in column_list :
t = sqlalchemy.Column(x, sqlalchemy.Integer)
xyz.append_column(a)
columns.append(t)
so now
how to add + to make the quer run
Thanks in the advance.
--~--~-~--~~~---~--~~
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: Class.query vs DBSession.query(Class)
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Sanjay Sent: 27 February 2009 05:52 To: sqlalchemy Subject: [sqlalchemy] Class.query vs DBSession.query(Class) Hi, There are two styles of writing code for querying: the assignmapper style, i.e. Class.query vs. the standard style as documented in SQLAlchemy tutorial, i.e. DBSession.query(Class). The assignmapper style seems simpler and intuitive. Curious to know why it is not the standard way. Are there any disadvantages? Also refer http://groups.google.co.in/group/turbogears/browse_thread/thre ad/8f3b4c4da33d69c8 thanks, Sanjay Class.query can only work if you are using a scoped session, which isn't appropriate for many kinds of application. Session.query(Class) is completely general and will work anywhere, and is therefore more appropriate for use in the documentation. As the post in the TurboGears thread points out, you can easily add Class.query using the query_property method of the scoped session. Simon --~--~-~--~~~---~--~~ 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: Insertion issues
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Tanmoy
Sent: 03 April 2009 14:30
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Insertion issues
import sqlalchemy
from sqlalchemy import *
engine = create_engine('mysql://root:voxta...@localhost/stock')
metadata = MetaData()
users=Table('NSE', metadata,
Column('Company_ID',String(40),primary_key=True),
Column('10:00',Numeric(8,2)),
)
metadata.create_all(engine)
conn = engine.connect()
conn.execute(users.insert(), [{'name':'joe', '10:00':'1200'}])
Run this snippet.if u cld..thr r errors
cropping up...
Tom
You aren't passing a value for the 'Company_ID' column. If I change your
'name' parameter to 'Company_ID' instead, and run the script on sqlite
(ie. dburi = 'sqlite:///:memory:'), it works for me.
Simon
--~--~-~--~~~---~--~~
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: mapping class against arbitrary SQL expression
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of JanW
Sent: 17 April 2009 13:18
To: sqlalchemy
Subject: [sqlalchemy] mapping class against arbitrary SQL expression
Hi,
is there a way to map a class against an arbitrary SQL expression
(read-only would be OK)? I can't find the correct way to define the
selectable for the mapper.
Example:
this table:
carside_table = Table(
'carside', metadata,
Column('id', Integer, primary_key=True),
Column('car_id', Integer),
Column('side', Text),
Column('temperature', Float),
)
with a dummy class;
class CarSide(object):
pass
And I want to use this SQL expression to map the class:
SELECT
left.car_id
left.temperature AS left_temperature
right.temperature AS right_temperature
FROM carside AS left
JOIN carside AS right
ON left.car_id=right.car_id
WHERE
left.side = left AND
right.side = right
;
Many thanks,
Jan.
I think the approach should look something like this:
#---
from sqlalchemy import *
from sqlalchemy import orm
metadata = MetaData()
carside_table = Table(
'carside', metadata,
Column('id', Integer, primary_key=True),
Column('car_id', Integer),
Column('side', Text),
Column('temperature', Float),
)
left = carside_table.alias('left')
right = carside_table.alias('right')
tables = left.join(right, left.c.car_id == right.c.car_id)
s = select([left.c.car_id,
left.c.temperature.label('left_temperature'),
right.c.temperature.label('right_temperature')],
from_obj=tables,
whereclause=and_(left.c.side == 'left',
right.c.side == 'right'))
class CarSide(object):
pass
orm.mapper(CarSide, s, primary_key=[s.c.car_id])
#---
...but it fails on the last line with the message Mapper
Mapper|CarSide|%(3069523404 anon)s could not assemble any primary key
columns for mapped table '%(3069523404 anon)s'. I had hoped that
passing the primary_key parameter to mapper would have solved that, but
it doesn't. I'm not sure why.
Simon
--~--~-~--~~~---~--~~
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: mapping class against arbitrary SQL expression
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of JanW
Sent: 17 April 2009 14:45
To: sqlalchemy
Subject: [sqlalchemy] Re: mapping class against arbitrary SQL
expression
OK, thanks,
it does work if you make an alias on the select like this:
s = select([left.c.car_id,
left.c.temperature.label('left_temperature'),
right.c.temperature.label('right_temperature')],
from_obj=tables,
whereclause=and_(left.c.side == 'left',
right.c.side == 'right')).alias('carside')
Thanks a lot!
Now, is it really needed to translate my SQL query first to SA-speak
or could I use the SQL directly in some way? (I have many old projects
with SQL embedded in Perl scripts, so it would be someway easier is I
can transfer the SQL directly).
From session.query() there is something like from_statement
(SQL_string) but that won't work here I think.
Again, many thanks,
Jan.
I don't know the answer, but I suspect this will be a problem. I think
SA needs to know what columns are going to be returned from the select
statement so that it can set up properties on the mapped class. I don't
know of any way that you can mark a text block as a Selectable.
Sorry I can't be more help,
Simon
--~--~-~--~~~---~--~~
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: This join does not fill in the collection
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Marcin Krol Sent: 28 May 2009 10:09 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] This join does not fill in the collection Hello everyone, session.query(Reservation, Host).join(Reservation.hosts).filter(Reservation ...).all() Reservation.hosts is not filled in, when I access .hosts collection in individual Reservations, SQLA issues queries to fill in the collection one Reservation by one. Again, Reservation and Hosts are many to many relation. I certainly can group the Host objects to particular Reservations myself later. But... Is there a way to make SQLA do it itself at query time? I haven't found anything in the docs that would suggest that.. Regards, mk If your query had also filtered by some of the host columns, the result set wouldn't contain all the hosts for each Reservation returned. For this reason, SQLAlchemy doesn't assume that just because the host columns are available they represent the entire Reservation.hosts collection. If you want Reservation.hosts to be filled in by the query, you want eager loading. This can be configured for all queries when you define the relation (by setting lazy=False), or on a query-by-query basis by adding an 'eagerload' option to the query. Both of these methods will add an extra join to the hosts table in your query (separate from any join that you explicitly ask for) If you've added an explicit join and you know that the result set already contains all the information you need, you can use the contains_eager option to indicate that the the relation should be filled in from the information in the result set. Documentation for most of this is at http://www.sqlalchemy.org/docs/05/mappers.html#configuring-loader-strate gies-lazy-loading-eager-loading 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 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: This join does not fill in the collection
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Marcin Krol
Sent: 28 May 2009 13:57
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: This join does not fill in the collection
Hello Simon,
This answered my question, thanks! (I don't know how I missed that in
docs..)
This is theoretically theoretical:
But suppose I *did* some selection on Hosts and still used
.options(eagerload('hosts')) on query - would that screw smth up?
In particular, if I called session.commit() later, would that save
Reservations with changed collections?
Regards,
mk
I don't know the answer for certain, but I suspect that if you only had
a half-loaded collection, you could add and remove items from that
collection, and those changes would be reflected in the database,
without affecting items that hadn't been loaded. I don't think SA would
do the bulk UPDATES or DELETES that would be necessary to affect items
that hadn't been loaded.
(I also don't know what would happen if you deleted a Reservation with a
half-loaded hosts collection. It probably depends on the cascade
settings on the relation)
Again, these are only guesses. It should be easy enough for you to knock
up a test case to find out the answer for sure.
Simon
--~--~-~--~~~---~--~~
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 - column names
George Sakkis wrote: Is there a (public) API for getting the column names of a given Query instance and other similar introspection needs ? I didn't find anything related in the docs but after digging in the code I came up with col_names = [e._result_label for e in q._entities] but I'm not sure how stable and robust this is. George Query instances have a 'statement' property that returns the underlying SELECT object. You can then inspect it's 'columns' attribute: columns = list(query.statement.columns) for column in columns: print column.name Would that do what you want? Simon --~--~-~--~~~---~--~~ 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: aggregation with count and webhelpers.paginate
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Hollister Sent: 20 June 2009 02:15 To: sqlalchemy Subject: [sqlalchemy] Re: aggregation with count and webhelpers.paginate Well, that worked great: q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\ .group_by(m.Hit.referer)\ .order_by(func.count(m.Hit.id).desc()) Thanks! ps: Is there a better way to specify the count in the order_by? If it's just that you don't like repeating yourself, you should be able to save the result of func.count (untested): hit_count = func.count(m.Hit.id) q = (meta.Session.query(m.Hit.referer, hit_count) .group_by(m.Hit.referer) .order_by(hit_count.desc()) Simon --~--~-~--~~~---~--~~ 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: Dynamic making of the where clause
On Jun 23, 3:32 pm, Ash ashishsinghbha...@gmail.com wrote: Hello, I am tryin to make the dynamic where clause using append_whereclause. But how i can do that, For eg : I have a==b and c in (1,2,3,4) or d like %s So i made three sqlalchemy expression 1. a==b 2. c in (1,2,3,4) [ using in_] 3. d like %s [using like] now i want this 3 to stuff in where clause . I created select like sel = select() How can i make the where clause which have and and or both uisng append_whereclause. I think append_whereclause always uses AND, so you need to connect these conditions into a single clause and call append_whereclause just once. It's not clear how you want the grouping of your conditions to work. Is it: (a==b AND c in (1, 2, 3, 4)) OR d like %s Or a==b AND (c in (1, 2, 3, 4) OR d like %s) I think you need to do something like this (for the first option): from sqlalchemy import and_, or_ clause = or_(and_(a == b, c.in_([1, 2, 3, 4])), d.like(something)) sel.append_whereclause(clause) You may also be able to use the '' and '|' operators, as long as you are careful with brackets. See the docs at http://www.sqlalchemy.org/docs/05/sqlexpression.html#conjunctions 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 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: autoload of db view treating columns as Decimal
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Randy Syring Sent: 23 July 2009 07:23 To: sqlalchemy Subject: [sqlalchemy] Re: autoload of db view treating columns as Decimal Well, I am not really sure what the problem is, but it looks like it might not have anything to do with the autoload. I tried just using a select statement with a from_obj parameter and it looks to me like SA is getting things wrong when it looks for the field name. Here is the script i used: http://paste.pocoo.org/show/130239/ Here is the key:value pairs for each row/column when trying to select from an sqlite view that uses a case statement: row 0 id: 1 name: jack 'something': something case when name == 'jack' then 1 else 0 end: 1 row 1 id: 2 name: sam 'something': something case when name == 'jack' then 1 else 0 end: 0 The same test running against Postgres is: row 0 id: 1 name: jack staticfield: something isjack: 1 row 1 id: 2 name: sam staticfield: something isjack: 0 When I run your script, I get the correct output, using Python 2.5.1, SA 0.5.4p2, sqlite3 version 2.3.2. Perhaps the bug is in the version of SQLite that you are using? What happens if you run your query using the SQLite command-line tools? Simon --~--~-~--~~~---~--~~ 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: Session mapper and Class.query() method
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of naktinis Sent: 27 July 2009 13:07 To: sqlalchemy Subject: [sqlalchemy] Session mapper and Class.query() method I've seen that Session.mapper is deprecated. While I agree that auto- adding objects to a session may be a bad idea, I don't see why is it so bad to add the query() method directly to the mapped class. Is there a reason, why I shouldn't do it? The query() method assumes that you are using a scoped session. As long as you know that you are always going to use a scoped session, you can use: cls.query = scoped_session.query_property() ...to add it back in again. If you ever use explicit sessions, it might be confusing that sometimes you say cls.query(), and other times say session.query(cls), but that's up to you. That's the only reason I can think of for being cautious about cls.query(). Simon --~--~-~--~~~---~--~~ 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] Backrefs and the identity map
Hi,
Does accessing a backref always have to issue SQL, even if the object to
be loaded already exists in the identity map? For example, if I have a
many-to-one lazy-loaded relationship from Master to Detail with a
backref, the statement master.details[0].master will issue SQL for the
'.master' backref, even though it already exists in the session. I know
I can eagerload('details.master'), but I was just wondering if there was
any way of getting the relation mechanism to check the identity map
before issuing the SQL.
Here is an example:
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.bind = sa.create_engine('sqlite:///:memory')
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True)
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True)
master_id = sa.Column(None, sa.ForeignKey(Master.id))
master = saorm.relation(Master, backref='details')
def dbg(msg):
print \n %s % msg
def test():
dbg('SQLAlchemy version: %s' % sa.__version__)
Base.metadata.create_all()
Session = saorm.sessionmaker()
sess = Session()
m = Master()
for i in range(10):
m.details.append(Detail())
sess.add(m)
sess.commit()
sess.close()
Base.metadata.bind.echo = True
sess = Session()
dbg(Getting Master)
m = sess.query(Master).first()
dbg(Getting details)
details = m.details
dbg(Getting master of first detail)
m2 = details[0].master
assert m2 is m
dbg(Getting master again via query.get)
m3 = sess.query(Master).get(m.id)
assert m3 is m2
if __name__ == '__main__':
test()
And here is the output:
SQLAlchemy version: 0.5.5
Getting Master
2009-08-03 13:17:12,445 INFO sqlalchemy.engine.base.Engine.0x...7ecL
BEGIN
2009-08-03 13:17:12,447 INFO sqlalchemy.engine.base.Engine.0x...7ecL
SELECT master.id AS master_id
FROM master
LIMIT 1 OFFSET 0
2009-08-03 13:17:12,447 INFO sqlalchemy.engine.base.Engine.0x...7ecL []
Getting details
2009-08-03 13:17:12,451 INFO sqlalchemy.engine.base.Engine.0x...7ecL
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id
FROM detail
WHERE ? = detail.master_id
2009-08-03 13:17:12,451 INFO sqlalchemy.engine.base.Engine.0x...7ecL [1]
Getting master of first detail
2009-08-03 13:17:12,456 INFO sqlalchemy.engine.base.Engine.0x...7ecL
SELECT master.id AS master_id
FROM master
WHERE master.id = ?
2009-08-03 13:17:12,457 INFO sqlalchemy.engine.base.Engine.0x...7ecL [1]
Getting master again via query.get
Finished
So query.get doesn't issue a query, but master.details[0].master
does. Is there any way of making the backref use query.get, and thereby
use the identity map?
Thanks a lot,
Simon
--~--~-~--~~~---~--~~
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: Backrefs and the identity map
I wrote: Hi, Does accessing a backref always have to issue SQL, even if the object to be loaded already exists in the identity map? For example, if I have a many-to-one lazy-loaded relationship from Master to Detail with a backref, the statement master.details[0].master will issue SQL for the '.master' backref, even though it already exists in the session. I know [SNIP] So query.get doesn't issue a query, but master.details[0].master does. Is there any way of making the backref use query.get, and thereby use the identity map? I delved into the source to find out how this works, and I see that the LazyLoader strategy has an attribute 'use_get' which is meant to do exactly this. However the test to see whether the lazywhere clause is the same as the mapper's get() clause is failing: In [1]: import satest2 In [2]: s = satest2.Detail.master.property.strategy In [3]: s.mapper._get_clause[0].compare(s._LazyLoader__lazywhere) Out[3]: False In [4]: print s.mapper._get_clause[0] master.id = ? In [5]: print s._LazyLoader__lazywhere master.id = ? In [6]: print s.mapper._get_clause[0].left master.id In [7]: print s._LazyLoader__lazywhere.left master.id In [8]: print s.mapper._get_clause[0].left.compare(s._LazyLoader__lazywhere.left) False So even though both clauses are binary expressions representing master.id = ?, the master.id in each case is different. On the offchance, I changed the foreign key definition from: master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id)) to master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.__table__.c.id)) ...and now it seems to work! So is this a bug? Thanks, Simon --~--~-~--~~~---~--~~ 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: Backrefs and the identity map
Michael Bayer wrote: King Simon-NFHD78 wrote: to master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.__table__.c.id)) ...and now it seems to work! So is this a bug? yes, that would be a bug. There are some other scenarios where this kind of thing occurs (lazy clause doesn't optimize) related to inheritance but they are less clear cut as bugs. Another point though, if you upgrade to trunk /0.5.6, many-to-one backrefs wont even fire off anymore if unloaded, when they are accessed in the backref context. anyway my hunch is that the Column you get back from Master.id is a proxy to the real one, so we'd have to figure some way for ForeignKey to navigate into the real column (dangerous, since it is assuming) or get the comparison to honor proxy columns (more likely). if you can file a ticket that would be very helpful otherwise im going to forget. Done. In case it helps, the proxy is an sqlalchemy.sql.util.AnnotatedColumn. If you call '_deannotate' on it, the resulting object compares equal with the original column. Thanks again, Simon --~--~-~--~~~---~--~~ 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: new questions
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of darkblueB Sent: 17 August 2009 06:31 To: sqlalchemy Subject: [sqlalchemy] new questions Hi- I have just read a lot and gone through some exercises, but am at an early stage here.. Two questions: 1) I have an existing database, and want to use sa to work with it.. so reflect seems like what I want. I have loaded up the database successfully - sqlite - not with reflect(), but instead with named tables and autoload=true. Now I am reading about declarative base class.. is a reflect/ autoload approach preemptive of declarative? do I have to make mappers? there are a number of foreign key relationships, declared in the db.. how do I find out more about which ones are being instantiated for me? 2) Session vs connect.. from my limited experience, conn = engine.connect() seems natural and all I need. I am reading this intro material and they are going on at length with Session. Is Session really necessary? so much more desirable? I am really looking for an expedient use of sa, I dont think I will get deeply into this.. pragmatic! thanks in advance -Brian Hi, In answer to your first question, the declarative extension is pretty flexible. You can still use autoload to get the table definitions, and then explicitly add anything that the autoload doesn't pick up. There is an example at http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#table-c onfiguration Once your declarative class has been created, you can access the underlying table object as YourMappedClass.__table__. This table should have a foreign_keys property which lists all the forgein keys, so you can see whether they have been detected correctly. As for your second question, this is mostly about the difference between the SQL Expression Language layer of SQLAlchemy, which generally works with engines and connections, and the ORM layer, which normally works with the Session. The description at http://www.sqlalchemy.org/docs/05/session.html#what-does-the-session-do is probably a reasonable description of why you might want to use a Session. If you are only ever reading data from the database, and you don't want or need to build object relationships on top of your SQL data, you can easily get away with the SQL Expression Language layer and not need the ORM. However, if you need to do much more than basic manipulation of the data, the ORM can make your life much easier. 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 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: renaming columns
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Michele Simionato
Sent: 17 August 2009 16:11
To: sqlalchemy
Subject: [sqlalchemy] renaming columns
There should be an easy way to do this, but I cannot find it in the
documentation.
I would like to generate a query with a renamed column, something like
this:
SELECT acolumn AS newcol FROM atable
I tried things like
print atable.select().with_only_columns([atable.c.acolumn.as_
('newcol')])
What's the right syntax? Thanks,
M.S.
I think you want something like column.label('newcol'). For example:
import sqlalchemy as sa
print sa.select([atable.c.acolumn.label('newcol')])
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 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: new questions
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of darkblueB Sent: 19 August 2009 02:58 To: sqlalchemy Subject: [sqlalchemy] Re: new questions Hi Simon thanks for the reply.. Your second part is straightforward.. The first one, not so much.. I have gone back to a simple meta.bind = //my engine here meta.reflect() I can see meta.tables, and meta.tables_sorted() but how do I know what mapped objects exist? (there should be about 12 tables, with a few one to many and one many to many defined) I feel like time is passing by, I would like to use ORM but this is new to me thanks much -Brian Ah - I see what you mean now. meta.reflect() will only create Table objects (part of the SQL expression language layer). It doesn't automatically create mapped classes for you. If you want to use the declarative system, you would need to create at least a basic class for each of your reflected tables. For example (untested): import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base meta = sa.Metadata(bind='sqlite:///your.db') meta.reflect() Base = declarative_base(metadata=meta) class SomeObject(Base): __table__ = meta.tables['some_table'] class SomeOtherObject(Base): __table__ = meta.tables['some_other_table'] I suppose you could automate this by iterating over meta.tables and creating the classes in a loop. You would end up with something similar to SqlSoup (http://www.sqlalchemy.org/trac/wiki/SqlSoup). I suppose it should be possible to automatically create ORM-level relations by inspecting your foreign keys as well if you really wanted. Simon --~--~-~--~~~---~--~~ 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: table creation oddity
Faheem Mitha wrote:
Hi,
The following script is then followed by its output, and
finally by the
table output.
I don't get what is going on here. Yes, I should commit the
session, and
the table is empty as expected, but why does the id keep
incrementing on
successive runs, and where is this table living, if not in
the db? I'd
expect to see the id stay at 1. Also, I'd expect to see something in
session.dirty. Deleting the table resets the counter back to 1, so it
looks like it is using the table in some way, but as already
stated, the
table shows as empty via a select * command.
If anyone can clarify what is going on here and satisfy my
curiosity, I'd
appreciate it. Please CC me on any reply. Thanks.
Regards, Faheem.
***
oddity.py
***
from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker
def add_obj(session, obj):
Check if object primary key exists in db. If so,exit, else
add.
from sqlalchemy import and_
from sqlalchemy.orm import object_mapper
mapper = object_mapper(obj)
pid = mapper.primary_key_from_instance(obj)
criterion = and_(*(col == val for col, val in
zip(mapper.primary_key,
mapper.primary_key_from_instance(obj
if session.query(obj.__class__).filter(criterion).count() 0:
print %s object with id %s is already in
db.%(type(obj).__name__, pid)
exit
else:
session.add(obj)
metadata = MetaData()
mytest_table = Table(
'mytest', metadata,
Column('id', Integer, primary_key=True),
)
class MyTest(object):
def __init__(self):
pass
mapper(MyTest, mytest_table)
dbstring = postgres://username:pas...@localhost:5432/oddity
db = create_engine(dbstring)
metadata.bind = db
metadata.create_all()
conn = db.connect()
Session = sessionmaker()
session = Session()
t1 = MyTest()
add_obj(session, t1)
print session.query(MyTest).count()
stmt = mytest_table.select()
for row in stmt.execute():
print row
stmt = select([mytest_table.c.id])
print anno statement is %s\n%stmt
for row in stmt.execute():
print row
print session.dirty is %s%session.dirty
#session.commit()
#session.flush()
#conn.close()
*
script output
*
$ python oddity.py
1
(1,)
anno statement is SELECT mytest.id
FROM mytest
(1,)
session.dirty is IdentitySet([])
$ python oddity.py
1
(2,)
anno statement is SELECT mytest.id
FROM mytest
(2,)
session.dirty is IdentitySet([])
table output
oddity=# select * from mytest;
id
(0 rows)
I've never used postgres, but I believe auto-incrementing counters are
implemented using database sequences. I think these are incremented
outside of a transaction - this ensures that two seperate database
connections using the sequence at the same time will get distinct
values. So although you aren't commiting your transaction, the sequence
still advances.
I guess the sequence must be associated with the table, so when you drop
the table it destroys the sequence as well (I don't know if this is SA
behaviour or PG behaviour).
session.dirty only contains objects that have been loaded from the DB
and subsequently modified. You don't ever actually modify your object,
so it shouldn't appear in session.dirty. (It should appear in
session.new though)
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 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: index in SA
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 21 August 2009 10:25
To: sqlalchemy
Subject: [sqlalchemy] Re: index in SA
i tried
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type', name , type ,unique=True)
)
it errors out
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1476, in append_column
self._set_parent(column.table)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1469, in _set_parent
self.metadata = table.metadata
AttributeError: 'NoneType' object has no attribute 'metadata'
thanks
The problem is that at the time you are calling Index, the table
object doesn't exist. Apparently the Index object doesn't work with
declarative in this way.
However, if you just move your Index definition outside the class
definition completely, I think it should be fine.
ie.
class MyClass(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
Index('ix_name_type', MyClass.name, MyClass.type, unique=True)
Simon
--~--~-~--~~~---~--~~
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: table creation oddity
Faheem Mitha wrote:
Thanks for the fast and helpful response. This looks like an artifact
of
how I am creating the table. I wonder if this would still show up if I
explicitly specified the id. I could check this. Also, presumably if I
had
other cols in the table, they wouldn't show up in sqla's printout.
The sequence isn't directly related to the table (as far as I'm aware).
Instead, when you insert a row into the table, you do something like
SELECT NEXTVAL('sequencename') to get the next ID value. SQLALchemy
does this automatically for you.
I'm still puzzled why sqlalchemy lists the entries in the table, when
querying directly via psql doesn't show it.
Your SQLAlchemy operations are happening within a transaction that never
gets committed. If you turned on SQL echoing (use echo=True or
echo='debug' in your call to create_engine), you would see that your
object is actually being inserted into the 'mytest' table. When you
SELECT from the table, you are still in the same transaction, so you see
the rows that you have inserted.
However, when the script exits, the transaction gets rolled back, so you
never see the new rows in psql. The only evidence that anything ever
happened is that the sequence has moved on (see the note at the bottom
of http://www.postgresql.org/docs/8.1/static/functions-sequence.html)
Simon
--~--~-~--~~~---~--~~
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: negative indexes in query slices?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of john smallberries Sent: 24 August 2009 08:51 To: sqlalchemy Subject: [sqlalchemy] negative indexes in query slices? I just tried limiting a query to the final 10 items of a 30010 item table using this slice notation: q = some.Session.query() items = q[-10:] the resulting mysql query had no limit or offset clause. Changing the form to items = q[q.count()-10:] produced: select from data LIMIT 3, 18446744073709551615 (which worked fine in practice). Changing the form to items = q[q.count()-10:q.count()] produced the desired: select from data LIMIT 3, 10 Is that the expected behavior for negative indices? I am using SA 0.5.4p2 I think the problem is that there is no way of specifiying negative indices in the SQL LIMIT clause (at least for MySQL - I don't know about other databases), so it would have to be emulated in some way. The only choices I can think of are: a) Execute a count() first, as you did above b) Use a subquery in which the sort order is reversed Both would have to transform the query substantially, so I'm not sure if it's the sort of thing that SA should do automatically. Simon --~--~-~--~~~---~--~~ 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: ForeignKey on a ForeignKey
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Laurent Rahuel
Sent: 24 August 2009 12:16
To: sqlalchemy
Subject: [sqlalchemy] ForeignKey on a ForeignKey
Hi all,
I'm stucked with a problem I'm not able to solve (SA 0.5.2).
I hope on
of you would be smarter than me.
Here is the problem:
import sqlalchemy as sa
__metadata__ = sa.MetaData()
OBJECTS = sa.Table('objects', __metadata__,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('parent_id', sa.ForeignKey('objects.id',
ondelete='CASCADE', onupdate='CASCADE')),
sa.Column('name', sa.Text, index=True, nullable=False,
default='Root'),
)
OBJECTSTREE = sa.Table('objects_tree', __metadata__,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('child_id', sa.ForeignKey('objects.id',
ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
sa.Column('parent_id', sa.ForeignKey('objects.parent_id',
ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
sa.Column('depth', sa.Integer, nullable=False,
index=True, default=0),
)
When I call the create_all() method from metadata, I always get this
error - sqlalchemy/types.py, line 375, in get_col_spec raise
NotImplementedError()
I think your Column definitions are wrong - the second parameter to
Column should be the column type (Integer, String etc.). For foreign
keys, you can pass None, in which case the type will be the same as the
column that the key is pointing at.
eg.
sa.Column('child_id', None, sa.ForeignKey'objects.id',
ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)
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 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
-~--~~~~--~~--~--~---
