Re: [sqlalchemy] use USING ( join_column [, ...] ) instead of ON join_condition
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 08/11/2012 00:32, Michael Bayer ha scritto: On Nov 7, 2012, at 6:18 PM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. When selecting against joined tables, SQLAlchemy always use the ON join_condition form. The problem with this is that when the joined columns have the same name, they are included 2 times in the result set. Only if you select all of those columns. There's no need to resort to side effects of unusual SQL syntaxes here, if you're already using the expression language to generate joins, then you should be specifying those columns you care about selecting when you call select(). I care about selecting all columns, but without having duplicates. And this is currently not possible, unless I use `use_labels` or manual labels. Instead of passing * to select(), pass the Table object you care about. The names of the columns should be the least important thing here - the expression language deals in Table and Column objects first and foremost. Maybe I'm missing something: http://paste.org/56806 when executing the output is (SQLAlchemy 0.7.7): 2012-11-08 12:49:29,274 INFO sqlalchemy.engine.base.Engine SELECT foo.id, foo.x, bar.a, bar.id FROM foo JOIN bar ON foo.id = bar.id 2012-11-08 12:49:29,280 INFO sqlalchemy.engine.base.Engine () (u'ID', 777, 0, u'ID') [u'id', u'x', u'a', u'id'] Traceback (most recent call last): File ambiguous.py, line 41, in module print r[0]['ID'] sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'ID' in result set! try 'use_labels' option on select statement. How can I avoid this exception? use_labels is not an option, for me, and manually settings labels in order to avoid ambiguos columns is a bit of nuisance (I'm actually doing this). Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAlCbnmQACgkQscQJ24LbaUQcWACfYezfKV3NXDnCFO4VXVBCXvwn Tb4AmwdLa/0XtHxaSWDzl/YamInjKOHF =mLiB -END PGP SIGNATURE- -- 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] use USING ( join_column [, ...] ) instead of ON join_condition
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
When selecting against joined tables, SQLAlchemy always use the
ON join_condition form.
The problem with this is that when the joined columns have the same
name, they are included 2 times in the result set.
Example (PostgreSQL):
CREATE TABLE foo (
id TEXT PRIMARY KEY,
x INTEGER
);
CREATE TABLE bar (
a INTEGER PRIMARY KEY,
id TEXT REFERENCES (foo.id) NOT NULL
);
INSERT INTO foo VALUES ('ID', 777);
INSERT INTO bar VALUES (0, 'ID');
SELECT * FROM foo JOIN bar ON foo.id = bar.id;
id | x | a | id
- +-+---+
ID | 777 | 0 | ID
If we use USING ( join_column [, ...] ), instead, only one of each
pair of equivalent columns will be included in the join output, not both.
SELECT * FROM foo JOIN bar USING (id);
id | x | a
- +-+---
ID | 777 | 0
Is it possible to change SQLAlchemy to use the second form when the
joined columns have the same name?
In alternative, this can be requested explicitly, as example:
class Join(FromClause):
__visit_name__ = 'join'
def __init__(self, left, right, onclause=None, using=None,
isouter=False):
...
When specified ``using`' take precedence over `onclause`.
Thanks Manlio Perillo
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAlCa7CwACgkQscQJ24LbaUQeyQCeIqMJyemQWfe+OKoMU0wV7Z+y
+0oAn3dVqCIA9QeEtysbBHMTMDp4CpS2
=ey+X
-END PGP SIGNATURE-
--
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] best practice to create an engine in a test suite
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
What is the best practice to create an engine in a test suite?
In one of my projects I was using sqlalchemy.testing module and the sqla
nose plugin, but these are no more available in the latest versions.
I have checked the alembic source code, and it have some private code
for parsing test.cfg configuration file and searching engine
configuration given an alias.
However I would like to avoid having to write such a code for every
project that use SQLAlchemy.
My current solution is to have this code in the test/__init__.py module:
## begin __init__.py
import ConfigParser
from sqlalchemy import create_engine
__all__ = ['create_testing_engine']
config = ConfigParser.ConfigParser()
config.read(['test.cfg'])
def create_testing_engine(alias=None):
if alias is not None:
url = config.get('db', alias)
else:
# Return the first engine defined
config_data = config.items('db')
if not config_data:
# Fall back to sqlite in memory database
url = 'sqlite://'
else:
url = config_data[0][1]
return create_engine(url)
## end __init__.py
Thanks Manlio Perillo
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk+IT48ACgkQscQJ24LbaUQYsQCfe9MSsb+/1yk+c+3HzdQUcxZz
xh0An2FU6CCQ3TJ8OsjN7GLGecRygnTq
=Gzwa
-END PGP SIGNATURE-
--
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 about new Engine transaction's context manager
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 12/04/2012 20:45, Michael Bayer ha scritto: [...] I have another question, about coding style. Current SQLAlchemy code create the connection and transaction objects in the Engine.begin method, and then return an instance of Engine._trans_ctx class. What about, instead, creating the connection and transaction objects inside the __enter__ method of the Engine._trans_ctx class? Doing this way, one can prevent incorrect usage of the context manager; that is doing so: ctx = db.begin() should not create a connection that will never be explicitly closed. oh that's a nice idea, if you create a pull request from https://bitbucket.org/sqlalchemy/sqlalchemy I can pull that in, thanks ! you'll need to adjust my new test test.engine.test_execute:ConvenienceExecuteTest.test_transaction_engine_ctx_begin_fails to use testing.run_as_contextmanager in order to hit the exception in that case. Sure, just give me some more time. The test suite should probably be renamed in something like test_transaction_engine_ctx_enter_fails (since it is no more the begin method that is failing); not sure. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk+IUnEACgkQscQJ24LbaUT8aACePkDebBAOhVbhLJuAtiYg/SyS tgoAnjR6Cz/cm7mNq29DOC5ytbD9+F8w =Tq/Y -END PGP SIGNATURE- -- 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 practice to create an engine in a test suite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 13/04/2012 18:59, Michael Bayer ha scritto: On Apr 13, 2012, at 12:08 PM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. What is the best practice to create an engine in a test suite? In one of my projects I was using sqlalchemy.testing module and the sqla nose plugin, but these are no more available in the latest versions. I have checked the alembic source code, and it have some private code for parsing test.cfg configuration file and searching engine configuration given an alias. However I would like to avoid having to write such a code for every project that use SQLAlchemy. A testing harness is outside the scope of SQLAlchemy itself. There might be some on Pypi, though I'd be skeptical that they'd solve this problem either; creation of engines is highly dependent on frameworks and other patterns in use and are very dependent on each application individually. you'd need to have your own pattern that you use. Well, I thought that using database aliases was a simple yet flexible solution. But looking at SQLAlchemy test suite the word simple does not apply any more ;-). Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk+IZH4ACgkQscQJ24LbaURVdgCeKdsiwE03tcaa+RtU1WzG+WHS LWAAnA5mQ/ThCe3cY7ROxK22RGc7deiU =sfVx -END PGP SIGNATURE- -- 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 about new Engine transaction's context manager
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I looked at SQLAlchemy source code to see examples about `with` statement support and found something that look suspicious to me. class Engine: def begin(self, close_with_result=False) conn = self.contextual_connect( close_with_result=close_with_result) trans = conn.begin() return Engine._trans_ctx(conn, trans, close_with_result) What happens in case conn.begin() fails? I was assuming that conn.begin always executed a BEGIN SQL command, but this is not the case. However some dialects (like informix and MySQL oursql) *do* execute SQL commands, and these can fail. Should I fill a bug report? I have another question, about coding style. Current SQLAlchemy code create the connection and transaction objects in the Engine.begin method, and then return an instance of Engine._trans_ctx class. What about, instead, creating the connection and transaction objects inside the __enter__ method of the Engine._trans_ctx class? Doing this way, one can prevent incorrect usage of the context manager; that is doing so: ctx = db.begin() should not create a connection that will never be explicitly closed. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk+HFywACgkQscQJ24LbaUTE7QCfbxJTk2vujsqik4UNZ4oIPooR 9NgAnArmvpg9qEeJxsGAlseFVykeuJzH =N3JD -END PGP SIGNATURE- -- 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] Postgresql COPY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 15/04/2011 09:17, Yang Zhang ha scritto: Is it possible to execute a Postegresql COPY ... FROM STDIN statement via sqlalchemy, or do we have to drop down to psycopg2 for that (http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from)? Tried executing a COPY statement followed directly by the input values, as in psql, but that didn't work. Thanks in advance. The problem is that psycopg2, if I'm not wrong, only supports an high level interface to COPY command. With plain libpq, you can issue a normal query string with the COPY command, and then use PQputCopyData and PQputCopyEnd functions to send data. psycopg2, instead, implements custom methods for copy support (I assume this is done to avoid having to handle/expose the additional PGRES_COPY_OUT or PGRES_COPY_IN status code, and an additional PGRES_COMMAND_OK after PQputCopyEnd). Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2odH0ACgkQscQJ24LbaURe/gCgmptRuAv1usFDYIzChjUIDt/1 bdwAnjLUij/CFScNEv1zV/K/Y//v9Akf =Sxsi -END PGP SIGNATURE- -- 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] reusing the SQLAlchemy nose plugin
Hi. In a project using SQLAlchemy I decided to use the SQL nose plugin, in order to avoid reinventing the wheel. Unfortunately it seems the plugin is a bit too invasive and it will only work with SQLAlchemy test suite. The problem is with the wantClass method, that pratically will ignore all normal test cases. Fortunately, in my case I use an additional nose plugin: http://bitbucket.org/mperillo/nose-tree and increasing its score solved the problem. Is is possible to modify the SQLAlchemy nose plugin in order to be more cooperative? As an example by adding an option that will change the wantClass method from if not issubclass(cls, testing.TestBase): return False else: if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) to something like (not tested): if self.sa_only and not issubclass(cls, testing.TestBase): return False if issubclass(cls, testing.TestBase): if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) # use nose default Thanks Manlio -- 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] reusing the SQLAlchemy nose plugin
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] Is is possible to modify the SQLAlchemy nose plugin in order to be more cooperative? As an example by adding an option that will change the wantClass method from if not issubclass(cls, testing.TestBase): return False else: if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) to something like (not tested): if self.sa_only and not issubclass(cls, testing.TestBase): return False if issubclass(cls, testing.TestBase): if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) # use nose default whats sa_only and how does that get set ? It is a boolean value, and it is set from a nose config option, defined in the SQLAlchemy plugin. it seems like the score attribute is already provided by Nose for the purpose of setting plugin priority ? (since you are already setting attributes on plugins, it seems) The problem is that if I don't use my plugin (with a score higher than the one of the SQLAlchemy plugin), the wantMethod from SQLAlchemy plugin will be used, thus ignoring all normal tests cases (that is, test cases not derived from sqlalchemy.testing.TestBase). Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkv2lWEACgkQscQJ24LbaUTzxwCeOcCmfNOAhGgLNjlaM6YLvDxc sp0AmQFC0OXr/KLeHlJpg7hwZ9MieOEk =/PGv -END PGP SIGNATURE- -- 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] reusing the SQLAlchemy nose plugin
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] Is is possible to modify the SQLAlchemy nose plugin in order to be more cooperative? As an example by adding an option that will change the wantClass method from if not issubclass(cls, testing.TestBase): return False else: if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) to something like (not tested): if self.sa_only and not issubclass(cls, testing.TestBase): return False if issubclass(cls, testing.TestBase): if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) # use nose default A precisation. The reason I used the sa_only flag is because I assumed there was a reason why test cases not derived from testing.TestBase must be ignored. If possible, the wantMethod should be something like: if not issubclass(cls, testing.TestBase): # Skip return None else: if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) Another problem I found when using SQLAlchemy nose plugin is that warnings are configured to raise exceptions, and this is a problem for me. By the way: what is the reason why testing.Test does not derive from unittest.TestCase? I would like to use __unsupported_on__, but with current implementation it is a pain (and there is also the problem with how nose plugins work, since SQLAlchemy wantClass method will be ignored in case another plugin with an higher score is available). Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkv2n4AACgkQscQJ24LbaURU4gCfeVQRuEOTHpQb9hzCoA0aEfB/ 6RsAoJn9L51KObvWva7WOQIY21dpYDCf =ZI6r -END PGP SIGNATURE- -- 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: Alembic
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] To clarify; Alembic supports branching, Migrate doesn't? (and where can I find out exactly what is meant by branching?) it means if you have a branch of the repo, so say you start with source A with versions: 1 2 3 then you branch into B. A continues to get more versions: 4 5 6 B also gets more versions: 4 5 6 7 so now you want to merge B back to A. because migrate is counting with integers, you're out of luck. You have to renumber your files somehow so that they all work out. What I'm doing, you still might have an uphill battle if A and B have non-compatible migrations, but we're using randomly generated hex ids: a65fe 47aed 277eef What about using revisions strings, as handled by setuptools? You have version 0.1 and 0.2, and branches 0.1.1, 0.1.1, and 0.2.1, 0.2.2. So, versions have a well defined ordering. However I'm still not sure what a merge should do. [...] Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkv20zYACgkQscQJ24LbaUTJ+QCgj9HjEjacyjAYflPu74DaSZrN Cg0An0dwxCMpSZucpayt4zMvnyN44bTv =vIGO -END PGP SIGNATURE- -- 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] reusing the SQLAlchemy nose plugin
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] A precisation. The reason I used the sa_only flag is because I assumed there was a reason why test cases not derived from testing.TestBase must be ignored. If possible, the wantMethod should be something like: if not issubclass(cls, testing.TestBase): # Skip return None else: if (hasattr(cls, '__whitelist__') and testing.db.name in cls.__whitelist__): return True else: return not self.__should_skip_for(cls) Another problem I found when using SQLAlchemy nose plugin is that warnings are configured to raise exceptions, and this is a problem for me. OK my thought here is that yeah, this is likely a problem for you, Right now, fortunately, I have solved all the problems. And the only thing I use from SQLA plugin is testing.db, so that I don't have to hard write the database to use. I would like to use support for skipping test for specified dialects, but it is not a big issue (and given the way nose plugins work, I can not use the SQLA plugin implementation). [...] By the way: what is the reason why testing.Test does not derive from unittest.TestCase? nose doesn't require unittest. Ideally we would be able to run tests that don't derive from any class at all. I have a bad experience with this, using nose. In my code I use functions like setup_testing_defaults, and by default nose try to execute it as a test. The first time I used nose, I got several failure and I took some time to understand what was going on. I had to write a custom plugin, to change this. Another advantace of using UnitTest classes, is that test execution can be pretty printed in a tree (I liked the output produced by twisted.trial, and I have tried to reproduce it for nose). [...] Since nose has taken a deep dive into the its author can't support it anymore category, I was not aware of nose state. I'm waiting for the next de-facto test library (nose2 i think it was called ?) If I remember correctly there is an unittest2 project for the standard library. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkv21a8ACgkQscQJ24LbaURjuQCeO1GG8kXxUwOODIEtkZxe+AW3 ZtMAn14kktUZOpa5ar0tkZbXwAjM13I0 =Sny0 -END PGP SIGNATURE- -- 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] SQLAlchemy 0.6beta2 released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: SQLAlchemy 0.6beta2 is now available.This beta may be the last before the 0.6.0 final release. We've hopefully gotten every largish change into the release as possible so that people can test. 0.6 is already running on a number of production servers and is already widely tested on mainstream platforms. Big new things in this release include: [...] Do you plan to implement ticket #877 for the 0.6.0 final release? What about ticket #1679, for SQL Schema support? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkumMJMACgkQscQJ24LbaUTitgCeJXSTpWdcWZDwishGvIScFkm7 C2kAn2OVRHyglzTzad7NI4tExoQ4R7p5 =tRls -END PGP SIGNATURE- -- 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 in_ against multiple columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 richard reitmeyer ha scritto: Hello, all. I'm trying to use compound fields with in_ to achieve something like this: update foo set svrid = NULL, asof=NULL where (svrid,asof) in (select svrid, asof from foo except select svrid, asof from bar); If you are using PostgreSQL, you can use the ROW constructor. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkufw6wACgkQscQJ24LbaUTiuQCfUhN26bZA7FTzWZK8wWYl7L73 q8MAn0vu60420NU9DwPlGQCdl2EuSNAA =fEMJ -END PGP SIGNATURE- -- 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] SQLAlchemy 0.6beta1: PG dialect: DropTable: (ProgrammingError) syntax error at or near CONSTRAINTS
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Gerry Reno ha scritto:
Been doing great with SQLAlchemy 0.6beta1 for the past week after a
couple snags migrating from 5.5.
Today I hit a small problem with postgresql on DropTables:
running this command:
engine.execute(DropTable(table, cascade=True))
produced this error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
near CONSTRAINTS
LINE 2: DROP TABLE users CASCADE CONSTRAINTS
^
'\nDROP TABLE users CASCADE CONSTRAINTS' {}
The problem seems to be with the keyword 'CONSTRAINTS' for the
postgresql dialect. The drop succeeds without it.
I have reported the same problem a few days ago.
Support for cascading has been removed very recently, since it used
Oracle only syntax.
[...]
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuYt9kACgkQscQJ24LbaUTTFACgiYx4yWm7mT3ulZvYdt9wWLgf
gQEAn3IwidE3nba7o4hc2jW7iGoTbiPU
=Yne8
-END PGP SIGNATURE-
--
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] update_or_insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I have the following use case: a table that contains the layout info of some elements in a web page. The layout is fixed, that is, the number and the positions of the elements that can be displayed is fixed. This means that in order to change the layout I will have to update this table. But for the very first elements, I need to insert the first values. I wrote this function: http://paste.pocoo.org/show/18/ It first executes the supplied UPDATE statement, and if no rows are returned, it try to convert it to on INSERT statement and execute it. A better solution is to use the SQL:2003 MERGE statement, but it is not supported by PostgreSQL. The function I wrote is very limited, but I would like to know if the implementation is at least correct. I would also like to know if there is a better (and portable at least on PostgreSQL) pattern. MySQL has the REPLACE statement, but the logic is different from the one I need; it first try to INSERT and then to UPDATE. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuTzP0ACgkQscQJ24LbaUR/CACfQNLWLv9rMN5udPY+POC1FeyC cuwAnRqUKt/uCfbWroEsGYoOsVzEwK3D =YF0U -END PGP SIGNATURE- -- 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: [PROPOSE] Integrate migration support in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lele Gaifax ha scritto: Manlio Perillo manlio.peri...@gmail.com writes: Michael Bayer ha scritto: Similarly, the concept of a version as an integer number is not really flexible enough - The idea was to keep it simple. IMHE, there's no such a beast! I would like to investigate the creation of migration scripts between branches as well. The version can then be a string, but it needs to sortable (see setuptool as an example). The mechanism of schema versioning is not at all something that belongs in SQLAlchemy core. I'm all with Michael here. Sure, for *very* simple projects, it'd be nice to have a mechanism that spits out the needed statements, but at least in my experience (going from very little to moderately big and complex schemas), maintaining an upgradable path is something better done at another level. Some steps needs an hand-crafted solution, that maybe involve dropping/recreating dependencies, temporary data-space to upgrade existing contents and so on. I think you have misunderstod my propose. The propose was not to to spits out the needed statements, but to call user defined functions for a migration path from version x to version y. That's why it should be kept simple: different users need different features, and this is up to high level applications that are built on top of the base support to migration. You can have very simple upgrade functions that use SQLAlchemy builtin DDL, or you can have complex scripts with hand written SQL. I'm interested in a reusable core. The more is simple, the more is reusable (unless it is too simple, and thus useless) [...] Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuQ/UMACgkQscQJ24LbaUSxxwCePPm9N6e+oZAqf/Zwo/kt5f9M cOsAn2UzpxfSmg3W+lOWuWADds60D/Xs =B6HL -END PGP SIGNATURE- -- 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] [PROPOSE] Integrate migration support in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: I'm pretty much -1 on this since I think migrations need to have absolute flexibility - the listener system within create_all() is not at all designed to be used to that degree. Another solution is to add a completely new interface. The engine will have new `upgrade` and `downgrade` methods, and SQLAlchemy will have a new engine.migrate module, with custom Visitors for migration events. You can migrate a MetaData or single Tables. For starters, its an entirely in-memory system - if you have hundreds of migration scripts its quite cumbersome to read them all into memory for a single migration. The idea was to use functions, and not scripts. I would add the migration functions in the same module where the metadata is defined. I don't see problems with this. Only a single function is called at one time. Similarly, the concept of a version as an integer number is not really flexible enough - The idea was to keep it simple. I would like to investigate the creation of migration scripts between branches as well. The version can then be a string, but it needs to sortable (see setuptool as an example). The mechanism of schema versioning is not at all something that belongs in SQLAlchemy core. Yes. But it is a simple metadata. It can also be stored in the `info` dictionary (and this is what I plan to do if I have to write the support by myself). I would like to implement an alternate version of Migrate at some point as an example or recipe - the ALTER constructs themselves could eventually live within SQLAlchemy.schema, since those are non-controversial atomic units of functionality. +1 The versioning would be a simple script system that starts with the integer version but could later work off of md5 hashes perhaps - but this would just be a usage recipe, that like the PostGIS example would launch into a real product. the main point would be that the scripting system would be cleanly separated from the system of interaction with the database. The existing Migrate project is burdened by three things - the monkeypatching legacy of older SQLA versions, an overly rigid and complex system of creating engines that makes it almost impossible to control the transactional scope of a migration, and its superfluous features, namely the whole schema comparison system that I could do without. Another problem, for me, is that migration scripts apply to the whole database (unless I'm missing something - I have only read the documentation and some of the code). However in my projects I have several metadata objects. Some of these metadata objects are defined in external packages. This is the reason why I think that migration should be implemented as normal callback functions. And it should possible to register several event listeners on the same event, so that an application can add its own behaviour when a schema in an external package is upgraded/downgraded. [...] Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuP854ACgkQscQJ24LbaUTmiACePF2aKGBYbWgVLle5B5aHuqdV HNkAoJe2czsAXtTF1AFAvyUvMs/qJ+2z =bNMq -END PGP SIGNATURE- -- 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] extended use of VALUES command
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. Is it possible, in SQLAlchemy, to express this query? CREATE TEMP TABLE foo ( x INTEGER, y INTEGER ); INSERT INTO foo VALUES (10, 11); INSERT INTO foo VALUES (1, 2); manlio= SELECT * FROM foo WHERE (x, y) in (VALUES (1, 2), (3, 4)); x | y - ---+--- 1 | 2 (1 riga) This should be standard SQL. It is supported by PostgreSQL [1], but SQLite fails with a syntax error. [1] http://www.postgresql.org/docs/8.4/static/sql-values.html Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuNNh0ACgkQscQJ24LbaUTuowCffJyiWt4/Hi9adT0Vrk4K5/6A B8EAnA2JOmsHBQVnPiEHsrFZIW19iLx6 =ijKV -END PGP SIGNATURE- -- 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] failures in the test suite for match operator in PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 28, 2010, at 8:01 PM, Manlio Perillo wrote: Rami Chowdhury ha scritto: On Feb 28, 2010, at 15:17 , Manlio Perillo wrote: Rami Chowdhury ha scritto: On Sunday 28 February 2010 13:32:40 Manlio Perillo wrote: I have found that some tests fails: http://paste.pocoo.org/show/184048/ Are you using the latest SVN? I'm using the mercurial mirror. Changeset 4937:629e75b74dd3 I'm also getting failures for the profile tests. Profile tests? http://paste.pocoo.org/show/184116/ I wouldnt worry too deeply about those, did you run with logging on or something ? No. The tests that are failing are: test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_profile_3_properties Function call count 7193 not within 5.0% of expected 6783. and test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_profile_4_expressions Function call count 24122 not within 5.0% of expected 22510. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuLt2sACgkQscQJ24LbaUT3FQCglgYnHbOWPr0U263RHaUJMYRs 6y4An16YOPr7s24tqwNoImTwjH24Zqv/ =n7aI -END PGP SIGNATURE- -- 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] some question about SQLAlchemu development
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
On Feb 28, 2010, at 8:12 PM, Manlio Perillo wrote:
Michael Bayer ha scritto:
[...]
* I have noted that some database objects not directly attached to a
metadata, like a Sequence, can leave the database in an inconsistent
state if the associated test suite fails.
I have the same problem with the Schema object.
If, for some reason, in the test suite there is an error before the
schema is dropped, the successive run of the test suite will fail,
since the test will try to create an already existing schema.
Is there a good solution?
the tests support a --dropfirst option that will remove any errant Table
objects. I haven't observed errant sequences to be an issue except
perhaps on Oracle, but this system could be enhanced to drop all the
sequences too.
for testing a CREATE SCHEMA construct I would just check the SQL string
and not actually execute anything. unit tests should not create any
schemas.see the existing schema-listing tests that just use the three
which are part of the documented testing environment.
I can't, since I have also to check the `has_schema` method in the Dialect.
call has_schema() on test_schema, which will exist in a full test
environment. then call it on something like sa_fake_schema_123 to test
the not found condition.
Ok, done.
I'm not sure, however, if `test_schema` method test should go in
`test/engine/test_reflection.py`, since it is not directly related to
reflection.
With the old patch
(http://www.sqlalchemy.org/trac/attachment/ticket/1679/schema.patch)
the code was in the HasSchemaTest class, so it was ok (the same is done
for HasSequenceTest).
P.S.: I have removed the supports_schema flag.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuLuxsACgkQscQJ24LbaURcuQCfRwMZWyW+zPEacm9g/BVURSqq
R50AnRY7AJx/OjQhhz/xUFZaEIKLgNe2
=/PF3
-END PGP SIGNATURE-
--
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.
diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -104,6 +104,7 @@
PrimaryKeyConstraint,
Sequence,
Table,
+Schema,
ThreadLocalMetaData,
UniqueConstraint,
)
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -650,6 +650,19 @@
def _get_default_schema_name(self, connection):
return connection.scalar(select current_schema())
+def has_schema(self, connection, schema):
+cursor = connection.execute(
+sql.text(
+select nspname from pg_namespace where lower(nspname)=:schema,
+bindparams=[
+sql.bindparam(
+'schema', unicode(schema.lower()),
+type_=sqltypes.Unicode)]
+)
+)
+
+return bool(cursor.first())
+
def has_table(self, connection, table_name, schema=None):
# seems like case gets folded in pg_class...
if schema is None:
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -80,6 +80,45 @@
else:
return schema + . + name
+class Schema(SchemaItem):
+Represent a schema in a database.
+
+e.g.::
+
+myschema = Schema(myschema)
+
+Constructor arguments are as follows:
+
+:param name: The name of this schema as represented in the database.
+
+Names which contain no upper case characters
+will be treated as case insensitive names, and will not be quoted
+unless they are a reserved word. Names with any number of upper
+case characters will be quoted and sent exactly. Note that this
+behavior applies even for databases which standardize upper
+case names as case insensitive such as Oracle.
+
+:param info: A dictionary which defaults to ``{}``. A space to store application
+specific data. This must be a dictionary.
+
+:param quote: Force quoting of this schema's name on or off, corresponding
+to ``True`` or ``False``. When left at its default of ``None``,
+the schema identifier will be quoted according to whether the name is
+case sensitive (identifiers with at least one upper case character are
+treated as case sensitive), or if it's a reserved word. This flag
+is only needed to force
[sqlalchemy] copyright on submitted patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. What is the copyright policy on patches submitted on the SQLAlchemy bug tracker? I'm glad to release them under the MIT license (it is the licence I use for my own projects) but the problem is that the patch author des not appear anywhere. It seems that is not even possible to create a personal account on Trac. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuL4soACgkQscQJ24LbaURSZwCdGFXHvtY/zWtLsl7xbcqqLvNu 5RoAnjYR/Di/UM2Hs+zmSNje/E38sa9j =JQn6 -END PGP SIGNATURE- -- 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] some question about SQLAlchemu development
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] I'm not sure, however, if `test_schema` method test should go in `test/engine/test_reflection.py`, since it is not directly related to reflection. With the old patch (http://www.sqlalchemy.org/trac/attachment/ticket/1679/schema.patch) the code was in the HasSchemaTest class, so it was ok (the same is done for HasSequenceTest). P.S.: I have removed the supports_schema flag. can you attach the latest that you have to the ticket. Done. Also I thought perhaps we should make sure has_schema(), has_table(), has_sequence() are present on the Inspector interface since that's where we want the public API to be for this stuff.Also get_sequence_names() would be nice too. I pass. I'm not sure how things should work. As an example, we have: * Dialect.table_names * Dialect.get_table_names * Inspector.get_table_names The Inspector method checks if the Dialect instance has a `get_table_names` method defined, but it seems, to be, that this method is *always* defined, and raises NotImplementedError if not supported by the dialect. `Dialect.table_names` is just a wrapper around `Dialect.get_table_names` that make sure the schema and the connection are properly set. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuL72UACgkQscQJ24LbaURdmQCcC3pCe+X7wvBZEAoO5FYpvBGO 1rQAnijVQ9MNxK7PJYqQkKXb9IHWTTVe =Zoxs -END PGP SIGNATURE- -- 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] some question about SQLAlchemu development
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] I'm not sure, however, if `test_schema` method test should go in `test/engine/test_reflection.py`, since it is not directly related to reflection. With the old patch (http://www.sqlalchemy.org/trac/attachment/ticket/1679/schema.patch) the code was in the HasSchemaTest class, so it was ok (the same is done for HasSequenceTest). P.S.: I have removed the supports_schema flag. can you attach the latest that you have to the ticket. Done. Also I thought perhaps we should make sure has_schema(), has_table(), has_sequence() are present on the Inspector interface since that's where we want the public API to be for this stuff.Also get_sequence_names() would be nice too. How are things supposed to work? As an example, we have: * Dialect.get_table_names * Engine.table_names * Inspector.get_table_names The Inspector method checks if the Dialect instance has a `get_table_names` method defined, but it seems, to be, that this method is *always* defined, and raises NotImplementedError if not supported by the dialect; is this a bug or I'm missing something? `Engine.table_names` is just a wrapper around `Dialect.get_table_names` that make sure the schema and the connection are properly set. So we have a public interface implemented in the Engine and another one implemented in the Inspector. As far as I can understand, `has_schema`, `has_table` and so on, are only used by the engine, during the visitor phase, to avoid to create a table or sequence if this already exists (and the metadata object has checkfirst set to True). There is no need for them in the Inspector. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuL72UACgkQscQJ24LbaURdmQCcC3pCe+X7wvBZEAoO5FYpvBGO 1rQAnijVQ9MNxK7PJYqQkKXb9IHWTTVe =Zoxs -END PGP SIGNATURE- -- 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] copyright on submitted patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: Hi. What is the copyright policy on patches submitted on the SQLAlchemy bug tracker? I'm glad to release them under the MIT license (it is the licence I use for my own projects) but the problem is that the patch author des not appear anywhere. It seems that is not even possible to create a personal account on Trac. The LICENSE file expresses that copyright is held by Michael Bayer and contributors, so you'd be a contributor. While its not a big deal for us to give you a trac account, I've never heard of that being a requirement for copyright assignment. Of course not :). It's just a question to track original authors of each patch. And I don't think I can qualify as contributor just for a very few lines of codes. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuL+hAACgkQscQJ24LbaUSNeACdGaI76kK5+/1XzlLd8EXbOKdM byIAmgKfFh7DgwCEP+Klp0q2ROsCXtLk =dTDu -END PGP SIGNATURE- -- 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] failures in the test suite for match operator in PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 28, 2010, at 4:32 PM, Manlio Perillo wrote: By the way, I have also noted that in the SQLAlchemy source code there are many extra whitespaces (lines with only whitespaces, or lines ending with whitespaces), that are usually (?) considered bad programming practice. lines ending with extra whitespace are editor bugs. as far as blank lines between defs and such, The problem is that there is extra whitespace in these blank lines. The fact is, I'm using a Mercurial hook taken from: http://hgbook.red-bean.com/read/handling-repository-events-with-hooks.html#id403945 so I'm quick to note trailing whitespaces. Note, also, that the trailing whitespaces are reported by the pep8 [1] program. [1] http://pypi.python.org/pypi/pep8/ Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuL/EMACgkQscQJ24LbaURrdgCfSNIcZD7bPomsJRWydiCJG0Ov Yw8AnAm9glA5ydQNA+i3FilUsRlMxz7/ =mtqO -END PGP SIGNATURE- -- 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] suspicious code in PostgreSQL ENUM handling
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I find this code (sqlalchemy.dialects.postgresql.base) suspicious: class ENUM(sqltypes.Enum): def create(self, bind=None, checkfirst=True): if not checkfirst or not bind.dialect.has_type(bind, self.name, schema=self.schema): bind.execute(CreateEnumType(self)) def drop(self, bind=None, checkfirst=True): if not checkfirst or bind.dialect.has_type(bind, self.name, schema=self.schema): bind.execute(DropEnumType(self)) The code does not handle the case when bind is None. However, looking at sqlalchemy.types.SchemaType, bind is never None. In the SchemaType docstring there is a typo: complimented should be complemented. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuMB3EACgkQscQJ24LbaUSSJwCgh/WpOlqHqz9Cex7ZxFcIH3ZZ B+AAn1aCQEYtsA/ctE90uDWHGtlg42Fy =vQq7 -END PGP SIGNATURE- -- 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] ticket #877
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I would like to write a patch for ticket #877. What is the best method to implement the requested feature? The ExecutionContext class has a post_exec method, so the implementation for the psycopg2 driver can override this method, check for the notice messages and log them. Attached is a very simple patch. The problem is that NOTICE messages will only be logged if echo parameter is set to True, in the create_engine function; however I usually don't want to echo all SQL statatements, but I'm interested to see NOTICE messages. By the way, it seems there is a bug in the documentation: http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine echo=False – if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. The echo attribute of Engine can be modified at any time to turn logging on and off. If set to the string debug, result rows will be printed to the standard output as well. This flag ultimately controls a Python logger; see dbengine_logging at the end of this chapter for information on how to configure logging directly. However there is no dbengine_logging section! Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuKmCEACgkQscQJ24LbaURdJgCeIPIbeYfVK+9B0cQK0Bv2YhBt epQAnj44OYf66Kug6zZvRQd5SmzxE1Vv =Rlnm -END PGP SIGNATURE- -- 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. diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -135,6 +135,10 @@ else: return base.ResultProxy(self) +def post_exec(self): +for notice in self._connection.connection.notices: +self.engine.logger.info(notice) + class PostgreSQL_psycopg2Compiler(PGCompiler): def visit_mod(self, binary, **kw):
Re: [sqlalchemy] ticket #877
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
On Feb 28, 2010, at 11:21 AM, Manlio Perillo wrote:
I would like to write a patch for ticket #877.
What is the best method to implement the requested feature?
The ExecutionContext class has a post_exec method, so the implementation
for the psycopg2 driver can override this method, check for the notice
messages and log them.
Attached is a very simple patch.
The problem is that NOTICE messages will only be logged if echo
parameter is set to True, in the create_engine function; however I
usually don't want to echo all SQL statatements, but I'm interested to
see NOTICE messages.
1. dont use the engine's logger, use one specific to the psycopg2 dialect
2. if the logger has info enabled, then you can dump the notices. otherwise
don't waste CPU digging through them
put it under the namespace
logging.getLogger('sqlalchemy.dialects.postgresql') .
Done, new patch attached.
The is also a bug fix: I forgot to clear the notices list.
The notices list is cleared *only* if messages are actually logged.
I took the liberty to change the coding style, too.
If patch is ok, I will upload it on the bug tracker.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuKqP8ACgkQscQJ24LbaUT9ZgCfdGG+vREnuoJf0e0KmjQaKAGH
bkcAoIorPN24ZeDfvd6g7zym+jYGA1l3
=LuLD
-END PGP SIGNATURE-
--
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.
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -46,8 +46,10 @@
-import random, re
+import random
+import re
import decimal
+import logging
from sqlalchemy import util
from sqlalchemy import processors
@@ -59,6 +61,10 @@
PGIdentifierPreparer, PGExecutionContext, \
ENUM, ARRAY
+
+logger = logging.getLogger('sqlalchemy.dialects.postgresql')
+
+
class _PGNumeric(sqltypes.Numeric):
def bind_processor(self, dialect):
return None
@@ -135,6 +141,14 @@
else:
return base.ResultProxy(self)
+def post_exec(self):
+if logger.isEnabledFor(logging.INFO):
+for notice in self._connection.connection.notices:
+# NOTICE messages have a newline character at the end
+logger.error(notice.rstrip())
+
+self._connection.connection.notices[:] = []
+
class PostgreSQL_psycopg2Compiler(PGCompiler):
def visit_mod(self, binary, **kw):
Re: [sqlalchemy] ticket #877
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
On Feb 28, 2010, at 11:21 AM, Manlio Perillo wrote:
I would like to write a patch for ticket #877.
Sorry, I forgot to update the log level used in the patch (I was doing
some tests with the engine `echo` parameter).
Attached is the correct patch.
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuKqbMACgkQscQJ24LbaUQd+wCeOlUulbQoP9KO/EJrd9ZgLUCa
irsAnipmXxMXQwnaWe0bIcZDbCuF5Ml6
=MNXe
-END PGP SIGNATURE-
--
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.
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -46,8 +46,10 @@
-import random, re
+import random
+import re
import decimal
+import logging
from sqlalchemy import util
from sqlalchemy import processors
@@ -59,6 +61,10 @@
PGIdentifierPreparer, PGExecutionContext, \
ENUM, ARRAY
+
+logger = logging.getLogger('sqlalchemy.dialects.postgresql')
+
+
class _PGNumeric(sqltypes.Numeric):
def bind_processor(self, dialect):
return None
@@ -135,6 +141,14 @@
else:
return base.ResultProxy(self)
+def post_exec(self):
+if logger.isEnabledFor(logging.INFO):
+for notice in self._connection.connection.notices:
+# NOTICE messages have a newline character at the end
+logger.info(notice.rstrip())
+
+self._connection.connection.notices[:] = []
+
class PostgreSQL_psycopg2Compiler(PGCompiler):
def visit_mod(self, binary, **kw):
[sqlalchemy] bug in DROP TABLE CASCADE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
When reading the code in the `sql.compiler` module, I noted this:
def visit_drop_table(self, drop):
ret = \nDROP TABLE + self.preparer.format_table(drop.element)
if drop.cascade:
ret += CASCADE CONSTRAINTS
return ret
The problem is that this syntax is not supported by all backends!
I have checked the SQL 2003 standard (a draft), and it seems that
CASCADE CONSTRAINTS is *not* standard.
Example:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True)
)
engine = create_engine('sqlite://', echo=True)
engine.execute(schema.CreateTable(test))
engine.execute(schema.DropTable(test, cascade=True))
This fails on both PostgreSQL and SQLite.
My suggestion:
1) Remove CONSTRAINTS from the DROP statetemt in `sql.compiler`
2) override visit_drop_table method for dialects that don't support
CASCADE syntax (like sqlite)
3) override visit_drop_table for dialects with extended syntax
(Oracle and MSSQL?)
By the way: does Oracle support the SQL standard
DROP TABLE CASCADE ?
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuK0EgACgkQscQJ24LbaURzvQCgiYGEMKsiOr4lmXnYBtidq5vp
FL0AnAqsZCnsbidkJcKW1s8EGeWErsnu
=XNiZ
-END PGP SIGNATURE-
--
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] failures in the test suite for match operator in PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have found that some tests fails: http://paste.pocoo.org/show/184048/ The problem is with the match operarator for fulltext search. I'm using PostgreSQL 8.4. By the way, I have also noted that in the SQLAlchemy source code there are many extra whitespaces (lines with only whitespaces, or lines ending with whitespaces), that are usually (?) considered bad programming practice. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuK4PgACgkQscQJ24LbaUR9ZgCfc2r9Nm2vohSxuOKU6rrpkge4 hQwAn0b3PcY84ksY5sxzEKPxZz1rPENr =7klx -END PGP SIGNATURE- -- 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] some question about SQLAlchemu development
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I recently wrote my first big patch for SQLAlchemy: http://www.sqlalchemy.org/trac/attachment/ticket/1679/schema.patch and I have some questions about the source code. * Why objects like Sequence have a `__visit_name__ = 'sequence'`, and the `visit_sequence` method in the `Compiler` simply returns None? If there is nothing to do in the visitor, what is the reason to be visitable? * I have noted that some database objects not directly attached to a metadata, like a Sequence, can leave the database in an inconsistent state if the associated test suite fails. I have the same problem with the Schema object. If, for some reason, in the test suite there is an error before the schema is dropped, the successive run of the test suite will fail, since the test will try to create an already existing schema. Is there a good solution? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuK6JwACgkQscQJ24LbaURl9ACfV5tcexDnWcNee62MiQaMdkr6 kVcAoJDEtTt4D1DhNeaTVSA38HYDO5I1 =EmkU -END PGP SIGNATURE- -- 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] failures in the test suite for match operator in PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rami Chowdhury ha scritto: On Sunday 28 February 2010 13:32:40 Manlio Perillo wrote: I have found that some tests fails: http://paste.pocoo.org/show/184048/ Are you using the latest SVN? I'm using the mercurial mirror. Changeset 4937:629e75b74dd3 I'm also getting failures for the profile tests. I don't get any of those test failures PostgreSQL 8.4.2, rev 6878 Uhm, is the Mercurial mirror up to date? The revision numbers differ, but I checked the last log messages and they are the same. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuK+aEACgkQscQJ24LbaUR05ACeJl+jdMk+TcIagmz8uZKmoTkg fgQAnj0FZ8F23lGnSg6qC89wsW9DSLKU =EiKO -END PGP SIGNATURE- -- 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] ticket #877
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Done, new patch attached. The is also a bug fix: I forgot to clear the notices list. The notices list is cleared *only* if messages are actually logged. I took the liberty to change the coding style, too. If patch is ok, I will upload it on the bug tracker. looks good to me - have you run all the tests with that logging enabled ? Yes. I ran all the tests using: nosetests --db postgresql -v --log-info=sqlalchemy.dialects.postgresql and it works as expected. On the bug tracker I have attached a patch that also updates the documentation. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuK+3YACgkQscQJ24LbaUSCMACghdj22doappPrbCvsfq82r6d2 NpAAoJXEPrB4eu4Thv6hdHMad2M8f7M4 =LZRS -END PGP SIGNATURE- -- 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] incorrect behaviour with primary key constraint and integer columns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I think that SQLAlchemy behaves incorrectly when there is a primary key
constraint on multiple columns and one of the columns is of type Integer.
##
from sqlalchemy import schema, types, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.String, nullable=False),
schema.Column('y', types.Integer, nullable=False),
schema.PrimaryKeyConstraint('x', 'y')
)
engine = create_engine('postgresql://...', echo=True)
try:
metadata.create_all(engine)
finally:
metadata.drop_all(engine)
##
The generated TABLE statament is:
CREATE TABLE test (
x VARCHAR NOT NULL,
y SERIAL NOT NULL,
PRIMARY KEY (x, y)
)
In my opinion, autoincrement should be assumed to be true *only* when
there is one primary key of type Integer.
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuLA+4ACgkQscQJ24LbaUQpgQCeLMg6mdv1INCe0EgAxWipPeak
4lgAmwdZcQiVYv3JWL/quAVQb4WBwSef
=wgyS
-END PGP SIGNATURE-
--
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] failures in the test suite for match operator in PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rami Chowdhury ha scritto: On Feb 28, 2010, at 15:17 , Manlio Perillo wrote: Rami Chowdhury ha scritto: On Sunday 28 February 2010 13:32:40 Manlio Perillo wrote: I have found that some tests fails: http://paste.pocoo.org/show/184048/ Are you using the latest SVN? I'm using the mercurial mirror. Changeset 4937:629e75b74dd3 I'm also getting failures for the profile tests. Profile tests? http://paste.pocoo.org/show/184116/ I'm also having random failures. As an example, in the test I ran right now I got this error: http://paste.pocoo.org/show/184118/ See my previous post about these errors. I don't get any of those test failures PostgreSQL 8.4.2, rev 6878 Uhm, is the Mercurial mirror up to date? The revision numbers differ, but I checked the last log messages and they are the same. Hmmm, that's a good question. I don't know much about it but from what I have been told it does lag sometimes -- can you try running the tests in an SVN checkout? Sure. The result is the same. But I have found the problem: I have tsearch configured to use the italian locale but the test cases assume english! By the way, I have found that the README.unittests is missing an important information about PostgreSQL. The default value for `max_prepared_transactions` [1] is 0, but with this value some of the SQLAlchemy tests fail. The document should also inform the developer that `default_text_search_config` [2] parameter MUST be set to pg_catalog.english. [1] http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS [2] http://www.postgresql.org/docs/8.4/interactive/runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuLEcsACgkQscQJ24LbaUQmKQCeNmBwrdTFiOZgatGPi2h1gJwK T7gAnjMhEFtUWG051jjaIySr47aXeeMu =bext -END PGP SIGNATURE- -- 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] some question about SQLAlchemu development
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] * I have noted that some database objects not directly attached to a metadata, like a Sequence, can leave the database in an inconsistent state if the associated test suite fails. I have the same problem with the Schema object. If, for some reason, in the test suite there is an error before the schema is dropped, the successive run of the test suite will fail, since the test will try to create an already existing schema. Is there a good solution? the tests support a --dropfirst option that will remove any errant Table objects. I haven't observed errant sequences to be an issue except perhaps on Oracle, but this system could be enhanced to drop all the sequences too. for testing a CREATE SCHEMA construct I would just check the SQL string and not actually execute anything. unit tests should not create any schemas. see the existing schema-listing tests that just use the three which are part of the documented testing environment. I can't, since I have also to check the `has_schema` method in the Dialect. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuLFGEACgkQscQJ24LbaUSZYwCdEi5Vxhi0o20v3FV6dkWNn/Ui +MkAn3zWLmmDKs5ErKWCmCtPspoKY7SK =GwmL -END PGP SIGNATURE- -- 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] bug: changeset b89c179b0daf and user defined types
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. It seems that changeset b89c179b0daf (from Mercurial repository) broke user defined types handling. Here is a program that reproduces the problem: http://paste.pocoo.org/show/183084/ Here is the output of the program: http://paste.pocoo.org/show/183086/ Basically (without the need for such a complex program) the `bind_processor` method is no more called for a select statement (it is only called for the insert statement). I'm getting ProgrammingError, since the `ltree` type does not support the '-' character (that's why I need to escape the value). Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuH/kYACgkQscQJ24LbaUQQ3ACfTGhr7sYjmDo4HrEkJ9MzXdmR VkEAnjFsEhCkTnCuyuuPCByCAmU+sTUQ =uAQ5 -END PGP SIGNATURE- -- 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] bug: changeset b89c179b0daf and user defined types
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
we no longer make the assumption that the right side of an expression is the
same type as that of the left (since it is not true in many cases). So you
must coerce manually:
literal('programming-lang', LabelTree)
Ok, thanks.
It is not a problem to update the code, and it will work on older
SQLAlchemy versions.
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuIDzUACgkQscQJ24LbaUQQawCfZTPvpsTmfaoT1P9Ab3lRuMuc
VYQAniQ/aETpJAiN1ZUQKFVrzmuumCOr
=ThFZ
-END PGP SIGNATURE-
--
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] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 18, 2010, at 11:55 AM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] so what I had in mind is that, if its given a join as the left side, it just does the natural thing, i.e. joins to the right. If the natural join isn't available, then it does its usual search through the whole thing. Here is another tentative patch. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuEBiAACgkQscQJ24LbaUTzDQCfSSJBXe9LzQvKFcDva3oqojxp WvQAn2k0Ykdn3hh1mmfFSg3gTOx1okPQ =TiLH -END PGP SIGNATURE- -- 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. diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -2820,7 +2820,16 @@ global sql_util if not sql_util: from sqlalchemy.sql import util as sql_util -return sql_util.join_condition(primary, secondary) + +# First try the natural join +left = primary +while isinstance(left, Join): +left = left.right + +try: +return sql_util.join_condition(left, secondary) +except exc.ArgumentError: +return sql_util.join_condition(primary, secondary) def select(self, whereclause=None, fold_equivalents=False, **kwargs): Create a :class:`Select` from this :class:`Join`.
Re: [sqlalchemy] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ben De Luca ha scritto: Ok, thanks. I have never seen this pattern in use. If the import is done inside a function, usually there are no problems with circular module import. Its a performance gain as the second time you hit that function you don't have to import it again. Isn't this done by the Python interpreter? Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuAATgACgkQscQJ24LbaUTGMwCeIutBcbM4IZYzjH3uAuloLC5j gwoAmgL2QA0Her6a2o0eKcpMpIByf8Lo =Niy3 -END PGP SIGNATURE- -- 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] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] Since we are speaking about _match_primaries, I'm curious to know why the implementation is: def _match_primaries(self, primary, secondary): global sql_util if not sql_util: from sqlalchemy.sql import util as sql_util return sql_util.join_condition(primary, secondary) What is the need for a sql_util to be global? that pattern is used when there is a circular module import between two modules.the global + boolean is to avoid repeated calls to import. Ok, thanks. I have never seen this pattern in use. If the import is done inside a function, usually there are no problems with circular module import. just ask well is this particular foreign key the rightmost join on the left side and then its good. Non sure to understand what you have in mind, here. Do you mean that the checks: `if len(crit) == 0` and `len(constraints) 1` should not be done by the util.join_condition, and instead: 1) `_match_primaries` method will call `util.join_condition`, using the rightmost join on the left side (as in my patch) 2) if len(crit) == 0, then it will call `util.join_condition` again, but this time using the left side of the join, as is that is pretty much what I mean. if the left side is a join, then call join_condition with the rightmost side of the left first, if nothing returned, call with the full left side. Ok. If no one else is interested in this feature I can try to write a patch, with tests included. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt/BoMACgkQscQJ24LbaURR6wCdEdo5mitZcabEArqPe2BQV1Ez EY0An3YFxGgWE8LcHnHi6aqYlxoeKfPd =+p49 -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 17, 2010, at 9:56 AM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] By the way, I have found an incorrect behaviour in SQLAlchemy, when the select column list is empty. query = sql.select( None, contents.c.slug == 'python', from_obj=[join]) SQLAlchemy generates an incorrect SQL query. It should, instead, automatically add the columns from the `from_obj` list, skipping duplicate columns involved in a join. sounds more like an assumption to me. select(None) is specifically a select with no columns, which can be added later using column() (that might be the intent). Of course. The columns should be added when the SQL query is generated. yeah that's not how it works. the columns are added to the structure. statement compilation doesn't make huge guesses like that. Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9Hi0ACgkQscQJ24LbaURw+ACcDmBfcJTZyJJtn3w7Iw02tUY6 bZAAnAr+m0GNB2pwn/uQFxjHibvaTGpB =9462 -END PGP SIGNATURE- -- 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] How to search a particular hour?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Ning ha scritto:
Hi all,
Anyone have any ideal how to search a particular hour.
usually we can use datepart function in sql.
for example
datepart(hour, datetime) in (7,8)
But it is not really working in sqlalchemy
I tried
func.datepart(func.hour, xx.c.datetime)._in((7,8))
This is not the correct syntax.
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
func.datepart('hour', xxx.c.datetime)
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkt9PWsACgkQscQJ24LbaUSZDACfVErbMbOGAxdles+PSfyt1MFi
cnIAoJT5gz3aM6/HGATkakEczmgQ3B+L
=lWFS
-END PGP SIGNATURE-
--
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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 18, 2010, at 10:18 AM, Michael Bayer wrote: Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. that's a better idea. how about sqlalchemy.EXPAND_STAR ? although, still not a compiler level thing. you still want to be able to say select.c.colname. So it would occur at the point of objects being added to the FROM list. Right. A direct support for the special '*' column is better, and in SQL you can still specify additional columns in addition to '*'. As for the name to use, EXPAND_STAR is ok. The only alternative I can think about is something like sqlalchemy.schema.COLUMN_ALL. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9anIACgkQscQJ24LbaUTn0gCff3M4sFUoRz2xV//qYeKjTlLw fJkAn1AK19mS5B4/4ZLk8mjDSRTyDRc4 =wu+0 -END PGP SIGNATURE- -- 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] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] so what I had in mind is that, if its given a join as the left side, it just does the natural thing, i.e. joins to the right. If the natural join isn't available, then it does its usual search through the whole thing. What do you mean by natural join isn't available? There is no direct foreign key relationship between the left and the right side of the join? I think _match_primaries could, right before it raises its message, Since we are speaking about _match_primaries, I'm curious to know why the implementation is: def _match_primaries(self, primary, secondary): global sql_util if not sql_util: from sqlalchemy.sql import util as sql_util return sql_util.join_condition(primary, secondary) What is the need for a sql_util to be global? just ask well is this particular foreign key the rightmost join on the left side and then its good. Non sure to understand what you have in mind, here. Do you mean that the checks: `if len(crit) == 0` and `len(constraints) 1` should not be done by the util.join_condition, and instead: 1) `_match_primaries` method will call `util.join_condition`, using the rightmost join on the left side (as in my patch) 2) if len(crit) == 0, then it will call `util.join_condition` again, but this time using the left side of the join, as is ? to get non-default behavior, as always you'd specify the on clause. which you'd have to do anyway even without the natural feature if you wanted to joinunnaturally. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9cOYACgkQscQJ24LbaUSS7QCeMchE6p2t3WaHDJzH+dTAu2Xk BBUAmQHpDq8Naq9f4cWsolK9BRnjTBcf =UAU4 -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 16, 2010, at 7:07 PM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] just so I can understand fully can you modify the paste I sent to illustrate exactly how the results are happening, as that can help me to formulate the best version of this feature. I have pasted a complete working fragment of the code I have used: http://paste.pocoo.org/show/179089/ By the way, I still have a problem when using fold_equivalents. http://paste.pocoo.org/show/179092/ the feature would be built into the select(), that as each column is added, if a column of the same name exists and is related by a foreign key, it is skipped. I agree. It should be built into the select. By the way, I have found an incorrect behaviour in SQLAlchemy, when the select column list is empty. query = sql.select( None, contents.c.slug == 'python', from_obj=[join]) SQLAlchemy generates an incorrect SQL query. It should, instead, automatically add the columns from the `from_obj` list, skipping duplicate columns involved in a join. Can I fill a bug report for this? Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt79f0ACgkQscQJ24LbaUSr/gCfatYVT36ZvK+0prqXKVcck4sI e3YAnjgvwvi/d5qUT17gOx8DopvJvLPN =IdpF -END PGP SIGNATURE- -- 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] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I have found another limitation of SQLAlchemy, when handling joins. The code is here: http://paste.pocoo.org/show/179286/ I get: sqlalchemy.exc.ArgumentError: Can't determine join between 'Join object on Join object on content_types(159586732) and contents(159588044)(160166604) and content_article(159601292)' and 'categories'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. However, again, a plain SQL NATURAL JOIN has no problems figuring out how to do the join. How hard is to improve the util.join_condition function? In the code I posted, it is rather obvious how to do the join, since only one of the two foreign key constraint in the `content_article` table should be used for the join with the `categories` table. Instead SQLAlchemy is considering *all* foreign key constraints, even the ones that don't involve the two tables being joined. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt7+vQACgkQscQJ24LbaUTWWgCgjz8lvMdXzJucUWzI1XMAOZV1 pQ0AnRBmYousLdkM4fj+jNjxKchAlI2/ =sve5 -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] By the way, I have found an incorrect behaviour in SQLAlchemy, when the select column list is empty. query = sql.select( None, contents.c.slug == 'python', from_obj=[join]) SQLAlchemy generates an incorrect SQL query. It should, instead, automatically add the columns from the `from_obj` list, skipping duplicate columns involved in a join. sounds more like an assumption to me. select(None) is specifically a select with no columns, which can be added later using column() (that might be the intent). Of course. The columns should be added when the SQL query is generated. P.S.: it seems that your MUA has problems wrapping the text Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt8A7UACgkQscQJ24LbaUQ/hwCdE5kfYXs92cR1AxFlXWd0yLAl GG4AnA7kFRTJ9WVnXDmY+dJ+kfgdxxiS =VLzc -END PGP SIGNATURE- -- 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] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 17, 2010, at 9:19 AM, Manlio Perillo wrote: Hi. I have found another limitation of SQLAlchemy, when handling joins. The code is here: http://paste.pocoo.org/show/179286/ I get: sqlalchemy.exc.ArgumentError: Can't determine join between 'Join object on Join object on content_types(159586732) and contents(159588044)(160166604) and content_article(159601292)' and 'categories'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. However, again, a plain SQL NATURAL JOIN has no problems figuring out how to do the join. How hard is to improve the util.join_condition function? In the code I posted, it is rather obvious how to do the join, since only one of the two foreign key constraint in the `content_article` table should be used for the join with the `categories` table. Instead SQLAlchemy is considering *all* foreign key constraints, even the ones that don't involve the two tables being joined. it definitely only considers foreign keys that join between the left and right sides. Ah, you are right, sorry: col = fk.get_referent(a) if the left side is itself a join, all columns which reference or are referenced by the right are considered. the difference with NATURAL JOIN is that it is specifically against the rightmost side of the left chain. Feel free to implement this for join_condition assuming test coverage can be maintained. What do you think about the attahed patch? Note that if the dialect supports NATURAL JOIN, SQLAlchemy could use it. I have not implemented this in the patch; it should not be hard however. With the patch, my code works: join = sql.join( content_types, contents, isnatural=True ).join(content_article, isnatural=True ).join(categories, isnatural=True) and it does not change current code, since natural join is disabled by default. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt8CmYACgkQscQJ24LbaUQl8QCdEhkz2fGnNnjw98YWtkfW91Tp oKsAn3u0kLnkJ6m3zsez+atCAWeobBuD =bZU+ -END PGP SIGNATURE- -- 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. diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -96,7 +96,7 @@ return Join(left, right, onclause, isouter=True) -def join(left, right, onclause=None, isouter=False): +def join(left, right, onclause=None, isouter=False, isnatural=False): Return a ``JOIN`` clause element (regular inner join). The returned object is an instance of :class:`Join`. @@ -119,7 +119,7 @@ methods on the resulting :class:`Join` object. -return Join(left, right, onclause, isouter) +return Join(left, right, onclause, isouter, isnatural) def select(columns=None, whereclause=None, from_obj=[], **kwargs): Returns a ``SELECT`` clause element. @@ -1917,10 +1917,10 @@ return select([self], whereclause, **params) -def join(self, right, onclause=None, isouter=False): +def join(self, right, onclause=None, isouter=False, isnatural=False): return a join of this :class:`FromClause` against another :class:`FromClause`. -return Join(self, right, onclause, isouter) +return Join(self, right, onclause, isouter, isnatural) def outerjoin(self, right, onclause=None): return an outer join of this :class:`FromClause` against another :class:`FromClause`. @@ -2753,12 +2753,17 @@ __visit_name__ = 'join' -def __init__(self, left, right, onclause=None, isouter=False): +def __init__(self, left, right, onclause=None, isouter=False, isnatural=False): self.left = _literal_as_text(left) self.right = _literal_as_text(right).self_group() if onclause is None: -self.onclause = self._match_primaries(self.left, self.right) +left = self.left +if isnatural: +while isinstance(left, Join): +left = left.right + +self.onclause = self._match_primaries(left, self.right) else: self.onclause = onclause
[sqlalchemy] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
Here is a simple example of using joins in SQLAlchemy:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
x = schema.Table(
'x', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('x', types.Text, nullable=False)
)
y = schema.Table(
'y', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('y', types.Text, nullable=False),
schema.ForeignKeyConstraint(['id'], [x.c.id])
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
engine.execute(x.insert(), id=1, x='X')
engine.execute(y.insert(), id=1, y='Y')
query = sql.join(x, y).select()
r = engine.execute(query).fetchone()
print r['id']
finally:
engine.drop(metadata)
This code will raise an:
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'id' in result
set! try 'use_labels' option on select statement.
Now, I would like to avoid using labels, since it will make code much
more verbose.
What is the reason why SQLAlchemy is including the `id` column two times?
After all, it should know that the `id` column is being used for the join.
In plain SQL (Postgresql database):
manlio= select * from x NATURAL JOIN y;
id | x | y
- +---+---
1 | X | Y
(1 riga)
manlio= select * from x JOIN y USING (id);
id | x | y
- +---+---
1 | X | Y
(1 riga)
the `id` column is being added only one time, as it should be.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkt62u8ACgkQscQJ24LbaUQGkwCfa/cSeg9xk1AFHTqTuDrA+LPt
aREAn0SiG75RNFav7cBv2M0Cacu2iyUx
=I+f+
-END PGP SIGNATURE-
--
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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: [...] query = sql.join(x, y).select() r = engine.execute(query).fetchone() [...] This code will raise an: sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'id' in result set! try 'use_labels' option on select statement. send fold_equivalents=True to join().select(). This flag has been deprecated for awhile but I suppose it is harmless if you truly have a use case for it (i.e. i will consider un-deprecating it, i didnt think anyone would ever need it). Well, the use case it to avoid the use of: 1) use_labels 2) ad hoc column names, to avoid ambiguous columns 3) explicit labels Unfortunately, fold_equivalents is not a generic solution for me I have a generative query, where I later add the select_from object. The query is generative, since the tables involved are not know in advance. I have several plugins, that take a query instance as input, and return the (modified) query, and the additional tables involved. These tables are later joined together. [...] Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt65DsACgkQscQJ24LbaUR98wCfctnfwAHUHsnJ1j9pskhp6u5z MtwAnixPKZqMg9VzCtdTOlLAO0dN4AGu =8qMv -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] Unfortunately, fold_equivalents is not a generic solution for me I have a generative query, where I later add the select_from object. The query is generative, since the tables involved are not know in advance. I have several plugins, that take a query instance as input, and return the (modified) query, and the additional tables involved. These tables are later joined together. I don't understand what you want then. the flag is applied at the point of select() where the list of columns that will be in its columns clause is passed, and then its fixed...unless you're adding columns after the fact (an unusual pattern). Here is the code I have: http://paste.pocoo.org/show/178959/ What each plugin do is: - - add additional columns in the select list - - add additional where clauses or order_by clauses Basically I have a simple table inheritance scheme, where each additional table derive from the `contents` table, Feel free to look at its implementation and suggest what it is you're looking for. I think I need to refactor that code. Each plugin *must not* add columns; instead the columnn to add should be computed using the util.folded_equivalents if tables are joined. So I think that I simply need the util.folded_equivalents function, thanks. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt6+ukACgkQscQJ24LbaURAtACcC2jVBx4GEc04Yb8Yckbvpve6 eh4An16RWuv9gwfpfj7Vc4rreXBcSogU =UsZP -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: Here is the code I have: http://paste.pocoo.org/show/178959/ What each plugin do is: - add additional columns in the select list - add additional where clauses or order_by clauses I don't see how you are getting column conflicts with that recipe: Because each plugin added the additional tables used in the query select list: def get_content_article_by_slug(environ, content_type, query): tables = [schema.content_article, schema.categories] query = query.column(schema.content_article) query = query.column(schema.categories) return query, tables I have refactored the code and now it works without problems. I use both the select method on the Join instance (when the plugins does not need to modify the query object), and the util.folded_equivalents function by hand. I have read the ticket 1131, and it seems that the folded_equivalents function was used, in the ORM, for exactly the same reason as I'm doing now. So, it seems that nobody is doing this without using the ORM. Is it a problem to revoke the deprecation on the folded_equivalents? I don't think I can go without it. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt7Gc8ACgkQscQJ24LbaUTztQCcC//niqKT21umxaKCXEy4kcPl mWYAoIP4k2kh4awv7EuJgppKwS1jXu/1 =45t9 -END PGP SIGNATURE- -- 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] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] just so I can understand fully can you modify the paste I sent to illustrate exactly how the results are happening, as that can help me to formulate the best version of this feature. I have pasted a complete working fragment of the code I have used: http://paste.pocoo.org/show/179089/ By the way, I still have a problem when using fold_equivalents. http://paste.pocoo.org/show/179092/ Again, the plain SQL code does not show duplicate columns here: wsgix= select * from content_types NATURAL JOIN contents NATURAL JOIN content_articles NATURAL JOIN categories WHERE slug='python'; type | category_slug | slug - -+---+ article | programming | python (1 riga) Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt7MzQACgkQscQJ24LbaUQSIACeMnH/OGepSOJrVpnMZYvUNo7O pu0An0GE4BUNGHaLAyuI+frAbuEm5dFw =/13b -END PGP SIGNATURE- -- 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] draft SQL schema support in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: Hi. I have written a module to implement support to SQL Schema in SQLALchemy. The code is available here: http://paste.pocoo.org/show/17/ It requires this patch: http://paste.pocoo.org/show/175556/ obviously we can't accept the patch, if that's what you're proposing, since it removes necessary functionality. I'm not really sure why breaking SQLA core should be necessary. It is necessary because it prevents the before-create MetaData listeners to do things that affects how tables are handled. What is the reason why the entire tables collection should be passed to the before-create MetaData listeners? It is not even documented: http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktxL90ACgkQscQJ24LbaUS0vQCgjqqjdviuSqqmqjzUYwGGNjCx eMcAn1WdacMxHltGDNyAXHfdmME0KTYp =hH/f -END PGP SIGNATURE- -- 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] suggestions about SQL SCHEMA handling in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: When writing this code I found some problems with SQLAlchemy: 1) There is no support for SQL Schema at all. It is ok, for me, if there is no direct support for SQL Schema in SQLAlchemy, but this is a standard feature, so SQLALchemy dialects should: * define a `supports_schemas` attribute, as a boolean * define a `has_schema` method, to check is a schema exists Can I fill a ticket with this feature request? can you describe this for me ? We support schemas from the perspective that you can define a table that is in a particular schema, so i dont understand the statement no support for SQL schema at all. The at all was unnecessary, sorry. What I meant was that there is no support to check if a dialect supports schemas, and to check is a schema is already defined. [...] `has_schema()` is a totally fine method for which we'd accept a patch to the Inspector class as well as dialects. Ok, thanks. I can only provide a patch for PostgreSQL, but it's a starting point. 2) I would like to emulate SQL Schema support in SQLite, using secondary databases, attached to the main database. [...] The problem is that SQLAlchemy executes these queries **before** my DDL listener is called, so the query fails because I don't have yet attached the secondary database. if your database requires special configuration in order for schemas to be present, you would do that before issuing anything with metadata creation. I'd advise using a PoolListener to handle this configuration. It should not be the job of create_all to attach to a particular schema, since create_all is not a configure the database command - many applications don't ever call create_all but still may be using sqlite schemas. Please note that the code I have posted is only meant to support my current usage pattern of SQLAlchemy. PoolListener is rather low level; if I use them I need to know in advance the secondary databases to attach. I think that this behaviour is incorrect. The before-create listeners of a MetaData object should be called before checking if the tables exist. I can see the awkardness here but the current contract of before-create is that it means before I create this list of Tables that I have found need creation, and here they are.Its something we may have to revisit if there's really a reason for a before I do anything hook - but I don't buy the case of I need to attach schemas in the create_all() as a necessary use case. Ok, fine for me. Auto attach database for SQLite it's not something i really need and it can still can be implemented using a PoolListener. See my reply to the more recent thread about Schema in SQLAlchemy about the reason why I was assuming that changing current behaviour was fine. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktxNRIACgkQscQJ24LbaURRdACcCj51Sb5Gp1cqZNDUv3U6zM1Y LKgAn35CBel2vOrfkCd4yK5XMVMFf+V/ =xBEd -END PGP SIGNATURE- -- 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] suggestions about SQL SCHEMA handling in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
Manlio Perillo wrote:
PoolListener is rather low level; if I use them I need to know in
advance the secondary databases to attach.
What precisely is the use case for an application that will use a database
X, will be using remote schemas P, Q, and R, but its unreasonable for
those schemas P, Q and R to be configured along with the database
connectivity ?
The reason is that I'm abusing the MetaData object to implement
namespace (namespace = SQL schema) support.
So it is natural (for me) to create the schemas as a DDL attached to the
MetaData.
Note that I'm also abusing SQLite attach database support, in order to
emulate schemas.
I see an easy feature add to the SQLite dialect that would
allow:
create_engine('sqlite:///X.db', schemas=['p', 'q', 'r'])
Not sure if SQLAlchemy should implement an high level interface (schema)
to a low level SQLite feature.
You have to force an algorithm to associate a schema name to a database
file name.
Alternatively, I can even see using connection options for this, if you
want to attach to those schemas per connection:
conn = engine.connect().execution_options(sqlite_attach_schemas=['p', 'q',
'r'])
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktxj0kACgkQscQJ24LbaURZBgCgjX5eSrRudVh04ccoomIGMZw9
fvoAn1mLAiZd79mNdjHITAvAhHngrTce
=h8BJ
-END PGP SIGNATURE-
--
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] suggestions about SQL SCHEMA handling in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: Hi. I would like to use SQL schemas in my projects, however support in SQLAlchemy is quite poor. What I want to do is to create a custom MetaData object, that allows me to specify the default schema name to use. All tables bound to that meta data, will be created inside that schema. With a DDL listener, I then make sure CREATE SCHEMA and DROP SCHEMA statements are issued. does this work for you ? [...] Ok, sorry for the delay. Following your suggestions I have written this: http://paste.pocoo.org/show/175512/ What do you think? When writing this code I found some problems with SQLAlchemy: 1) There is no support for SQL Schema at all. It is ok, for me, if there is no direct support for SQL Schema in SQLAlchemy, but this is a standard feature, so SQLALchemy dialects should: * define a `supports_schemas` attribute, as a boolean * define a `has_schema` method, to check is a schema exists Can I fill a ticket with this feature request? 2) I would like to emulate SQL Schema support in SQLite, using secondary databases, attached to the main database. However, I noted that SQLAlchemy executes the following query: BEGIN PRAGMA test.table_info(test) () ROLLBACK for a table named test in a schema named test (to be precise, the previous query is exected two times). It seems that SQLAlchemy assumes that the schema refers to a secondary database. The problem is that SQLAlchemy executes these queries **before** my DDL listener is called, so the query fails because I don't have yet attached the secondary database. I think that this behaviour is incorrect. The before-create listeners of a MetaData object should be called before checking if the tables exist. Should I fill a bug report? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktwipkACgkQscQJ24LbaUQ7uACggb3Q3kgAQL99XQaqt6lOxSRF Fx8AnRxjpmtcpSJ8foMhAptKUWxaF6X3 =ZCiM -END PGP SIGNATURE- -- 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] draft SQL schema support in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I have written a module to implement support to SQL Schema in SQLALchemy. The code is available here: http://paste.pocoo.org/show/17/ It requires this patch: http://paste.pocoo.org/show/175556/ It currently supports PostgreSQL and SQLite. I would like to get some review, since I plan to use this code in production (using PostgreSQL). Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktwnXAACgkQscQJ24LbaURa8wCfXniNN0Cao/SfrTtbc28xOVMT oJoAn1wanZUgFk35Suc5fJ9triACp9v0 =1OXm -END PGP SIGNATURE- -- 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] UserDefinedType and result_processor
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
On Feb 1, 2010, at 8:31 PM, Manlio Perillo wrote:
Hi.
I have defined a custom type for PostgreSQL ltree support.
[...]
This works, when I create the table and insert some values.
However when I select data from the database, result_processor is not
called.
nothing unusual above and UserDefinedType.result_processor is covered in
tests (which fail if I change result_processor, so its called), so you'll
have to provide more specifics.
Ok, sorry again for the noise.
The cause was a literal value ('*') specified in the column clause in
the select statement.
Unfortunately SQLAlchemy is not (yet?) smart enough to deduce the types
for literal queries.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktoDekACgkQscQJ24LbaUTSLQCgjc8egOSlx3Qq8spcyczRjKpJ
HkcAoItDI4+sCGSTUaMkbBL7JDGxBT95
=kG1g
-END PGP SIGNATURE-
--
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] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I have written a small class in order to support PostgreSQL tsearch type:
class TextSearchVector(UserDefinedType):
Support for full-text search
See
http://www.postgresql.org/docs/8.4/static/datatype-textsearch.html
def get_col_spec(self):
return 'tsvector'
The problem with this code is that a string is converted to a tsearch
type without first normalizing it; this can also cause a syntax error.
The solution is to use the `to_tsearch` function.
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each value.
Is this possible to do with SQLAlchemy?
It seems to me that it is not possible.
It is not possible to specify an SQL scalar select statement in a custom
type bind_processor method, and it is not possible to specify an SQL
scalar select statement in an insert statement.
Can this be solved using SQLAlchemy?
Or should I simply use triggers?
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktoc0IACgkQscQJ24LbaUQ/EgCeIKThU9dV8DZT0qampIR1iHRx
bP4AoI1/DPoEXRyewZGHLs6LF8DdCRZp
=YKtw
-END PGP SIGNATURE-
--
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] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
[...]
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each
value.
Is this possible to do with SQLAlchemy?
[...]
OK you mean SQL expression during compilation. This is ticket #1534
which is not yet implemented.
After some tests I got something that works
http://paste.pocoo.org/show/173099/
The documentation seems to confirm this:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.insert
SQLAlchemy still continues to surprise me!
I need to download the documentation in PDF format, and take some time
study it.
I don't know if an example of this feature is available in the tutorial;
if not, it should be added.
Right now you'd have to set attributes to
the to_tsvector() expression directly, note that the ORM and such accept
SQL expressions as values.Which means if you're dealing just with ORM,
you can use a @validates or similar (and maybe a comparator too) that
wraps incoming values into to_tsvector().
It is 4 years that I don't touch the ORM ;-).
The example in
examples/postgis/postgis.py illustrates these techniques.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktogfkACgkQscQJ24LbaUSjwgCfevuSHb0YjMGNMFXhm+imJHqY
YSsAn1wypa/GG86TfGHMIGbFvf9lctVN
=eqiB
-END PGP SIGNATURE-
--
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] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: [...] OK you mean SQL expression during compilation. This is ticket #1534 which is not yet implemented. After some tests I got something that works http://paste.pocoo.org/show/173099/ [...] errr, you can put SQL expressions into insert.values(), but not in the params sent to execute() (that was discontinued after 0.4). I have never used the values method. Is this a recent feature? the example you have is actually executing the sql.select() statement beforehand since thats what scalar() does. you're looking for query = test.insert().values(text=sql.func.to_tsvector(u'some text')) engine.execute(query) Thanks. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktolCUACgkQscQJ24LbaURINgCglj+N2JKqkWit1/DFCEBff2Kd 7fsAn0F8Y5mUCYyqWSxydosxk36RzsGZ =VHYH -END PGP SIGNATURE- -- 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] syntax error in a multi INSERT statement
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I'm experiencing a strange problem with a multi INSERT statement.
Strange because I'm unable to reproduce it.
Here are both SQLAlchemy (trunk) and PostgreSQL (8.3) logs:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at
position 9 near -
INSERT INTO cms_categories (type, category_slug, category,
description, seq) VALUES (%(type)s, %(category_slug)s, %(category)s,
%(description)s, nextval('cms_categories_seq')) [{'category':
'Normativa e legislazione', 'type': 'article', 'description': uLa
legislazione italiana e la legislazione in altri paesi comunitari. Il
diritto internazionale e transnazionale: cosa bisogna fare se si \xe8
Italiani all'estero o Italiani residenti in Italia ma che intendono
sposare cittadini di altre nazionalit\xe0.\n, 'category_slug':
'normativa-e-legislazione'}, {'category': 'Riti e religioni', 'type':
'article', 'description': uLe principali religioni monoteistiche e i
riti previsti in ciascuna. I riti misti, le compatibilit\xe0 e
incompatibilit\xe0, le conversioni. L'approccio razionalistico, il
matrimonio civile e i patti sociali.\n, 'category_slug':
'riti-e-religioni'}] ... and a total of 17 bound parameter sets
2010-02-01 16:08:14 CET ERROR: syntax error at position 9 near -
2010-02-01 16:08:14 CET STATEMENT: INSERT INTO cms_categories (type,
category_slug, category, description, seq) VALUES (E'article',
E'normativa-e-legislazione', E'Normativa e legislazione', E'La
legislazione italiana e la legislazione in altri paesi comunitari. Il
diritto internazionale e transnazionale: cosa bisogna fare se si è
Italiani all''estero o Italiani residenti in Italia ma che intendono
sposare cittadini di altre nazionalità .
', nextval('cms_categories_seq'))
The problem is probably with the tab character in the category field.
I have no idea why there is a tab character there.
Data is loaded from a YAML file, but even if I use the same data I'm not
able to reproduce the problem.
The original program loads, in an unique transaction, several fixtures.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktm/ZMACgkQscQJ24LbaUToxgCgmM8ADXGUN8hJqHCsLyYzWSDs
sXMAn2mzj+0haQyAa2mfP01N4mouJ/rw
=HETb
-END PGP SIGNATURE-
--
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] suggestions about SQL SCHEMA handling in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I would like to use SQL schemas in my projects, however support in SQLAlchemy is quite poor. What I want to do is to create a custom MetaData object, that allows me to specify the default schema name to use. All tables bound to that meta data, will be created inside that schema. With a DDL listener, I then make sure CREATE SCHEMA and DROP SCHEMA statements are issued. By the way, another thing I would like to do is the ability to specify some other defaults for table creation. As an example, assume nullable=False as default, since I set a column not nullable the 99% of the time. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktnApoACgkQscQJ24LbaUSMTQCdGEyB/UqIYi9aM9es6BI7GO+4 FzQAn3Lwa+tJSa9FPa1NeRlOQ8FvpCFV =a0XY -END PGP SIGNATURE- -- 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] syntax error in a multi INSERT statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: Hi. I'm experiencing a strange problem with a multi INSERT statement. Strange because I'm unable to reproduce it. Here are both SQLAlchemy (trunk) and PostgreSQL (8.3) logs: [...] not sure either. My approach here would be to slowly walk it back to a simpler case. Edit the YAML file to a smaller and smaller set of data, removing elements one at a time, including fewer records, fewer characters within each record, reduction of non-ascii chars, until the element that creates the problem is identified. Ok, forget this, sorry. The problem is not with SQLAlchemy. I'm using the ltree extension of PostgreSQL: http://www.postgresql.org/docs/8.4/static/ltree.html and the syntax error was raised by the ltree module, since a label tree field can not contain a '-' character. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktnCrIACgkQscQJ24LbaUSq+gCggtXqdH5AUIHB1J1mpDFZGwVM xHQAn0Ynf8ksxoSD8i7he68PqZTlhDgP =nrl5 -END PGP SIGNATURE- -- 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] UserDefinedType and result_processor
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I have defined a custom type for PostgreSQL ltree support.
The code is very simple (I'm using version from trunk):
class LabelTree(UserDefinedType):
def bind_processor(self, dialect):
def process(value):
assert '_' not in value
assert '.' not in value
return value.replace('-', '_').replace('/', '.')
return process
def result_processor(self, dialect, coltype=None):
def process(value):
return value.replace('_', '-').replace('.', '/')
return process
def get_col_spec(self):
return 'ltree'
This works, when I create the table and insert some values.
However when I select data from the database, result_processor is not
called.
The database is PostgreSQL (psycopg2).
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktngG0ACgkQscQJ24LbaUT4ngCfRbIr/haPhB4UoXtTDk5TJ11q
/YYAn1NJ5rLgkjvXbMaf2TkDpvefBrTH
=N6IO
-END PGP SIGNATURE-
--
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] problem when executing multiple insert statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I'm here again with a problem I don't know if it is a bug in SA or in my
code.
Here is the offending code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True),
schema.Column('y', types.Boolean, default=True, nullable=False)
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
finally:
engine.drop(metadata)
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkti31QACgkQscQJ24LbaUQSdgCfctrxG3mAH22uWIoVj65EXCKH
bKIAnjPmGw5CvQID6JvW7bHpn5aAgD2j
=m+dS
-END PGP SIGNATURE-
--
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 when executing multiple insert statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
King Simon-NFHD78 ha scritto:
[...]
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
[...]
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
[...]
This is explained in the last paragraph of
http://www.sqlalchemy.org/docs/sqlexpression.html#executing-multiple-sta
tements:
When executing multiple sets of parameters, each dictionary must have
the same set of keys; i.e. you cant have fewer keys in some dictionaries
than others. This is because the Insert statement is compiled against
the first dictionary in the list, and it's assumed that all subsequent
argument dictionaries are compatible with that statement.
Ah, thanks.
I think a check has been added in 0.6 so that an exception is raised if
you don't follow this advice.
No, I'm using the version from trunk, and there is no exception or warnings.
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkti8dUACgkQscQJ24LbaURziQCdH+Co40QqeYv+9YVWXyXay0/+
9zYAn1bpZ7RxjkSjeNULeT4alxokFqYG
=aoIT
-END PGP SIGNATURE-
--
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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
It seems there is a bug with SQL CASE support in SQLAlchemy (I'm testing
using trunk).
Here is the original SQL statements:
CREATE TABLE test (
x INTEGER PRIMARY KEY
);
SELECT
CASE WHEN 'true'
THEN (SELECT EXISTS (SELECT x FROM test))
END;
And here is the Python code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True)
)
engine = create_engine('sqlite://')
subquery = sql.exists(test.select().as_scalar())
clause = sql.case([(True, subquery.select().as_scalar)])
query = clause.select()
engine.execute(query)
I get an error:
Traceback (most recent call last):
File case.py, line 13, in module
subquery = sql.exists(test.select().as_scalar())
File
/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py,
line 520, in exists
return _Exists(*args, **kwargs)
File
/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py,
line 2688, in __init__
s = select(*args, **kwargs).as_scalar().self_group()
File
/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py,
line 238, in select
return Select(columns, whereclause=whereclause, from_obj=from_obj,
**kwargs)
File
/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py,
line 3492, in __init__
if columns:
File
/usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py,
line 1289, in __nonzero__
raise TypeError(Boolean value of this clause is not defined)
TypeError: Boolean value of this clause is not defined
I'm not sure if my code is correct.
If it is not correct, what it the correct method to define that query?
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkth7S4ACgkQscQJ24LbaUQEXgCggBUV3orSFAPPK155cVHplpb4
3U0An1djFkgt3LE6LHQOCHrepVt4F0Vc
=hL1p
-END PGP SIGNATURE-
--
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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
[...]
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True)
)
engine = create_engine('sqlite://')
subquery = sql.exists(test.select().as_scalar())
clause = sql.case([(True, subquery.select().as_scalar)])
query = clause.select()
engine.execute(query)
its more SQLA not warning you about an argument it doesn't like and then
it leading into problems. it should raise an error earlier. basically
exists() is like a select() and accepts the list of column expressions as
a list. if you were to pass your select inside of a [], you'd get a
valid SQL expression, though with two levels of select.
sql.exists([test]) or exists().select_from(test) would be more effective.
Ok, thanks.
However, now I'm not sure to understand the example here:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists
# use on an existing select()
s = select([table.c.col1]).where(table.c.col2==5)
s = exists(s)
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkth+0EACgkQscQJ24LbaUTf0QCfTWwPMRvTYMAH7R/mU2CCb2fQ
DaYAn0EoxWUCkFv8e/omK0A+tx8MY2S0
=i3I0
-END PGP SIGNATURE-
--
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] possible bug with SQL CASE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: However, now I'm not sure to understand the example here: http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=exists#sqlalchemy.sql.expression.exists # use on an existing select() s = select([table.c.col1]).where(table.c.col2==5) s = exists(s) wow, look at that. I'm glad exists() accepts that, since it seemed awkward that one would be required to say exists([someselect]). your call to as_scalar() was throwing it off. It is accepted in the same way now as a plain select() in r6702. By the way; with another query (but still using exists and case) I get this error: TypeError: self_group() takes exactly 2 arguments (1 given) /usr/local/lib/python2.5/site-packages/sqlalchemy/sql/expression.py, line 2411: (_literal_as_binds(c).self_group(), _literal_as_binds(r)) for (c, r) in whens It is probable that this query is broken, too. Or it may be a bug in SQLAlchemy. If required, I can try to write a minimal example that reproduces the problem. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktiBZsACgkQscQJ24LbaUQgQACeJWYpAlkSloQZD/jBqUfNYApQ 7iMAn2eWtTI3dTBqjUCj13DOmjY6rPBN =RIh0 -END PGP SIGNATURE- -- 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] error handling for sessionmaker function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I'm updating some of my code to SQLALchemy 0.6, and I have noted a problem with the sessionmaker function. The problem is a compatibility one: old versions use the transactional parameter, new ones the autocommit parameter. Usually, to handle these problems I use the try/except method: try: return orm.sessionmaker(bind=bind, autocommit=autocommit) except TypeError: # COMPAT: SQLAlchemy 0.4.x, deprecated in 0.5.x transactional = not autocommit return orm.sessionmaker(bind=bind, transactional=transactional) However this does not work, since error is raise only ewhen the actual Session instance is created. As far as can understand, the sessionmaker function supports keyword arguments since user can specify a custom session class to use. Can error handling be improved? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktLvVYACgkQscQJ24LbaUSrRQCfab1w/JR+KUNdAo188hEn4NgK Rf8AoIJR/iGu0xHGTUv09Z3A6sjeydFg =w5ts -END PGP SIGNATURE- -- 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: problems with pickle and RowProxy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Oct 11, 2009, at 12:59 PM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] BufferedColumnRow is also not picklable. The reference to the parent ResultProxy is still required which itself is not picklable. [...] don't worry about it. Describing the feature in email was most of the work so I just added the feature to RowProxy, and also changed the namedtuple used by Query, in r6394. If you can give 0.6 a try that's always a good thing. Thanks, I can confirm that it works. I don't plan to use 0.6 branch in the near future, but I always try to test my applications against several versions: 0.4.2p2, 0.5.6 and trunk. Since this feature is only required in testing environment, there are no problems. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrTLbgACgkQscQJ24LbaUQduACfWDOkKlp7rdT3iBjtFwVXEpBP yv4AnRBO6hbwXIXbD32U0WIB7SSmkD0i =2JaB -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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] problems with pickle and RowProxy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I have noted a problem when a I try to pickle an instance of sqlalchemy.engine.base.RowProxy: ... File /usr/lib/python2.5/pickle.py, line 306, in save rv = reduce(self.proto) File /usr/lib/python2.5/copy_reg.py, line 76, in _reduce_ex raise TypeError(a class that defines __slots__ without TypeError: a class that defines __slots__ without defining __getstate__ cannot be pickled Is this limitation really necessary? Thanks Manlio Perillo -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrRylEACgkQscQJ24LbaUR5YwCbBF1KjQTWVpVU6P6I7X9taouv vS8AoIRfmoyfF/hKt5vf+tBJ6ZqtBA3C =BMbb -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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: problems with pickle and RowProxy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Oct 11, 2009, at 8:06 AM, Manlio Perillo wrote: Hi. I have noted a problem when a I try to pickle an instance of sqlalchemy.engine.base.RowProxy: ... File /usr/lib/python2.5/pickle.py, line 306, in save rv = reduce(self.proto) File /usr/lib/python2.5/copy_reg.py, line 76, in _reduce_ex raise TypeError(a class that defines __slots__ without TypeError: a class that defines __slots__ without defining __getstate__ cannot be pickled Is this limitation really necessary? a RowProxy has an active handle to the parent Result which in turn has an active handle to the DBAPI cursor, and it's data representation is that of the DBAPI's cursor row, which itself is only a dict like object in the general case. The actual data isn't pulled across from the DBAPI row until requested - hence the name Proxy. My use case is quite simple; I always call .fetchall from a ResulProxy, so all data is in memory. I don't see any reasons why a RowProxy should not be pickleable in this case. I have tried with SQLAlchemy 0.4.2p3 (the version I'm using in production, and I get this traceback: http://paste.pocoo.org/show/144372/ I have to say that I don't understand the error. [...] If there is no simple method to pickle a RowProxy, then I should create a custom driver where the BufferedColumnRow is used. I have not checked the sources, is it possible to create a custom database driver as an external plugin? Note that I need to use pickle only in a testing environment, for a web application. The reason is that I have a resource rendering function that, in a testing environment, pickle the content of the Mako context dictionary and return it from a WSGI application; then this data is unpickled so that a test case can easily test the application. Thanks Manlio Perillo -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrR9iUACgkQscQJ24LbaUQRbACgk8d2JLvDpPjWxTXe+WtauJMa iBwAn1CgrjxyKZ9JpwosNYAxf2kfdyCx =Y7g9 -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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] PostgreSQL: subqueries and alias
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I have noted that with this query:
# query is the original query
# query_aux is the query required to compute the number of rows returned
# by the original query
query_aux = sql.select(
[sql.func.count()], from_obj=[query])
I get, with PostgreSQL 8.3.7 (and SQLALchemy from trunk):
ProgrammingError: (ProgrammingError) subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo
The solution is to explicitly add an alias
query_aux = sql.select(
[sql.func.count()], from_obj=[query.alias('subquery')])
However IMHO this should be done by SQLALchemy, and not by the programmer.
What do you think?
Thanks Manlio Perillo
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkoByRkACgkQscQJ24LbaUQz8QCeIrSG2o+XeiZEi1PUY7RmbGG3
148An39JLOw/KRnffUcKknmF8aQQnZmh
=hiSI
-END PGP SIGNATURE-
--~--~-~--~~~---~--~~
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] integration of SQLAlchemy in a WSI environ
I have completed my idea of integration of SQLALchemy in a WSGI environ, in my wsgix framework. wsgix (http://hg.mperillo.ath.cx/wsgix) is a WSGI framework whose main design goal it to provide a low level but flexible layer over WSGI. Since WSGI is mainly a functional API, wsgix is the same: a collection of functions that explicitly use the WSGI environ to keep state (and also configuration options). The integration with SQLALchemy is in wsgix.dbapi (http://hg.mperillo.ath.cx/wsgix/file/tip/wsgix/dbapi/__init__.py). The integration consist only of two pair of functions: contextual_connect(environ, engine) close_contextual_connection(environ, engine) and scoped_session(environ, session_factory) close_scoped_session(environ) These functions use the WSGI environ dictionary to keep the state, and a reference count is used to make sure resources are closed when no more needed. There are also some high level wrappers around contextual_connect. An example of high level wrapper around scoped_session can be found instead in the test suite (since I'm not sure about the best patterns for advanced session usage): http://hg.mperillo.ath.cx/wsgix/file/tip/wsgix/test/dbapi/test_dbapi.py I would really appreciate comments about the code. Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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] OperationalError: (OperationalError) cannot commit transaction - SQL statements in progress
Hi. I'm having problems with the exception in subject, with pysqlite (on Debian Lenny). Unfortunately, I'm unable to reproduce the problem, but basically it is an update statement in a transaction. If there is only one row in the table, all is ok; but if there are more then one rows, I get this exception. If I catch the exception and execute the transaction again, it finally succeed. This is the log of executed queries: BEGIN SELECT users.username, users.password, users.email, users.role, users.enabled, users.registration_date, users.last_login FROM users WHERE users.username = ? ['[EMAIL PROTECTED]'] UPDATE users SET last_login=? WHERE users.username = ? ['2008-07-16 11:08:25.944900', u'[EMAIL PROTECTED]'] COMMIT BEGIN SELECT contacts_info.username, contacts_info.full_name, contacts_info.telephone, contacts_info.fiscal_code FROM contacts_info WHERE contacts_info.username = ? ['[EMAIL PROTECTED]'] SELECT contacts_address.username, contacts_address.administrative_area_code, contacts_address.locality, contacts_address.address_line, contacts_address.address_line_2, contacts_address.postal_code FROM contacts_address, contacts_info WHERE contacts_info.username = ? ['[EMAIL PROTECTED]'] COMMIT BEGIN UPDATE contacts_info SET full_name=?, telephone=?, fiscal_code=? WHERE contacts_info.username = ? ['Manlio Perillo x', u'B', u'C', '[EMAIL PROTECTED]'] UPDATE contacts_address SET administrative_area_code=?, locality=?, address_line=?, address_line_2=?, postal_code=? WHERE contacts_address.username = ? [u'AV', 'X', 'Y', 'Z', u'W', '[EMAIL PROTECTED]'] COMMIT ROLLBACK Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: integrating SQLAlchemy in a WSGI based framework
Michael Bayer ha scritto: On Jul 8, 2008, at 2:50 PM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] Current integration approaches focus on the scoped_session() construct as home base for the current transaction. Ah, sorry. Sessions are not a problem, since the common pattern for web applications is to create a new session for each transaction (if the user does not like it, then it can use its how logic). well you could have a single session that uses multiple transactions. This is actually more common. Right, thanks. I will add code for storing the session inside the environ dictionary. def scoped_session(environ, **kwargs) where the session factory is stored inside the environ, too. My problem is with contextual connections. the connection-based approach should work as well. whats the problem ? No problems, I was asking because I was not sure. Thanks again Manlio Perillo --~--~-~--~~~---~--~~ 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] sessions and transactions
Hi. Is it ok to do something like: def helper(conn): sess = orm.create_session(bind=conn) t = Test() t.x = 121 sess.save(t) sess.flush() sess.close() db = create_engine(URL) db.transaction(db, helper) ? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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] integrating SQLAlchemy in a WSGI based framework
Hi.
I'm writing my own framework based on WSGI (and somehow on Nginx mod_wsgi):
http://hg.mperillo.ath.cx/wsgix
I want to implement the best possible integration with SQLAlchemy.
SQLAlchemy already implements all I need, the only problem is that for
proper functioning of some features (like emulated nested transaction),
it needs to use a threadlocal storage.
This is bad, since in Nginx we can have multiple concurrent connections
per thread (using psycopg2 extension for asynchronous queries).
I'm thinking to add a function:
# XXX add support for additional parameters like close_with_result?
def contextual_connect(environ, engine):
connection = environ.get('wsgix.dbapi.contextual_connection')
if connection is None:
connection = engine.connect()
environ['wsgix.dbapi.contextual_connection'] = connection
return connection
Using this function I can do:
connection = contextual_connect(environ, engine)
engine.transaction(callable, connection, *args, **kwargs)
and so on, perhaps implementing some convenient wrappers.
Moreover I plan to write a pseudo middleware that will take care of
proper cleanup of the contextual connection at the end of the request.
My question is: will this suffice or should I implement something more
integrated with SQLAlchemy?
Another problem is with the ORM, but I'm still studing it since a lot
has changed since last time I have used it.
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: integrating SQLAlchemy in a WSGI based framework
Michael Bayer ha scritto: About integrating SQLAlchemy in WSGI with a per request scoped session. [...] Current integration approaches focus on the scoped_session() construct as home base for the current transaction. This is an adaption of 0.3's SessionContext, and the idea is the same; it receives a scopefunc which can return any token you like that identifies the current context. By default, it returns the current thread identifier, but you can change it to examine other resources to get at the WSGI environ or similar. This is not possible. How should I get the WSGI environ? It MUST be passed explicitly. NOTE: in theory using greenlets, one always knows the current greenlet, so you can have something like greenlet local storage, but I would like to avoid this, and always explicitly pass the environ. When using ORM session, the automatic nesting of begin/commit behavior is available if you set autocommit to True to start with, then use begin()/commit() pairs, sending the flag subtransactions=True to allow subtransactions. I'm not sure if your scheme overall calls for that. For now its not a problem. Lately I tend to avoid the use of the ORM, I will think about this in future. I think the method you're using with an individual connection would work fine as well - the begin() method on connection also nests itself with a subtransaction when called multiple times. Yes, this is what I want. Obviously, if multiple requests occur async in a single thread, you have to ensure the connection pool is sized appropriately otherwise your whole app could freeze if an async request blocks on no connections available. If no connections are available, then an exception should be raised. Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: integrating SQLAlchemy in a WSGI based framework
Michael Bayer ha scritto: [...] Current integration approaches focus on the scoped_session() construct as home base for the current transaction. Ah, sorry. Sessions are not a problem, since the common pattern for web applications is to create a new session for each transaction (if the user does not like it, then it can use its how logic). My problem is with contextual connections. Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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] problem with SHOW command
Hi.
I'm having strange problems when I execute a SHOW command with PostgreSQL.
from sqlalchemy import create_engine, sql, __version__
print __version__
URL = 'postgres://xxx:[EMAIL PROTECTED]/xxx'
db = create_engine(URL, echo=True)
conn = db.connect()
query = sql.text('SHOW CLIENT_ENCODING')
r = conn.execute(query)
print r.scalar()
0.4.6
Traceback (most recent call last):
File postgres_show_bug.py, line 11, in module
print r.scalar()
File /var/lib/python-support/python2.5/sqlalchemy/engine/base.py,
line 1661, in scalar
self.connection._handle_dbapi_exception(e, None, None, self.cursor)
File /var/lib/python-support/python2.5/sqlalchemy/engine/base.py,
line 942, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.InterfaceError: (InterfaceError) cursor already
closed None None
What's the problem?
If I execute the query directly with psycopg2, there are no errors.
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: problem with SHOW command
Glauco ha scritto:
Manlio Perillo ha scritto:
Hi.
I'm having strange problems when I execute a SHOW command with PostgreSQL.
from sqlalchemy import create_engine, sql, __version__
print __version__
URL = 'postgres://xxx:[EMAIL PROTECTED]/xxx'
db = create_engine(URL, echo=True)
conn = db.connect()
query = sql.text('SHOW CLIENT_ENCODING')
r = conn.execute(query)
print r.scalar()
I've no problem:
0.3.10
2008-07-07 15:22:29,161 INFO sqlalchemy.engine.base.Engine.0x..d4 SHOW
CLIENT_ENCODING
2008-07-07 15:22:29,161 INFO sqlalchemy.engine.base.Engine.0x..d4 {}
UTF8
What version of SQLAlchemy/PostgreSQL/psycopg2 are you using?
I'm using (on Debian Lenny):
* SQLAlchemy 0.4.6-1
* PostgreSQL 8.3.3-1
* psycopg2 2.0.7-4
do you have the same problem with the fetchall()?
r = conn.execute( 'SHOW CLIENT_ENCODING' )
print r.fetchall()
It's the same.
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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] insert into table select from another_table
Hi. Does sqlalchemy supports the query in the subject? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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] foreign key support in SQLite
Hi. In this wiki page: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers there is a recipe on how to add basic foreign key support to SQLite using triggers. Is it possible to implement this recipe in SQLAlchemy? Some time ago I have tried to implement it, by adding support for triggers in SQLAlchemy (so that they can be automatically created/dropped) but I have abandoned the project because the internals of SQLAlchemy are unstable. Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: foreign key support in SQLite
Koen Bok ha scritto: Hey Manilo, My feeling is that this is out of the scope of the SQLAlchemy project. It should support the official workings for each database package, not extend it. Right, this should not be part of SQLAlchemy core, but maybe in a contrib package? Nobody else has written a small function for this easy job? It is pretty easy to implement this yourself and use SQLAlchemy on top of that (I use a lot of triggers in my app). There may be something to say for pythonizing triggers so they become database agnostic, but I am not even sure if that is possible given all the different triggers different databases can have. Not sure, but the generic syntax is quite standard. The only problem is the trigger action. What do you mean by 'unstable internals' in SQLAlchemy? Unstable internal API. Kindest regards, Koen Bok Manlio Perillo --~--~-~--~~~---~--~~ 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: foreign key support in SQLite
Michael Bayer ha scritto: On Jan 28, 2008, at 6:03 AM, Manlio Perillo wrote: Hi. In this wiki page: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers there is a recipe on how to add basic foreign key support to SQLite using triggers. Is it possible to implement this recipe in SQLAlchemy? Some time ago I have tried to implement it, by adding support for triggers in SQLAlchemy (so that they can be automatically created/dropped) but I have abandoned the project because the internals of SQLAlchemy are unstable. we have ticket 903 http://www.sqlalchemy.org/trac/ticket/903 with a suggested way we'll be accomplishing this, a generic DDL() construct that associates with table/metadata creates/drops. we just haven't decided on exact API details but comments are welcome. Thanks. I like the idea of custom events. However, instead of having: table.events['after-create'].append(run) IMHO it is better an higher level: table.add_event(phase, event_type, callable, *args, **kwargs) Where phase is create or drop and event_type is before or after. (http://twistedmatrix.com/trac/browser/trunk/twisted/internet/interfaces.py#L624) Manlio Perillo --~--~-~--~~~---~--~~ 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: Asynchronous SQLAlchemy
Mike Lewis ha scritto: Hi All, I'm starting a new project that will probably be using Twisted, but I want to use SQLAlchemy as well. I saw a couple of old posts about Asynchronous SQLAlchemy and two different implementations, but neither sAsync nor nadbapi seem to be maintained (or at least have new versions posted), and I don't think they'd be compatible with version 4.x of SQLAlchemy. Any pointers? Thanks, Mike Two months ago I have updated nadbapi to add support for SQLAlchemy 4.x. I have used the version from trunk. Since I still use 0.3.x, and I'm not following the development of SQLAlchemy, recent versions may not work. The first thing you can do is trying to run the unit tests. Let me know if they fail. Note that in the recent updates I have tried to minimize the dependencies from SQLAlchemy internals; moreover the code is very simple so it should be easy to fix any problems. A possible problem can be with compiled query execution. Regards Manlio Perillo --~--~-~--~~~---~--~~ 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 and postgresql warning messages
Michael Bayer ha scritto: On Nov 20, 2007, at 4:35 AM, Manlio Perillo wrote: I have asked on the psycopg2 list. psycopg2 connection has a notices attribute. try: conn = db.connect() metadata.create_all(bind=conn) print conn.connection.connection.notices finally: metadata.drop_all() ['NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b\n', 'WARNING: foreign key constraint b_id_fkey will require costly sequential scans\nDETAIL: Key columns id and id are of different types: text and integer.\n'] So SQLAlchemy can process it, if this is reasonable. hmmm, thats interesting. it would have to be placed at the execute level, but of course we are only going to issue warnings, not exceptions. I think this might also be better as an optional flag for the PG dialect. create a ticket in trac else I'll lose track of this one Done: #877. Manlio Perillo --~--~-~--~~~---~--~~ 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 and postgresql warning messages
Michael Bayer ha scritto: On Nov 16, 2007, at 1:54 PM, Manlio Perillo wrote: There is an error in the schema, b.id is of type String instead of type Integer. Unfortunately PostgreSQL does not raises an error, but just a warning. In fact I have found such a problem in one of my programs only after a pg_dump + pg_restore: WARNING: foreign key constraint b_id_fkey will require costly sequential scans DETAIL: Key columns id and id are of different types: text and integer. What's the best method to avoid these bugs? It would be nice to have something like `salint`, that can scan a metadata searching for problems. Also, it would help if PostgreSQL warnings messages can be reported by psycopg/SQLAlchemy. Better if warnings can be considered like errors, thus raising an exception (something like the Werror option in GCC). if the warning is issued by Psycopg2, you can turn warnings into exceptions using the warnings filter, http://www.python.org/doc/lib/warning-filter.html . If its not, then SA can't do anything about it, you'd have to post on the psycopg2 list for this behavior to be supported. I have asked on the psycopg2 list. psycopg2 connection has a notices attribute. try: conn = db.connect() metadata.create_all(bind=conn) print conn.connection.connection.notices finally: metadata.drop_all() ['NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b\n', 'WARNING: foreign key constraint b_id_fkey will require costly sequential scans\nDETAIL: Key columns id and id are of different types: text and integer.\n'] So SQLAlchemy can process it, if this is reasonable. Manlio Perillo --~--~-~--~~~---~--~~ 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 and postgresql warning messages
Hi.
I have found an insidious problem with SQLAlchemy and PostreSQL.
Here is an example:
from sqlalchemy import engine, schema, sql, types
metadata = schema.MetaData()
a = schema.Table(
'a', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('x', types.Integer),
)
b = schema.Table(
'b', metadata,
schema.Column('id', types.String, schema.ForeignKey(a.c.id),
primary_key=True),
schema.Column('y', types.Integer),
)
URL = 'postgres://twisted_test:[EMAIL PROTECTED]/twisted_test'
db = engine.create_engine(URL, echo=True)
metadata.bind = db
try:
metadata.create_all()
finally:
metadata.drop_all()
There is an error in the schema, b.id is of type String instead of type
Integer.
Unfortunately PostgreSQL does not raises an error, but just a warning.
In fact I have found such a problem in one of my programs only after a
pg_dump + pg_restore:
WARNING: foreign key constraint b_id_fkey will require costly
sequential scans
DETAIL: Key columns id and id are of different types: text and integer.
What's the best method to avoid these bugs?
It would be nice to have something like `salint`, that can scan a
metadata searching for problems.
Also, it would help if PostgreSQL warnings messages can be reported by
psycopg/SQLAlchemy.
Better if warnings can be considered like errors, thus raising an
exception (something like the Werror option in GCC).
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: problems with session transaction in SQLAlchemy 0.4
Michael Bayer ha scritto: On Nov 7, 2007, at 4:26 PM, Manlio Perillo wrote: I would like this interface to be public, so I can implement it for the Engine class in nadbapi. ok, its public, rev 3751. Great, thanks. What about _execute_compiled :-)? I have just implemented these two functions in nadbapi and it works. Manlio Perillo --~--~-~--~~~---~--~~ 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] problem with compiled queries in SA 0.4
Hi.
I have noted a change in SA 0.4 about compiled queries.
Here is a sample script (it requires PostgreSQL):
from sqlalchemy import engine, schema, sql, types
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('x', types.Integer),
)
URL = 'postgres://twisted_test:[EMAIL PROTECTED]/twisted_test'
db = engine.create_engine(URL)
metadata.bind = db
try:
metadata.create_all()
query = test.insert().compile()
r = db.execute(query, x=10)
print r.rowcount
finally:
metadata.drop_all()
I get an exception:
$python compiled.py
Traceback (most recent call last):
File compiled.py, line 24, in ?
r = db.execute(query, x=10)
File
/home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/engine/base.py,
line 1121, in execute
return connection.execute(statement, *multiparams, **params)
File
/home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/engine/base.py,
line 784, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/engine/base.py,
line 847, in _execute_compiled
self.__execute_raw(context)
File
/home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/engine/base.py,
line 859, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/engine/base.py,
line 875, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) null value in
column id violates not-null constraint
'INSERT INTO test (id, x) VALUES (%(id)s, %(x)s)' {'x': 10, 'id': None}
I'm posting the problem here since I'm not sure if this is a bug or a
feature.
If this is a bug, I will fill a ticket.
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: SQL execution order in the unit of work
Michael Bayer ha scritto: On Nov 9, 2007, at 4:23 PM, Manlio Perillo wrote: Michael Bayer ha scritto: Sorry, I forgot to add that the mappers A and B must have a relation() specified in order for unit of work to determine the order of operations. this has always been the case in all versions. illustrated in the attached script. But I have a piece a code that works with SQLAlchemy 0.3.6 and fails with never versions. The schema is at: http://hg.mperillo.ath.cx/nweb/nauth/file/68ab5b8e71f8/nauth/schema.py and the code is at: http://hg.mperillo.ath.cx/nweb/nauth/file/68ab5b8e71f8/test/test_nauth.py in the test suit named RealmTestCase (in the setUp method) column 248. Well thats a lot of code to read, Sorry. but I can see that your mappers have no relations set between them. Right. SQLAlchemy has never made any guarantees of insert order among two different tables that have no explicitly defined relation to each other at the mapper level; and in fact the test case I posted earlier still inserts into B before A even on 0.3.6. If your application save()s A and B, which have no relation() to each other, and it requires that A be inserted before B, you are relying on behavior that is currently undefined. If your app runs on 0.3.6 with linux, I bet it would fail on OSX which usually orders dictionaries differently than the same app would on linux. Ok, so the order is undefined. this is not to say it might be an *interesting* feature for mappers to take foreign keys on tables which have no corresponding relation() into account when flushing, although i think it might be somewhat surprising behavior and id be concerned about the feature adding unnecessary complexity. Isn't it possible to just use the order used by the programmer? If I call save(A) save(B) then the operations should be executed in this order. Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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] SQL execution order in the unit of work
Hi. It seems that from SQLAlchemy 0.3.7(?) the unit of work, after a flush, executes the SQL operations in a different order. As an example, assuming this schema CREATE TABLE A ( x INTEGER PRIMARY KEY ); CREATE TABLE B ( y INTEGER PRIMARY KEY REFERENCES A(x) ); in 0.36 I can execute, in a session transaction: a = A(x=10) sess.save(a) b = B(x=10) sess.save(b) sess.flush() This no longer works on 0.3.10, where I need to do a flush after `a` creation. Is this a feature? Is it possible to force the unit of work to execute queries in the right order, in order to avoid an intermediate flush? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
