[sqlalchemy] open session blocks metadata create_all method
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. Regards, Faheem. ## from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy import MetaData meta = MetaData() def make_foo(meta): foo = Table( 'foo', meta, Column('id', Integer, nullable=False, primary_key=True), ) return foo def make_bar(meta): bar = Table( 'bar', meta, Column('id', Integer, ForeignKey('foo.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False, primary_key=True), ) return bar dbuser = password = dbname = dbstring = postgres://%s:%...@localhost:5432/%s%(dbuser, password, dbname) from sqlalchemy import create_engine db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() Session = sessionmaker() session = Session(bind=db) session.execute(select * from foo;) make_bar(meta) meta.create_all() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[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? Regards, Faheem -- 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, 2010-07-28 at 11:33 +, Faheem Mitha wrote: 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? I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say. Another option would be to simply not use a session, but instead just a *connection*. Sessions are specific to the ORM which, according to the code you posted, you are not using. So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this: db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() db.connect().execute(select * from foo;) make_bar(meta) meta.create_all() Lance -- 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] association_proxy problem
Hi! When trying to remove element from association_proxy exception occure. Here is code example: ## CODE EXAMPLE # from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm import scoped_session, sessionmaker, relation, backref engine = create_engine('sqlite://') meta = MetaData() meta.bind = engine class Base(declarative_base(metadata=meta)): _decl_class_registry = {} def __init__(self, *args, **kwargs): for key, val in kwargs.iteritems(): self.__setattr__(key, val) class Client(Base): __tablename__ = 'client' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) groups = association_proxy('client_group', 'group', creator=lambda x: ClientGroup(client=x)) class Group(Base): __tablename__ = 'group' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) clients = association_proxy('client_group', 'client', creator=lambda x: GroupClient(client=x)) class GroupClient(Base): __tablename__ = 'clientgroup' client_id = Column('client_id', Integer, ForeignKey('client.id'), primary_key = True) group_id = Column('group_id', Integer, ForeignKey('group.id'), primary_key = True) client = relation(Client, backref = backref('client_group')) group = relation(Group, backref = backref('client_group')) meta.create_all() session = scoped_session(sessionmaker(bind=engine)) clnt = Client(name='test') session.add(clnt) grp = Group(name='tg') session.add(grp) grp.clients.append(clnt) #session.flush() #grp.clients.remove(clnt) clnt.groups.remove(grp) session.flush() # -- 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] Updating db schema without using migrate
Hi there, I'm using sqlalchemy 0.5 with python 2.5 for a desktop application. I know that if the database is empty sqlalchemy can create automatically all the tables needed. But what if i have to add some new tables or columns or delete columns ? I would like to update the schema in the startup of the application; is there a way that sqlalchemy can handle this issue ? I know there is sqlalchemy-migrate but i've some problems on integrating it into an exe (http://url-zip.com/ao7) and i'm searching some alternatives using only sqlalchemy (or other libraries, hints ?). Are there any way on doing that ? I have to support sqlite and postgresql so it would be great to maintain the dbs using the sqlalchemy dialect avoiding sql statement; but in the last chance i can use sql directly if there is no other way. Do i have to write from scratch a migration tool inside my application ? Or is there some approach that i can take as example ? Thanks for any help, Emanuele Gesuato -- 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] association_proxy problem
thanks for sending a nice, succinct example...very rare these days. Use this: class GroupClient(Base): __tablename__ = 'clientgroup' client_id = Column('client_id', Integer, ForeignKey('client.id'),primary_key = True) group_id = Column('group_id', Integer, ForeignKey('group.id'), primary_key = True) client = relation(Client, backref = backref('client_group', cascade='all, delete-orphan')) group = relation(Group, backref = backref('client_group')) On Jul 28, 2010, at 9:00 AM, DimonB wrote: from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm import scoped_session, sessionmaker, relation, backref engine = create_engine('sqlite://') meta = MetaData() meta.bind = engine class Base(declarative_base(metadata=meta)): _decl_class_registry = {} def __init__(self, *args, **kwargs): for key, val in kwargs.iteritems(): self.__setattr__(key, val) class Client(Base): __tablename__ = 'client' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) groups = association_proxy('client_group', 'group', creator=lambda x: ClientGroup(client=x)) class Group(Base): __tablename__ = 'group' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) clients = association_proxy('client_group', 'client', creator=lambda x: GroupClient(client=x)) class GroupClient(Base): __tablename__ = 'clientgroup' client_id = Column('client_id', Integer, ForeignKey('client.id'), primary_key = True) group_id = Column('group_id', Integer, ForeignKey('group.id'), primary_key = True) client = relation(Client, backref = backref('client_group')) group = relation(Group, backref = backref('client_group')) meta.create_all() session = scoped_session(sessionmaker(bind=engine)) clnt = Client(name='test') session.add(clnt) grp = Group(name='tg') session.add(grp) grp.clients.append(clnt) #session.flush() #grp.clients.remove(clnt) clnt.groups.remove(grp) session.flush() -- 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] Updating db schema without using migrate
On Jul 28, 2010, at 9:09 AM, Emanuele Gesuato wrote: Hi there, I'm using sqlalchemy 0.5 with python 2.5 for a desktop application. I know that if the database is empty sqlalchemy can create automatically all the tables needed. But what if i have to add some new tables or columns or delete columns ? I would like to update the schema in the startup of the application; is there a way that sqlalchemy can handle this issue ? only if you executed the requisite ALTER TABLE instructions yourself. I know there is sqlalchemy-migrate but i've some problems on integrating it into an exe (http://url-zip.com/ao7) that seems like a very minor issue in migrate - they are using some setuptools function internally...a little strange that they need to do that, and there is probably some way to work around it. too bad they didn't respond to you. and i'm searching some alternatives using only sqlalchemy (or other libraries, hints ?). I have a migrations tool under construction (http://bitbucket.org/zzzeek/alembic) but its not complete and I won't have time for a few months to start getting it online. It also will not be supporting SQLite since SQLite has no reasonable ALTER construct. Are there any way on doing that ? I have to support sqlite and postgresql so it would be great to maintain the dbs using the sqlalchemy dialect avoiding sql statement; but in the last chance i can use sql directly if there is no other way. Do i have to write from scratch a migration tool inside my application ? Or is there some approach that i can take as example ? I really think your best bet is to fix Migrate and propose a patch for them. -- 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] Making a copy of a table
Hi everyone, I'm getting in trouble in th attempt to create a simple copy (structure and data) of a table in my database (sql server). I need the SQLAlchemy equivalent of the query: SELECT * INTO newtable FROM table How can I achieve this? I found that tometadata probably could do the trick, but I cannot understand how to use it (I'm pretty new to SA). Can anyone help me? thanks in advance! -- 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] Re: open session blocks metadata create_all method
Hi Lance, On Wed, 28 Jul 2010 06:45:30 -0500, Lance Edgar lance.ed...@gmail.com wrote: --=-dKyzuPx4woj1H0B5IT48 Content-Type: text/plain; charset=ISO-8859-1 On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: 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? I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say. Another option would be to simply not use a session, but instead just a *connection*. Sessions are specific to the ORM which, according to the code you posted, you are not using. So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this: db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() db.connect().execute(select * from foo;) make_bar(meta) meta.create_all() The example was just an example. After going back and forth a bit, I've finally standardized on session as the thing to more around in my application. The db.connect thing works, I think, because autocommit is the default for connect. I'd like to hear an explanation of why create_all is blocked here. I periodically have my scripts hang for no apparent reason, almost always because the db is blocking something, so would like to become more educated on this issue. Regards, Faheem -- 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, 2010-07-28 at 17:17 +, Faheem Mitha wrote: Hi Lance, On Wed, 28 Jul 2010 06:45:30 -0500, Lance Edgar lance.ed...@gmail.com wrote: --=-dKyzuPx4woj1H0B5IT48 Content-Type: text/plain; charset=ISO-8859-1 On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: 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? I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say. Another option would be to simply not use a session, but instead just a *connection*. Sessions are specific to the ORM which, according to the code you posted, you are not using. So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this: db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() db.connect().execute(select * from foo;) make_bar(meta) meta.create_all() The example was just an example. After going back and forth a bit, I've finally standardized on session as the thing to more around in my application. The db.connect thing works, I think, because autocommit is the default for connect. I'd like to hear an explanation of why create_all is blocked here. I periodically have my scripts hang for no apparent reason, almost always because the db is blocking something, so would like to become more educated on this issue. Are your sessions contextual (created with scoped_session()) ? Not sure what kind of project you're working on (i.e. if you need a contextual session or not), but I use sessions and also have to create tables on the fly occasionally...but my sessions aren't contextual and I always create and close them immediately when finished. See When do I make a Session ? at http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions But I also apologize if I'm telling you nothing new, certainly don't mean to insult. Just trying to help. I assume Michael will have to explain the blocking thing, but FWIW I couldn't reproduce that issue while using SQLite or MySQL, so it might have just as much to do with PostgreSQL as anything else...whether that means its SA dialect or the server configuration itself I couldn't say. Lance -- 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] session.execute with autocommit option turned on is a no-op
Hi, In the following script, the last line, namely session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) doesn't drop the constraint. It does if autocommit is turned off, and a session.commit() is issued after the statement. The autocommit setting works with similar statements that are not wrapped up in a function, specifically session.execute(ALTER TABLE foo DROP CONSTRAINT foo_pkey;) I also notice that in debug mode, the db issues a COMMIT in the case of session.execute(ALTER TABLE foo DROP CONSTRAINT foo_pkey;) but not in the case of session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) so presumably the problem is that in this case SQLA is not, in fact, autocommitting for some reason. Clarifications appreciated. Regards, Faheem * from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy import MetaData create_drop_constraint_if_exists_function = text( CREATE OR REPLACE FUNCTION drop_constraint_if_exists (t text, k text, s text = NULL) returns void as $$ BEGIN IF s IS NOT NULL THEN EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade '; ELSE EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade '; END IF; EXCEPTION WHEN undefined_object THEN END; $$ LANGUAGE plpgsql; ) meta = MetaData() def make_foo(meta): foo = Table( 'foo', meta, Column('id', Integer, nullable=False, primary_key=True), ) return foo dbuser = password = dbname = dbstring = postgres://%s:%...@localhost:5432/%s%(dbuser, password, dbname) from sqlalchemy import create_engine db = create_engine(dbstring) meta.bind = db db.echo = 'debug' foo = make_foo(meta) meta.create_all() Session = sessionmaker(autocommit=True) session = Session(bind=db) session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) -- 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.execute with autocommit option turned on is a no-op
On Jul 28, 2010, at 2:33 PM, Faheem Mitha wrote: Hi, In the following script, the last line, namely session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) doesn't drop the constraint. It does if autocommit is turned off, and a session.commit() is issued after the statement. The autocommit setting works with similar statements that are not wrapped up in a function, specifically session.execute(ALTER TABLE foo DROP CONSTRAINT foo_pkey;) I also notice that in debug mode, the db issues a COMMIT in the case of session.execute(ALTER TABLE foo DROP CONSTRAINT foo_pkey;) but not in the case of session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) so presumably the problem is that in this case SQLA is not, in fact, autocommitting for some reason. Clarifications appreciated. Autocommit does not apply to all statements: http://www.sqlalchemy.org/docs/dbengine.html#understanding-autocommit Autocommit can be controlled on a per statement level: http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.Executable.execution_options for textual SQL, you'd use text() with autocomimt: http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.text Regards, Faheem * from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy import MetaData create_drop_constraint_if_exists_function = text( CREATE OR REPLACE FUNCTION drop_constraint_if_exists (t text, k text, s text = NULL) returns void as $$ BEGIN IF s IS NOT NULL THEN EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade '; ELSE EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k) || ' cascade '; END IF; EXCEPTION WHEN undefined_object THEN END; $$ LANGUAGE plpgsql; ) meta = MetaData() def make_foo(meta): foo = Table( 'foo', meta, Column('id', Integer, nullable=False, primary_key=True), ) return foo dbuser = password = dbname = dbstring = postgres://%s:%...@localhost:5432/%s%(dbuser, password, dbname) from sqlalchemy import create_engine db = create_engine(dbstring) meta.bind = db db.echo = 'debug' foo = make_foo(meta) meta.create_all() Session = sessionmaker(autocommit=True) session = Session(bind=db) session.execute(select * from drop_constraint_if_exists('foo', 'foo_pkey', 'public');) -- 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. -- 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] Re: mismatched parameter markers when running on Mac OS X
Thanks. Yes, I am indeed connecting to MS SQL. I got everything working after upgrading to SQLAlchemy 0.6.3. This required some rework, to rip out collective.lead (which does not support recent versions of SQLAlchemy) and replace it with z3c.saconfig. I am still using unixodbc, though, installed from buildout. Thanks again. Fulvio On Jul 20, 1:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 20, 2010, at 3:34 PM, fulv wrote: I have the following stack, which works fine on two different Linux systems, but produces the error below on a Mac OS X 10.6.4 machine: - Ubuntu 10.04 / Fedora 8 (works) - Mac OS X 10.6.4 (does not work) - freeTDS 0.82 - unixODBC 2.3.0 - pyodbc 2.1.7 - collective.lead 1.0 - SQLAlchemy 0.4.8 - Zope 2.10.11 - Plone 3.3.5 From the debugger (bin/instance debug) I can make simple connections and execute simple queries, both using pyodbc and sqlalchemy. Basically, it looks like it's breaking at this line in _setup_tables (which overrides collective.lead.Database's): tables['WebRegions'] = Table('lut_WebRegions', metadata, autoload=True) MS-SQL (I am guessing you're using MS-SQL based on the error message and the quoting style) support is extremely poor in SQLAlchemy 0.4.8, though this appears to be at least partially a FreeTDS issue. Also OSX does not use unixODBC as far as I know, it uses iODBC. You should at once be using SQLAlchemy 0.6.3 where our reflection tests pass just fine using FreeTDS + OSX + default iODBC install + MS-SQL. Any assistance or leads on where to go next would be greatly appreciated! Thanks! 2010-07-14 20:40:33 ERROR Zope.SiteErrorLog 1279165233.140.347153571577 a href=http://localhost:8080/mysite/@@database-controlpanel;http:// localhost:8080/mysite/@@database-controlpanel/a Traceback (innermost last): Module ZPublisher.Publish, line 119, in publish Module ZPublisher.mapply, line 88, in mapply Module Products.PDBDebugMode.runcall, line 70, in pdb_runcall Module ZPublisher.Publish, line 42, in call_object Module zope.formlib.form, line 769, in __call__ Module Products.Five.formlib.formbase, line 55, in update Module zope.formlib.form, line 750, in update Module zope.formlib.form, line 594, in success Module plone.app.controlpanel.form, line 41, in handle_edit_action Module mysite.content.browser.dbsettings, line 40, in _on_save Module collective.lead.database, line 49, in invalidate Module collective.lead.database, line 95, in _initialize_engine Module mysite.content.db, line 61, in _setup_tables Module sqlalchemy.schema, line 110, in __call__ Module sqlalchemy.schema, line 226, in __init__ Module sqlalchemy.engine.base, line 1275, in reflecttable Module sqlalchemy.databases.mssql, line 570, in reflecttable Module sqlalchemy.engine.base, line 844, in execute Module sqlalchemy.engine.base, line 895, in execute_clauseelement Module sqlalchemy.engine.base, line 907, in _execute_compiled Module sqlalchemy.engine.base, line 916, in __execute_raw Module sqlalchemy.engine.base, line 960, in _cursor_execute Module sqlalchemy.engine.base, line 942, in _handle_dbapi_exception ProgrammingError: (ProgrammingError) ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000') u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1]. [COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1]. [CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1]. [ORDINAL_POSITION]' ['lut_WebRegions', 'dbo'] /Users/fulvio/plone/mysite/buildout/eggs/SQLAlchemy-0.4.8-py2.4.egg/sqlalch emy/engine/base.py(944)_handle_dbapi_exception() - del self._reentrant_error -- 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 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 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] Re: Exists clause on relations of different mapped objects
On Jul 28, 11:37 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 27, 2010, at 9:59 PM, Kalium wrote: I'm trying to determine whether or not there are any value in one mapped object relation that correspond to another mapped object relation i.e A.x - represents a relation of x's on A B.x - represents a relation of x's on B They both use their own separate mapping tables for these relations. So I want to find out of any of A.x is also present in B.x how about query(x).filter(x.a_id==my_a.id).filter(x.b_id==my_b.id) ? thats assuming you're starting with a particular A and B. Opps, accidentaly replied to Michael and not the group. I wasn't dealing with a particular A and B. I wasn't successful in using the tables in the outer query so I merely reconstructed the where clause in the exists query that I used to join the outer tables in the first place, i.e A.x.any(B.x.any()).where(X.id == my_id) Thanks The closes I've had to it working was in the follwing fashion, by doing the following; exists([1],from_obj=join(A_x_mapping_table,B_x_mapping_table, onclause=A_x_mapping_table.c.id==B_x_mapping_table.c.id)) I put this exists clause into an existing filter() but it didn't correlate the tables with those in the outer query (which I really need it to do!), so I tried using correlate function to correlate the table on the join() but it didn't seem to do anything (I've used it in other situations with success but not this time). Also , A.x.any(B.x.any()) seems to almost do what I want as well, but again, doesn't correlate to outer query and because I'm using 0.4 it does not have a correlate() method in ORM! Any other hints as to how I can do this ? Thanks -- 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 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 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] 'collation' keyword to String() doesn't work
According to [1], the following should work: import sqlalchemy sqlalchemy.__version__ '0.6.3' sqlalchemy.String(14, collation='Latin1_General_CS_AS') Traceback (most recent call last): File stdin, line 1, in module TypeError: __init__() got an unexpected keyword argument 'collation' Thoughts? Trent. [1] http://www.sqlalchemy.org/docs/reference/dialects/mssql.html#collation-support -- 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.