Re: Init alembic at specific revision
Am 29.03.2014 23:33, schrieb gbr: There's one thing though that I haven't been able to work out yet (maybe this feature doesn't exist). How about the stamp() command? http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch fs -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MySQL: You can't specify target table 'bar' for update in FROM clause
Am 28.02.2014 18:34, schrieb Michael Bayer: first off, there’s nothing wrong with the SQL in general, the statement as is works in Postgresql, SQLite. So MySQL doesn’t like “bar” stated twice. easy enough just use an alias(): (...) Thank you very much. Always a please working with SQLAlchemy :-) fs signature.asc Description: OpenPGP digital signature
[sqlalchemy] MySQL: You can't specify target table 'bar' for update in FROM clause
Hey,
I have a simple SQLAlchemy (0.8.4) insert command (with a subquery which
refers to the same table). Basically I want to use the max value of a column
and use that in an insert.
With MySQL this fails:
(1093, You can't specify target table 'bar' for update in FROM clause)
'INSERT INTO bar (position) VALUES ((SELECT max(bar.position) AS max_1 \nFROM
bar))'
The problem is well explained in a StackOverflow answer [1]: I need to use an
alias for the bar table in the SELECT sub query. Instead of
(SELECT max(bar.position) AS max_1 \nFROM bar)
SQLAlchemy should emit something like
(SELECT max(foo.position) FROM bar as foo)
I found a very old thread (2007) which refers to correlate but Mike stated
that r2515 should have auto-correlation (and manually adding the call does not
change anything).
It looks to me as if SQLAlchemy adds a unnecessary alias for the max
expression but does not do so for the actual table name.
My reproduction script is at the end of this email. Is this is a missing
feature in SQLAlchemy or just some lack of knowledge on my part?
fs
[1] http://stackoverflow.com/a/14302701/138526
#!/usr/bin/env python
from sqlalchemy import create_engine, func, sql
from sqlalchemy.schema import Column, MetaData, Table
from sqlalchemy.types import Integer
metadata = MetaData()
bar = Table('bar', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('position', Integer),
)
engine = create_engine('mysql://...:...@localhost/foo')
metadata.bind = engine
metadata.create_all()
connection = engine.connect()
connection.execute(
bar.insert().\
values({
'position': sql.select([func.max(bar.c.position)])
})
)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] MySQL: You can't specify target table 'bar' for update in FROM clause
btw.: This also happens with SQLAlchemy 0.9.3 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] how to define a custom type with a dialect-specific implementation?
I'm trying to get rid of SQLAlchemy's warning regarding problematic storage of numeric/decimal objects in sqlite. My idea was to build a custom type which is backed by a string on sqlite but uses the native DB types when available. Well, the basics (dialect-specific conversion) seem to be easy enough but I'm not sure how I can create the right colspec when initializing the new db (for example using 'alembic upgrade --sql') sqlalchemy.types.UserDefinedType has a method get_col_spec() but that method has no information about the dialect or dbapi. SQLAlchemy's built-in types seem to use a visitor-pattern where the dialect implementation decide on the output - that makes it hard to define a custom column type which I have in mind as I want to keep the default behavior in all places except a few which I have under direct control. What puzzles me is that there seem to be a few methods which look right but these are not called during an alembic upgrade, e.g. 'get_dbapi_type', 'dialect_impl' and 'load_dialect_impl'. fs -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] IntegrityError when deleting item mapped with association_proxy
I'm using SQLAlchemy's association_proxy to have extensible metadata for my
mapped classes.
So for example I can say:
user.meta[u'foo'] = u'bar'
However when I try to delete an individual item I get an IntegrityError:
users_meta.user_id may not be NULL ...
So likely I'm missing some 'cascade' configuration in my schema setup or so
and I'm glad for any pointers.
I attached a minimal script to reproduce the issue. Thank you very much,
fs
PS: I found
http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#sqlite-foreign-keys
but that doesn't seem to make a difference in my case
(sqlite-3.7.13-2.fc18.x86_64).
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy import Column, ForeignKey, Table, UniqueConstraint, MetaData
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.types import Unicode, Integer
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm.collections import attribute_mapped_collection
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', Unicode(250), nullable=False),
)
users_meta = Table('users_meta', metadata,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('user_id', Integer,
ForeignKey('users.id', onupdate='CASCADE', ondelete='CASCADE'),
index=True,
nullable=False),
Column('key', Unicode(64), nullable=False),
Column('value', Unicode(250), default=None),
UniqueConstraint('user_id', 'key'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
class UserMeta(object):
def __init__(self, key, value):
self.key = key
self.value = value
class User(object):
meta = association_proxy('_meta', 'value', creator=UserMeta)
mapper(
User, users,
properties={
'_meta': relation(
UserMeta,
collection_class=attribute_mapped_collection('key'),
passive_deletes=True,
),
},
)
mapper(UserMeta, users_meta)
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(sqlite:///:memory:)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = User()
user.name = u'foo'
user.meta[u'key'] = u'bar'
session.add(user)
session.commit()
del user.meta[u'key']
session.commit()
Re: [sqlalchemy] IntegrityError when deleting item mapped with association_proxy
Am 21.06.2013 20:12, schrieb Michael Bayer: just add a cascade directive to your relationship: ... thanks a lot. I feel kind of stupid right now as I used exactly that in another project - but somehow I was unable to remember the right solution. anyways: thanks. Felix -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] reflection failure with MySQL: Mapper could not assemble any primary key columns for mapped table
Am 12.03.2013 15:43, schrieb Michael Bayer: I suppose the reflection could have some feature where you flag it as, single unique key w/no primary key means that's the primary key, but we've not had requests for this feature before. We will at some point be adding a feature whereby one can intercept the event where we reflect the primary key; we have this event for individual columns now. When that event is present, you'll be able to flip on the primary key flag for unique column sets as needed. That sounds very helpful - looking forward to every new version of SQLAlchemy :-) fs -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] reflection failure with MySQL: Mapper could not assemble any primary key columns for mapped table
Am 11.03.2013 17:27, schrieb Michael Bayer: OK well that table has no primary key established. I see. So even if MySQL tells me (in 'show fields') that a column is a primary key, SQLAlchemy won't recognize it unless the column is explicitely marked as primary key (as opposed to a unique key). Makes sense, still a pitty that my DB schema is so broken (it's an old MediaWiki dump). fs -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] reflection failure with MySQL: Mapper could not assemble any primary key columns for mapped table
Hey,
I'm trying to use reflection with SQLAlchemy 0.8 but I always get this
exception:
sqlalchemy.exc.ArgumentError: Mapper Mapper|links|links could not assemble any
primary key columns for mapped table 'links'
mysql show fields from links;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| l_from | int(8) unsigned | NO | PRI | 0 | |
| l_to | int(8) unsigned | NO | PRI | 0 | |
++-+--+-+-+---+
2 rows in set (0.00 sec)
The class definition is pretty simple:
class links(Base):
__table__ = Table('links', Base.metadata, autoload=True)
Traceback:
File …/main.py, line 36, in connect_to_db
class links(Base):
File …/sqlalchemy/ext/declarative.py, line 1343, in __init__
_as_declarative(cls, classname, cls.__dict__)
File …/sqlalchemy/ext/declarative.py, line 1336, in _as_declarative
**mapper_args)
File …/sqlalchemy/orm/__init__.py, line 1129, in mapper
return Mapper(class_, local_table, *args, **params)
File …/sqlalchemy/orm/mapper.py, line 203, in __init__
self._configure_pks()
File …/sqlalchemy/orm/mapper.py, line 773, in _configure_pks
(self, self.mapped_table.description))
Any idea why this happens and how I fix the problem? (without having to
specify the ORM mapping myself)
fs
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: reflection failure with MySQL: Mapper could not assemble any primary key columns for mapped table
can you send the SHOW CREATE TABLE, I'll copy it exactly CREATE TABLE `links` ( `l_from` int(8) unsigned NOT NULL DEFAULT '0', `l_to` int(8) unsigned NOT NULL DEFAULT '0', UNIQUE KEY `l_from` (`l_from`,`l_to`), KEY `l_to` (`l_to`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 (sorry for breaking the threading, due to some misconfiguration I saw Michael's mail only in the web interface.) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Is MySQL 5.5 supported by SQLAlchemy? (Ubuntu 12.04)
Am 12.01.2013 23:47, schrieb Michael Bayer: As I don't have the resources to maintain that text across changing MySQL versions, as well as the fact that the compatibility section is much more specific than what is present for any other dialect, Is that something were the community could help? For example if we had some kind of test suite to run against different MySQL servers I could easily run that regularly against MySQL 4.1, 5.0, 5.1 and 5.5. fs -- 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] Unicode warnings - show offending value
Am 10.01.2013 00:45, schrieb Michael Bayer: Can't, because as a python warning, the warnings lib caches that message permanently, to support the typical case that the warnings filter is set to once. If the message were unique for every value your app would slowly leak memory I see, bad luck. I'll continue to modify my SQLAlchemy ad hoc then. fs -- 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] events: Which attributes did change (after_commit)?
Am 09.01.2013 17:20, schrieb Michael Bayer: the after_commit() event is independent of any flush, so the last event where the history API still reports the changed attributes on individual instances is after_flush(). If you need the aggregate of all attributes that were changed over many flushes within the span of a transaction, you'd probably want to use after_flush() and aggregate them all together somewhere. Is there a reasonably safe place in the session where I could store these? My line of thought is that all this info is specific to a transaction so I'd prefer storing it somewhere directly attached to that transaction. In my ideal world I would not have to do any state tracking (rollbacks, ...) because I just aggregate and only act on final success. Python's reference counting could deal with aborted transactions etc. checking attribute history in 0.7 is via attributes.get_history(): http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html?highlight=get_history#sqlalchemy.orm.attributes.get_history Tried that earlier but didn't see the info I needed. Probably because I was using the wrong event. I'll try again later. fs -- 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] Unicode warnings - show offending value
sometimes I get unicode warnings from SQLAlchemy (Unicode type received non-unicode bind param value). I find it very hard to fix these issues because I don't know where to look (often it's not a string literal explicitely in the code but some external library which returns a string instead of a unicode string). Could SQLAlchemy maybe add repr(value) to the unicode warning? The downside would be that people who want to ignore these had to actually configure a warning filter instead of just ignoring one line in the output. fs -- 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] events: Which attributes did change (after_commit)?
Hi, my question is how/if I can detect which attributes were changed when I get the 'after_commit' event. (I think it isn't possible based on what I read in the docs but I'd like to be sure.) If not, which other event could I use? What I want to achieve: I want to update an external search index incrementally after a commit. Ideally I can check if one of the attributes changed which are relevant for searching. fs -- 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] events: Which attributes did change (after_commit)?
Hi, my question is how/if I can detect which attributes were changed when I get the 'after_commit' event. (I think it isn't possible based on what I read in the docs but I'd like to be sure.) If not, which other event could I use? What I want to achieve: I want to update an external search index incrementally after a commit. Ideally I can check if one of the attributes changed which are relevant for searching. fs -- 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] Recovering from MySQL OperationalError 2006 - Server has gone away
Hey, I know this is a common topic and it has been discussed before. However even though I spent a considerable amount of time I was unable to fix my issue. Situation: When the MySQL server is restarted I get an OperationalError (as expected) on the next request. The application uses a scoped session [1]. As far as I know the suggested way for recovery is to add some code like this in the WSGI middleware stack (using Pylons): try: app(...) finally: DBSession.remove() That's already in my code and I can see that the code is executed.. However under certain circumstances I still get the OperationalError on the next request which puzzles me. Somehow I am under the impression that my app [2] might use multiple DB connections somehow and I'm removing the wrong connection in my finally-handler. Does that somehow makes sense? If I limit the connection pool to 1 without any overflow I get a TimeoutError from the ToscaWidgets stack somewhere. Does that mean my application really wants multiple DB connections? As far as I am aware I'm only accessing the DB using a central 'DBSession' object. Is there a way to find out which actual connection 'DBSession' refers to? Thank you very much for reading this post. Any pointers/suggestions appreciated. fs [1] SQLAlchemy session setup: maker = sessionmaker() DBSession = scoped_session(maker) [2] I'm using MediaCore (www.mediacorecommunity.org), an Open Source web application written in Python. I am using SQLAlchemy 0.6.8, MySQLdb 1.2.3, MySQL server 5.5.28, Python 2.7. -- 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] Recovering from MySQL OperationalError 2006 - Server has gone away
Hey,
I just learned about the pool events and with their help I can see that
MediaCore will retrieves multiple connections from the pool but not all
connections are properly returned by the end of the request...
In the end the culprit was a bad ToscaWidgets form:
CheckBoxList('groups', label_text=N_('Groups'),
options=Group.custom_groups(...).all),
That somehow got a new connection from the pool at class load time (= when
MediaCore was started) but the connection was never returned.
Just writing these posts somehow help understanding/solving the problem -
though I am sorry that I didn't find out until after sending the message.
fs
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Query to get all items from a table which have no reference to another table?
Hi, I have a table B which is connected to table A through a integer foreign key. This is a 1:1 relation so for every item in table B there is exactly one item in table A. But not every item in A must have a connection to B. I stored the foreign key in table B and using it as a primary key (that was the easiest way to ensure that there is only one item in B for every item in A). No I want to get all items from table A where no matching item in table B exists. I'm aware that this would be very easy if table A stores the foreign key. I think there is no direct sql query which can do that for me, right? So I have to query for every item in table A that there is no matching item in B? (I just want to make sure that I'm not missing a really obvious optimization.) Thanks so far, fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: New records won't be committed (ORM, Elixir)
Thank you very much for your reply - lightning fast as always :-) Gaetan de Menten schrieb: Other than that, maybe you are (or Turbogears is) starting a transaction somewhere (with session.begin()) without ever commiting it? Thanks for your hint. Indeed I used session.begin() after my session.flush() and obviously did not cause a COMMIT (why?) although there were objects to save. fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: New records won't be committed (ORM, Elixir)
jason kirtland wrote: SA won't automatically commit a transaction you've begun. You need to .commit() explicitly. As Michael said, .commit() will flush changes in the session before committing, so you could switch to calling that. It can be useful in a transaction to issue a simple .flush(). It updates the database with the Python-side changes queued up in the unit of work, and those updated rows are visible to subsequent queries within the transaction. The changes won't be visible to other db users until (and if) the transaction is eventually committed. Thank you very much for your detailed explanation! Now I do understand why my session.flush() did not work. But (from unitofwork.py) def flush(...): ... session.create_transaction(autoflush=False) flush_context.transaction = session.transaction try: flush_context.execute() ... except: session.rollback() raise session.commit() So I thought that flush should issue a commit? fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] String type with no length deprecation warnings in 0.4.2b
Hi,
I just upgraded to 0.4.2b (from 0.4.2) and now I get deprecation warnings
like this:
/usr/lib/python2.4/site-packages/sqlalchemy/databases/sqlite.py:389:
SADeprecationWarning: Using String type with no length for CREATE TABLE is
deprecated; use the Text type explicitly
colspec = self.preparer.format_column(column) + +
column.type.dialect_impl(self.dialect).get_col_spec()
I do understand why this warning is issued but I think there are two bugs left:
1. Even if I use the TEXT/Text type, the warning is issued (see example below):
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', sqlalchemy.types.Text)
)
2. Text is not exported in sqlalchemy/__init__.py, only TEXT is:
from sqlalchemy.types import \
BLOB, BOOLEAN, CHAR, CLOB, DATE, DATETIME, DECIMAL, FLOAT, INT, \
NCHAR, NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, VARCHAR, \
Binary, Boolean, Date, DateTime, Float, Integer, Interval, Numeric, \
PickleType, SmallInteger, String, Time, Unicode
fs
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: String type with no length deprecation warnings in 0.4.2b
Michael Bayer schrieb: can you try r4030 please Thank you very much, r4030 fixes the described problems. :-) fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy generates invalid SQL query with empty or_()
Hi, I noticed that SQLAlchemy 0.4.2 generates invalid SQL queries if I use or_() [without parameters] in a filter condition: or_conditions = [] condition = and_(User.c.name==John, or_(*or_conditions)) query = session.query(User).filter(condition) The generated SQL is: SELECT users.id AS users_id, users.name AS users_name FROM users WHERE users.name = ? AND () ORDER BY users.oid And executing this query will cause an exception: File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 847, in _execute_compiled self.__execute_raw(context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 859, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 875, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.OperationalError: (OperationalError) near ): syntax error u'SELECT users.id AS users_id, users.name AS users_name, users.no_phone AS users_no_phone, users.no_mail AS users_no_mail \nFROM users \nWHERE users.name = ? AND () ORDER BY users.oid' ['John'] I'm not sure if this is considered as a bug but I noticed that this code does not trigger an exception: or_conditions = [] condition = or_(*or_conditions) query = session.query(User).filter(condition) Thank you very much for SQLAlchemy :-) fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy generates invalid SQL query with empty or_()
Christoph Haas wrote: Since you can chain filters together I'd suggest something like: query = session.query(User).filter(User.c.name==John) if or_conditions: query = query.filter(or_(*or_conditions)) Yes, I use a similar workaround in my application currently. But I was not sure if the described behavior is considered buggy - despite easy workarounds are available. fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Minor documentation mistake related to Unicode?
Hi, http://www.sqlalchemy.org/docs/04/types.html says: The Unicode type is shorthand for String with convert_unicode=True and assert_unicode=True. But the constructor for String (types.py) says: def __init__(self, length=None, convert_unicode=False): Using TEXT(assert_unicode=True) raises an error. I think assert_unicode was removed in the past, correct? fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: LIMIT in queries
Chris M wrote: Or you could use .limit(1). I've always found that more clear than the Python indexing notation because I think of it slicing a list, not applying a limit to a query. Thank you very much for your help. I already found limit before my posting but it did not work, because I used .limit(1).one() where .one() will override the effect of limit(1). After reading your answer I just digged a bit deeper :-) fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: LIMIT in queries
Mike Orr wrote: Are there that many of those, given that I don't think [N] is even documented? It is in the official documentation: http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: IMPORTANT: Does SA caches objects in memory forever?
Michael Bayer schrieb: in version 0.4, the session is weak referencing so that objects which are not elsewhere referenced (and also are not marked as dirty or deleted) fall out of scope automatically. that is documented at: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_using_attributes I have a question which I think is similar enough to be asked in the same thread: I have a set of quite simple migration scripts which us SQLAlchemy 0.4 and Elixir 0.4. I do extract data from the old legacy (MySQL) database with SQLAlchemy and put this data into new Elixir objects. Currently, these scripts use up to 600 MB RAM. This is no real problem as we probably could devote a machine with 4 GB ram solely for the automated migration. But it would be nice to use lower-powered machines for our migration tasks. What wonders me is that I do not (knowingly) keep references neither to the old data items nor the new elixir objects. Nevertheless memory usage increases during the migration. Is there any way to debug this easily to see why Python does need so much memory/which references prevent the objects from being garbage collected? Running the garbage collector manually did not help much (saving only about 5 MB). fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] 0.4 does not work with wierd table/column names - regression or pebkac?
Hi,
I tried to migrate to SQLAlchemy 0.4 but unfortunately, 0.4 does
not seem to work with the weird table and column names I have
in my legacy database.
I believe this behavior is a regression because the script below
worked for me with 0.3.10. I got something horribly wrong (still
learning ;-) but I'm very confident somebody can show me the
correct solution if it is my fault :-))
#!/usr/bin/env python
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
myengine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData(myengine)
table_name = 'tbl-Foo+ID'
users_table = Table(table_name, metadata,
Column('id-Foo+ID', Integer, primary_key=True),
Column('name', String(40)))
metadata.create_all()
fooid_table = Table(table_name, metadata, autoload=True)
id_column = fooid_table.c.get('id-Foo+ID')
result = fooid_table.select(id_column==42).execute()
Traceback (most recent call last):
File ./regression.py, line 21, in ?
result = fooid_table.select(id_column==42).execute()
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/expression.py, line
971, in execute
return e._execute_clauseelement(self, multiparams, params)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line
1122, in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 829,
in _execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 840,
in _execute_compiled
context.pre_execution()
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py, line
202, in pre_execution
self.pre_exec()
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py, line
215, in pre_exec
self.parameters = self.__convert_compiled_params(self.compiled_parameters)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py, line
187, in __convert_compiled_params
parameters = [p.get_raw_list(processors) for p in parameters]
File /usr/lib/python2.4/site-packages/sqlalchemy/sql/util.py, line 83, in
get_raw_list
res.append(binds[key][2])
KeyError: 'tbl'
I think the problem is in sqlalchemy/sql/compiler.py with the
regular expression in BIND_PARAMS. The regex extracts the value 'tbl'
in the first match group from the generated query
SELECT tbl-Foo+ID.id-Foo+ID, tbl-Foo+ID.name
FROM tbl-Foo+ID
WHERE tbl-Foo+ID.id-Foo+ID = :tbl-Foo+ID_id-Foo+ID
(see DefaultCompiler.after_compile())
I suspect that the regex
BIND_PARAMS = re.compile(r'(?![:\w\$\x5c]):([\w\$]+)(?![:\w\$])',
re.UNICODE)
should be extended so that it accepts '-', '+' and other unusual #
characters.
So after all, it looks like a bug to me but if someone knows a
workaround, that would be fine, too :-)
fs
PS: Thank you all very much for the first-class support here!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4 does not work with wierd table/column names - regression or pebkac?
Michael Bayer schrieb: great news, that regex is gone in the trunk. try out the SVN trunk. Thank you very much - trunk fixes my problems :-) also yeah those column names are super wacky. :) I'm happy already if they don't contain broken umlauts characters or spaces :-) fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Deletion of referenced objects fails
Hi,
I have a problem using SQLAlchemy 0.4 when deleting referenced
objects in a PostgreSQL database and adding new ones within the same
transaction.
Originally, I found the problem with Elixir 0.4.0-pre (svn r216) and
SQLAlchemy 0.3.11 -
http://groups.google.com/group/sqlelixir/browse_thread/thread/b03a96e3ba9e61ea
Probably the problem can only be reproduced using a database which enforces
ForeignKey constraints (Postgresql does).
I don't know much about SQLAlchemy but I tried to create an SQLAlchemy script
which
reproduces the behavior (the original Elixir test case can be found on
http://pastebin.com/f3307e3c0 ). I hope the script exhibits really the same
problem as my Elixir script...
Example snippet (complete script http://pastebin.com/f6057bdbf ):
---
foo = session.query(User).filter_by(name='Foo Bar').one()
session.save(foo)
for address in foo.addresses:
foo.addresses.remove(address)
session.delete(address)
session.delete(foo)
foo = User()
session.save(foo)
foo.id = 1
foo_addr = Address()
session.save(foo_addr)
foo_addr.street = Picadelly Circus
foo.addresses.append(foo_addr)
transaction.commit()
---
This gives me the following traceback (complete output:
http://pastebin.com/f28f0e198 ,
original Elixir traceback http://pastebin.com/f5ae5c7c ):
---
Traceback (most recent call last):
File ./sqlalchemy_foreignkeys.py, line 88, in ?
transaction.commit()
...
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) Aktualisieren oder
Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint
»users_addresses__Address_Address_id_fkey« von Tabelle
»users_addresses__Address«
DETAIL: Auf Schlüssel (id)=(1) wird noch aus Tabelle
»users_addresses__Address« verwiesen.
'DELETE FROM Address WHERE Address.id = %(id)s' {'id': 1}
---
Sorry for the German exception message, I did not manage to get an English one
despite
switching the system locale to en_US. Here is a rough translation to English:
Update or deletion of table »Address« violates foreign key
constraint »users_addresses__Address_Address_id_fkey« of table
»users_addresses__Address«
DETAIL: Table »users_addresses__Address« still references key (id)=(1).
SQL trace:
---
BEGIN
SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name = %(users_name)s ORDER BY users.id LIMIT 2 OFFSET 0
{'users_name': 'Foo Bar'}
SELECT Address.id AS Address_id, Address.street AS Address_street
FROM Address, users_addresses__Address
WHERE %(param_1)s = users_addresses__Address.user_id AND
users_addresses__Address.Address_id = Address.id
{'param_1': 1}
UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s
{'users_id': 1, 'name': None}
select nextval('Address_id_seq')
None
INSERT INTO Address (id, street) VALUES (%(id)s, %(street)s)
{'street': 'Picadelly Circus', 'id': 2L}
INSERT INTO users_addresses__Address (user_id, Address_id) VALUES
(%(user_id)s, %(Address_id)s)
{'Address_id': 2L, 'user_id': 1}
DELETE FROM Address WHERE Address.id = %(id)s
{'id': 1}
ROLLBACK
---
I think the problem is the order of the SQL deletion statements. The item in
»users_addresses__Address«
must be deleted before deleting the address.
Is this behavior by design? Do I abuse the SQLAlchemy api?
fs
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Built-in escape function?
Hi, Paul Johnston wrote: If you create a ticket I'll look at it sometime, although not for a couple of weeks (I'm on holiday, woo :-) [x] done, http://www.sqlalchemy.org/trac/ticket/791 fs --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy: like and security (sql injection attacks)
Hi,
I have a question related to sql injection when using a clause like
this: User.c.username.like('%' + userinput + '%')
What restrictions do I have to put on the variable userinput? Of course,
I will ensure that is no percent character ('%') in userinput. Is that
enough (assuming that SQLAlchemy will do the rest by applying
database-specific quoting rules) or do I need to filter more characters?
Is this specific for database used?
Thank you very much
fs
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
