Re: [sqlalchemy] Autocreate parent while inserting child
On Monday, June 20, 2011 07:34:42 PM Michael Bayer wrote:
> SQLA doesn't automatically "create" any objects so you'd need to create the
> Parent object yourself, but you'd also use relationship():
>
> class Child(Base):
> parent_name = Column(String, ForeignKey('parent.name'))
> parent = relationship(Parent)
>
> def __init__(self, name):
> self.parent = Parent(name)
>
>
> relationship() would handle the "parent_name" assignment as well as adding
> Parent to the Session.
I did that. But later on, when I had to insert a Child to an existing Parent,
it raised IntegrityErrors once again.
With some IRC help, I found a solution using the UniqueObject recipe
(http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject). I was about
to use that in my code, but then I saw that there's a method called
session.merge(), which apears to do the same thing.
Please correct me if I'm wrong.
--
Fayaz Yusuf Khan
Cloud developer and designer
Dexetra SS, Kochi, India
fayaz.yusuf.khan_AT_gmail_DOT_com
fayaz_AT_dexetra_DOT_com
+91-9746-830-823
signature.asc
Description: This is a digitally signed message part.
Re: [sqlalchemy] A question about the Hybrid Value pattern for encrypted values
On Jun 21, 2011, at 7:55 PM, Roy Hyunjin Han wrote: > Hi Michael, > > In the documentation you state that the Hybrid Value pattern is useful > for encrypted values. > http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html > Is it possible for you to provide an example of the Hybrid Value > pattern as applied to encrypted values? > > In my case, I want to enforce case-insensitive comparison for > encrypted email addresses. The email addresses are currently > encrypted model-side by overriding TypeDecorator to encrypt on the way > in and decrypt on the way out. > > I have tried applying a > column_property(comparator_factory=CaseInsensitiveComparator) but that > applies lower() to the encrypted value instead of the decrypted email > address. > > I thought the Hybrid Value pattern might apply in this case, but the > encrypt() and decrypt() functions I am using are strictly Python > without SQL equivalents. Is there a way to enforce case-insensitive > encrypted comparison from the model? The hybrid can be made to work for encryption very nicely if you use a comparator that coerces the value to its encrypted form before passing to the database. It's worth publishing so I created an example at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SymmetricEncryption . The issue the approach has however is if you're dealing with salted encryption and attempting to compare crypted values in the database - you can't encrypt on the Python side since each comparison would require the salt of each value to be compared. Ants' older recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/HashProperty proposes using DB side functions for this scenario. -- 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: A question about the Hybrid Value pattern for encrypted values
On Jun 21, 5:55 pm, Roy Hyunjin Han wrote: > In the documentation you state that the Hybrid Value pattern is useful > for encrypted values.http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html > Is it possible for you to provide an example of the Hybrid Value > pattern as applied to encrypted values? > > In my case, I want to enforce case-insensitive comparison for > encrypted email addresses. The email addresses are currently > encrypted model-side by overriding TypeDecorator to encrypt on the way > in and decrypt on the way out. > > I have tried applying a > column_property(comparator_factory=CaseInsensitiveComparator) but that > applies lower() to the encrypted value instead of the decrypted email > address. > > I thought the Hybrid Value pattern might apply in this case, but the > encrypt() and decrypt() functions I am using are strictly Python > without SQL equivalents. Is there a way to enforce case-insensitive > encrypted comparison from the model? To answer my own question, I am going to guess that the Hybrid Value pattern for encrypted values refers specifically to database-provided encryption functions such as pgcrypto. If the encryption functions are pure Python, I don't think the Hybrid Value pattern applies. -- 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] A question about the Hybrid Value pattern for encrypted values
Hi Michael, In the documentation you state that the Hybrid Value pattern is useful for encrypted values. http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Is it possible for you to provide an example of the Hybrid Value pattern as applied to encrypted values? In my case, I want to enforce case-insensitive comparison for encrypted email addresses. The email addresses are currently encrypted model-side by overriding TypeDecorator to encrypt on the way in and decrypt on the way out. I have tried applying a column_property(comparator_factory=CaseInsensitiveComparator) but that applies lower() to the encrypted value instead of the decrypted email address. I thought the Hybrid Value pattern might apply in this case, but the encrypt() and decrypt() functions I am using are strictly Python without SQL equivalents. Is there a way to enforce case-insensitive encrypted comparison from the model? Thanks, RHH -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Raising exceptions from TypeDecorator.process_bind_param()
On Jun 21, 2011, at 7:04 PM, Anton wrote: > On 22 июн, 00:47, Michael Bayer wrote: >> I added StatementError after having too often a custom type or other kind of >> error happen deep inside the preparation for execution with no indication >> what statement or parameter caused the issue.It's an enhancement, and >> the original exception is associated with the new one as "orig". In Python >> 3 this works even more nicely as you can see we're doing "raise x from e", >> and you get both stacktraces.What about the StatementError is not >> working for you ? I'd advise against doing things like business-level >> validations in types. > > In my case it's more like sanity check than business-logic. And the > reason to do this checks in type is that I want to delay it as much as > possible. It would be acceptable to have another exception instead of > custom, but the problem is that it is not easy to understand, what's > went wrong looking at the stack trace. It doesn't show at the last > line its original exception, only "StatementError: 'query > text' [params list]". well Python 3 fixes this problem entirely by allowing exception chains. What text would you prefer in StatementError ? > -- > Anton > >> >> On Jun 21, 2011, at 6:39 PM, an...@angri.ru wrote: >> >> >> >> >> >> >> >>> Hi, >> >>> I'm porting sqlamp [1] to SQLAlchemy 0.7 and have some difficulties >>> with it. One of them is raising an exception (non-DBAPI) from >>> process_bind_param() method of classes derived from TypeDecorator. In >>> 0.6 [2] method _handle_dbapi_exception() of engine.base.Connectable >>> doesn't munch the exception if it is not an instance of >>> self.dialect.dbapi.Error. 0.7 in turn does [3]. And it reraises the >>> exception of different type - instead of the original one it uses >>> DBAPIError.instance(), which in my case returns StatementError. How >>> can I fix it on my side? >> >>> Thanks. >> >>> -- >>> Anton >> >>> [1]http://sqlamp.angri.ru >>> [2]http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?... >>> [3]http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?... >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to sqlalchemy@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Raising exceptions from TypeDecorator.process_bind_param()
On 22 июн, 00:47, Michael Bayer wrote: > I added StatementError after having too often a custom type or other kind of > error happen deep inside the preparation for execution with no indication > what statement or parameter caused the issue. It's an enhancement, and the > original exception is associated with the new one as "orig". In Python 3 > this works even more nicely as you can see we're doing "raise x from e", and > you get both stacktraces. What about the StatementError is not working for > you ? I'd advise against doing things like business-level validations in > types. In my case it's more like sanity check than business-logic. And the reason to do this checks in type is that I want to delay it as much as possible. It would be acceptable to have another exception instead of custom, but the problem is that it is not easy to understand, what's went wrong looking at the stack trace. It doesn't show at the last line its original exception, only "StatementError: 'query text' [params list]". It seems confusing, because there are no statement error indeed. The stack trace itself is right though (ends with my "raise TooManyChildrenError()" line). -- Anton > > On Jun 21, 2011, at 6:39 PM, an...@angri.ru wrote: > > > > > > > > > Hi, > > > I'm porting sqlamp [1] to SQLAlchemy 0.7 and have some difficulties > > with it. One of them is raising an exception (non-DBAPI) from > > process_bind_param() method of classes derived from TypeDecorator. In > > 0.6 [2] method _handle_dbapi_exception() of engine.base.Connectable > > doesn't munch the exception if it is not an instance of > > self.dialect.dbapi.Error. 0.7 in turn does [3]. And it reraises the > > exception of different type - instead of the original one it uses > > DBAPIError.instance(), which in my case returns StatementError. How > > can I fix it on my side? > > > Thanks. > > > -- > > Anton > > > [1]http://sqlamp.angri.ru > > [2]http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?... > > [3]http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?... > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Raising exceptions from TypeDecorator.process_bind_param()
I added StatementError after having too often a custom type or other kind of error happen deep inside the preparation for execution with no indication what statement or parameter caused the issue.It's an enhancement, and the original exception is associated with the new one as "orig". In Python 3 this works even more nicely as you can see we're doing "raise x from e", and you get both stacktraces.What about the StatementError is not working for you ? I'd advise against doing things like business-level validations in types. On Jun 21, 2011, at 6:39 PM, an...@angri.ru wrote: > Hi, > > I'm porting sqlamp [1] to SQLAlchemy 0.7 and have some difficulties > with it. One of them is raising an exception (non-DBAPI) from > process_bind_param() method of classes derived from TypeDecorator. In > 0.6 [2] method _handle_dbapi_exception() of engine.base.Connectable > doesn't munch the exception if it is not an instance of > self.dialect.dbapi.Error. 0.7 in turn does [3]. And it reraises the > exception of different type - instead of the original one it uses > DBAPIError.instance(), which in my case returns StatementError. How > can I fix it on my side? > > Thanks. > > -- > Anton > > [1] http://sqlamp.angri.ru > [2] > http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?rev=7667%3A6bf675d91a56#L1321 > [3] > http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?rev=7665%3A145c143cef74#L1685 > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Raising exceptions from TypeDecorator.process_bind_param()
Hi, I'm porting sqlamp [1] to SQLAlchemy 0.7 and have some difficulties with it. One of them is raising an exception (non-DBAPI) from process_bind_param() method of classes derived from TypeDecorator. In 0.6 [2] method _handle_dbapi_exception() of engine.base.Connectable doesn't munch the exception if it is not an instance of self.dialect.dbapi.Error. 0.7 in turn does [3]. And it reraises the exception of different type - instead of the original one it uses DBAPIError.instance(), which in my case returns StatementError. How can I fix it on my side? Thanks. -- Anton [1] http://sqlamp.angri.ru [2] http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?rev=7667%3A6bf675d91a56#L1321 [3] http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/base.py?rev=7665%3A145c143cef74#L1685 -- 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] Feature request - indexes on arbitrary expressions with postgresql
On Jun 21, 2011, at 5:02 PM, Filip Zyzniewski - Tefnet wrote: > Dnia 2011-06-21, wto o godzinie 15:58 -0400, Michael Bayer pisze: >> also note you can do this stuff without a patch with the @compiles >> decorator, if you just need to get something done. you'd redefine >> compilation for CreateIndex. > > I think it fails before compiling (I have included a backtrace in the > first post). > > sqlalchemy.schema.Index is derived from > sqlalchemy.schema.ColumnCollectionMixin, whose column attribute > (sqlalchemy.sql.expression.ColumnCollection) seems to assume (not > surprisingly), that items added to it are columns. > > Should I create the index object in some other way? > Maybe my own index class? yeah you can subclass Index. but also I think the feature add is fine.To be committed it needs tests, as this is a generic feature it could probably go into test/sql/test_constraints.py using assert_compile() with CreateIndex. Or I'll eventually get around to it... -- 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] Feature request - indexes on arbitrary expressions with postgresql
Dnia 2011-06-21, wto o godzinie 15:58 -0400, Michael Bayer pisze: > also note you can do this stuff without a patch with the @compiles > decorator, if you just need to get something done. you'd redefine > compilation for CreateIndex. I think it fails before compiling (I have included a backtrace in the first post). sqlalchemy.schema.Index is derived from sqlalchemy.schema.ColumnCollectionMixin, whose column attribute (sqlalchemy.sql.expression.ColumnCollection) seems to assume (not surprisingly), that items added to it are columns. Should I create the index object in some other way? Maybe my own index class? regards, Filip Zyzniewski Tefnet -- 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] Feature request - indexes on arbitrary expressions with postgresql
also note you can do this stuff without a patch with the @compiles decorator, if you just need to get something done. you'd redefine compilation for CreateIndex. On Jun 21, 2011, at 3:55 PM, Michael Bayer wrote: > > On Jun 21, 2011, at 3:52 PM, Filip Zyzniewski - Tefnet wrote: > >> Dnia 2011-06-21, wto o godzinie 15:34 -0400, Michael Bayer pisze: >>> sure probably, just compile the expression through the dialect and its >>> fine >> >> Would you like me to try to come up with a patch for that? >> >> Do you think that column_property could have an optional 'index' keyword >> argument for this situation? > > mmmprobably not !column_property() is ORM level has nothing to do > with DDL > >> >> regards, >> Filip Zyzniewski >> Tefnet >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Feature request - indexes on arbitrary expressions with postgresql
On Jun 21, 2011, at 3:52 PM, Filip Zyzniewski - Tefnet wrote: > Dnia 2011-06-21, wto o godzinie 15:34 -0400, Michael Bayer pisze: >> sure probably, just compile the expression through the dialect and its >> fine > > Would you like me to try to come up with a patch for that? > > Do you think that column_property could have an optional 'index' keyword > argument for this situation? mmmprobably not !column_property() is ORM level has nothing to do with DDL > > regards, > Filip Zyzniewski > Tefnet > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Feature request - indexes on arbitrary expressions with postgresql
Dnia 2011-06-21, wto o godzinie 15:34 -0400, Michael Bayer pisze: > sure probably, just compile the expression through the dialect and its > fine Would you like me to try to come up with a patch for that? Do you think that column_property could have an optional 'index' keyword argument for this situation? regards, Filip Zyzniewski Tefnet -- 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] SQLAlchemy and DBFs
Hi,
Dnia 2011-06-21, wto o godzinie 07:25 -0700, David Marsh pisze:
> I'm trying to get SQAlchemy to connect to a collection of DBFs and I'm
> having some difficulty.
>
> If I use pyodbc, I can connect and extract data using the following
> connection string:
>
> 'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\\path\\to\
> \dbfs;'
>
> I'm having trouble translating this into something that sqlalchemy can
> use. All of the posts/docs I've seen seem to be connecting to another
> server, not reading DBFs out of a folder.
>
> Is there a way to do this in SQLAlchemy? Am I on the right track
> using pyodbc? Does SQLAlchemy have another method of connecting to
> DBFs that I haven't discovered yet?
>
> Thanks in advance
>
Maybe the way I do it could help you in some way - I use pgdbf
(http://pgdbf.sourceforge.net/) to build the table, and then
I run a script every minute which does:
BEGIN;
TRUNCATE TABLE sometable;
COMMIT;
It plows through ~ 70MB worth of DBF files within 7.5s on a lousy
server.
Then I run stuff I want on the postgresql database.
I think you may have trouble with running pgdbf under Windows - I run it
on Linux.
regards,
Filip Zyzniewski
Tefnet
--
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] Feature request - indexes on arbitrary expressions with postgresql
sure probably, just compile the expression through the dialect and its fine
On Jun 21, 2011, at 3:28 PM, Filip Zyzniewski - Tefnet wrote:
> Hi,
>
> I got an idea today and thought that maybe SQLAlchemy could support that
> (copy at http://ideone.com/t42G4 ):
> ==
> # Fails with Python-2.7.0 and SQLAlchemy-0.7.1
>
> import sqlalchemy
> import sqlalchemy.ext.declarative
>
> Base = sqlalchemy.ext.declarative.declarative_base(mapper =
> sqlalchemy.orm.mapper)
>
>
> engine =
> sqlalchemy.create_engine('postgres://tefbusiness_test:None@/tefbusiness_test_2',
> echo = False)
>
> session = sqlalchemy.orm.scoped_session(
> sqlalchemy.orm.sessionmaker(
> bind = engine,
> autocommit = False,
> )
> )
>
> Base.metadata.bind = engine
>
> class Person(Base):
> Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key = True,
> autoincrement = True)
> __tablename__ = 'person'
> firstName = sqlalchemy.Column( sqlalchemy.types.String(128), nullable =
> False)
> lastName = sqlalchemy.Column( sqlalchemy.types.String(128), nullable =
> False)
> name = sqlalchemy.orm.column_property(
> sqlalchemy.func.lower(firstName + ' ' + lastName)
> )
>
> __table_args__ = (sqlalchemy.schema.Index('foo', name.columns[0]), {})
>
> """
> Traceback (most recent call last):
> File "column_property_2.py", line 20, in
> class Person(Base):
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
> line 1128, in __init__
> _as_declarative(cls, classname, cls.__dict__)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
> line 1027, in _as_declarative
> **table_kw)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
> line 265, in __new__
> table._init(name, metadata, *args, **kw)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
> line 340, in _init
> self._init_items(*args)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
> line 64, in _init_items
> item._set_parent_with_dispatch(self)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/events.py",
> line 227, in _set_parent_with_dispatch
> self._set_parent(parent)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
> line 2112, in _set_parent
> ColumnCollectionMixin._set_parent(self, table)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
> line 1808, in _set_parent
> self.columns.add(col)
> File
> "/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/sql/expression.py",
> line 2115, in add
> self[column.key] = column
> AttributeError: 'Function' object has no attribute 'key'
> """
> ==
>
> If Index() could accept clauses as columns, then maybe we could get something
> like:
> name = sqlalchemy.orm.column_property(
> sqlalchemy.func.lower(firstName + ' ' + lastName),
> index = True
> )
>
> Most of the needed pieces are already in place.
> PostgreSQL can do stuff like this:
> ==
> test=# CREATE INDEX ix_foo ON "teryt_Street" (lower(prename || ' ' ||
> postname));
> CREATE INDEX
> test=#
> ==
>
> What do you think :)?
>
> regards,
> Filip Zyzniewski
> Tefnet
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Feature request - indexes on arbitrary expressions with postgresql
Hi,
I got an idea today and thought that maybe SQLAlchemy could support that
(copy at http://ideone.com/t42G4 ):
==
# Fails with Python-2.7.0 and SQLAlchemy-0.7.1
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base(mapper =
sqlalchemy.orm.mapper)
engine =
sqlalchemy.create_engine('postgres://tefbusiness_test:None@/tefbusiness_test_2',
echo = False)
session = sqlalchemy.orm.scoped_session(
sqlalchemy.orm.sessionmaker(
bind = engine,
autocommit = False,
)
)
Base.metadata.bind = engine
class Person(Base):
Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key =
True, autoincrement = True)
__tablename__ = 'person'
firstName = sqlalchemy.Column( sqlalchemy.types.String(128),
nullable = False)
lastName = sqlalchemy.Column( sqlalchemy.types.String(128), nullable
= False)
name = sqlalchemy.orm.column_property(
sqlalchemy.func.lower(firstName + ' ' + lastName)
)
__table_args__ = (sqlalchemy.schema.Index('foo', name.columns[0]),
{})
"""
Traceback (most recent call last):
File "column_property_2.py", line 20, in
class Person(Base):
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
line 1128, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/ext/declarative.py",
line 1027, in _as_declarative
**table_kw)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
line 265, in __new__
table._init(name, metadata, *args, **kw)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
line 340, in _init
self._init_items(*args)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
line 64, in _init_items
item._set_parent_with_dispatch(self)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/events.py",
line 227, in _set_parent_with_dispatch
self._set_parent(parent)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
line 2112, in _set_parent
ColumnCollectionMixin._set_parent(self, table)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/schema.py",
line 1808, in _set_parent
self.columns.add(col)
File
"/home/filip/gitsrc/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.1-py2.7.egg/sqlalchemy/sql/expression.py",
line 2115, in add
self[column.key] = column
AttributeError: 'Function' object has no attribute 'key'
"""
==
If Index() could accept clauses as columns, then maybe we could get
something like:
name = sqlalchemy.orm.column_property(
sqlalchemy.func.lower(firstName + ' ' + lastName),
index = True
)
Most of the needed pieces are already in place.
PostgreSQL can do stuff like this:
==
test=# CREATE INDEX ix_foo ON "teryt_Street" (lower(prename || ' ' ||
postname));
CREATE INDEX
test=#
==
What do you think :)?
regards,
Filip Zyzniewski
Tefnet
--
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: SQLAlchemy and DBFs
Thanks for the quick reply.
A "generic" dialect like the one you mentioned would satisfy all of
the use cases I personally run into when using DBFs. Having one
available would certainly be handy but using the other available
modules is no big deal right now. The database aspect will be hidden
from the rest of the code anyway, so upgrading in the future shouldn't
be a big deal.
I hadn't considered converting it to sqlite, I'll definitely look into
that one.
Thanks again for the help.
On Jun 21, 10:53 am, Michael Bayer wrote:
> On Jun 21, 2011, at 10:25 AM, David Marsh wrote:
>
>
>
>
>
>
>
>
>
> > I'm trying to get SQAlchemy to connect to a collection of DBFs and I'm
> > having some difficulty.
>
> > If I use pyodbc, I can connect and extract data using the following
> > connection string:
>
> > 'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\\path\\to\
> > \dbfs;'
>
> > I'm having trouble translating this into something that sqlalchemy can
> > use. All of the posts/docs I've seen seem to be connecting to another
> > server, not reading DBFs out of a folder.
>
> > Is there a way to do this in SQLAlchemy? Am I on the right track
> > using pyodbc? Does SQLAlchemy have another method of connecting to
> > DBFs that I haven't discovered yet?
>
> SQLAlchemy doesn't have a dialect for DBF files, so while you could make an
> engine that connects to such a connection, its not clear what SQLAlchemy
> would do with this database once connected - it would need to act very, very
> similarly to Microsoft SQL Server or at least Access. The Access dialect
> hasn't been upgraded since 0.5 however, so we might not have a working
> solution here without someone willing to work on a dialect.
>
> It's probably possible to build a "generic" pyodbc dialect for use cases like
> these (assuming you want to do just SELECTs), but there's still some critical
> areas, namely primary key generation, that would probably have to remain
> undefined for such a dialect, so it would only have a limited, mostly
> read-only set of functionality.
>
> An entirely different way to do this is to convert your DBF file to SQLite -
> once in SQLite you'd be master of the universe. Here's a thread i found
> which has some various ways of doing it (unfortunately doesn't seem like
> there's a comprehensive tool yet):
>
> http://www.velocityreviews.com/forums/t691275-convert-dbase-dbf-files...
--
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] Read-Only Database
Hey, I have been using the approaches described here: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg21801.html Cheers Sebastian On 06/21/2011 05:06 PM, Mark Erbaugh wrote: My program accesses a sqlite database. It only extracts data from the database, it never writes anything to it. It can also be assumed that the database is not updated by other processes. In reality, the database is completely replaced periodically by a new version, but the program can be shut down and re-started whenever that happens. Is there a way to tell SQLAlchemy that the database is read-only, and would that simplify the work that SA does behind the scenes? Thanks, Mark -- Sebastian Elsner - Pipeline TD - r i s e | fx t: +49 30 201 803 00 sebast...@risefx.com c: +49 175 336 5739 7548 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28, Aufgang B 10997 Berlin Richard-Byrd-Strasse 12, 50829 Cologne Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] Read-Only Database
My program accesses a sqlite database. It only extracts data from the database, it never writes anything to it. It can also be assumed that the database is not updated by other processes. In reality, the database is completely replaced periodically by a new version, but the program can be shut down and re-started whenever that happens. Is there a way to tell SQLAlchemy that the database is read-only, and would that simplify the work that SA does behind the scenes? Thanks, Mark -- 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] SQLAlchemy and DBFs
On Jun 21, 2011, at 10:25 AM, David Marsh wrote:
> I'm trying to get SQAlchemy to connect to a collection of DBFs and I'm
> having some difficulty.
>
> If I use pyodbc, I can connect and extract data using the following
> connection string:
>
> 'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\\path\\to\
> \dbfs;'
>
> I'm having trouble translating this into something that sqlalchemy can
> use. All of the posts/docs I've seen seem to be connecting to another
> server, not reading DBFs out of a folder.
>
> Is there a way to do this in SQLAlchemy? Am I on the right track
> using pyodbc? Does SQLAlchemy have another method of connecting to
> DBFs that I haven't discovered yet?
SQLAlchemy doesn't have a dialect for DBF files, so while you could make an
engine that connects to such a connection, its not clear what SQLAlchemy would
do with this database once connected - it would need to act very, very
similarly to Microsoft SQL Server or at least Access. The Access dialect
hasn't been upgraded since 0.5 however, so we might not have a working solution
here without someone willing to work on a dialect.
It's probably possible to build a "generic" pyodbc dialect for use cases like
these (assuming you want to do just SELECTs), but there's still some critical
areas, namely primary key generation, that would probably have to remain
undefined for such a dialect, so it would only have a limited, mostly read-only
set of functionality.
An entirely different way to do this is to convert your DBF file to SQLite -
once in SQLite you'd be master of the universe. Here's a thread i found which
has some various ways of doing it (unfortunately doesn't seem like there's a
comprehensive tool yet):
http://www.velocityreviews.com/forums/t691275-convert-dbase-dbf-files-to-sqlite-databases.html
--
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] SQLAlchemy and DBFs
I'm trying to get SQAlchemy to connect to a collection of DBFs and I'm
having some difficulty.
If I use pyodbc, I can connect and extract data using the following
connection string:
'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\\path\\to\
\dbfs;'
I'm having trouble translating this into something that sqlalchemy can
use. All of the posts/docs I've seen seem to be connecting to another
server, not reading DBFs out of a folder.
Is there a way to do this in SQLAlchemy? Am I on the right track
using pyodbc? Does SQLAlchemy have another method of connecting to
DBFs that I haven't discovered yet?
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to 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] Can't make SQLAlchemy return unicode in results of raw SQLs
On Jun 21, 2011, at 6:07 AM, Adam Bielański wrote:
> Hello.
>
> I'm using Python 2.6, SQLAlchemy 0.7.1, MySQL 5.5 and MySQLdb 1.2.3.
>
> I'm creating engine with following connect string:
> mysql+mysqldb://localhost/test_1?charset=utf8&sql_mode=STRICT_ALL_TABLES' and
> convert_unicode parameter set to True.
>
> Now when I call connection.execute(sql) I'm receiving utf-8 encoded strings
> for each entry from VARCHAR column. The same thing happens when I call
> query(myModel).all(). From what I can see in SA docs I should've receive
> unicode objects instead.
>
> When I change my connect string by adding use_unicode=0, apparently SA starts
> to convert rows returned by MySQLdb and query(myModel).all() returns unicode
> objects, not strings. But still connection.execute(sql) returns strings
> encoded with utf-8.
>
> I suppose that MySQLdb fails to convert strings to unicode, and when I
> explicitly mark that I don't want such conversion with use_unicode=0, SA adds
> processor to each VARCHAR column but it works only when I use ORM constructs.
> When I try executing raw SQL queries SA doesn't do any type conversion.
>
> Now is this expected behaviour, or is SA supposed to convert all string
> output to unicode when convert_unicode is set to True, even when it is result
> of plain SQL execution?
>
> Does anyone know how to fix it - should I set some additional flag in MySQLdb
> or in SA when I create engine or connection?
connection.execute("some sql string") does not perform any result set
processing - you get back pretty much exactly what MySQLdb would give you. If
you set the MySQLdb use_unicode flag to 1, it should start returning Python
unicode objects natively. In the past we've recommended against this flag as
MySQLdb had a very large and obvious memory leak when it was used, but its very
likely this is no longer the case.
A simple test confirms the unicode behavior:
from sqlalchemy import *
e = create_engine('mysql://scott:tiger@localhost/test?use_unicode=1',
echo='debug')
print repr(e.execute("select 'hi'").scalar())
assert e.dialect.returns_unicode_strings
e = create_engine('mysql://scott:tiger@localhost/test?use_unicode=0',
echo='debug')
print repr(e.execute("select 'hi'").scalar())
assert not e.dialect.returns_unicode_strings
Only when types are used, such as String or Unicode, does SQLAlchemy have the
instruction to coerce to unicode. String will coerce to unicode if the
convert_unicode=True flag is set. Unicode does it unconditionally.
Note the "returns_unicode_strings" dialect flag - true in one case, false in
the other. The engine does a test on first connect to see if the DBAPI is
giving us unicode. If so, the String and Unicode types from that point on will
do nothing, assuming the DBAPI is doing it for us.
To get SQLAlchemy to do a unicode coercion on a raw SQL string when the DBAPI
is not doing it, use the text() construct:
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text
>
> Regards,
>Adam Bielański.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Can't make SQLAlchemy return unicode in results of raw SQLs
Hello. I'm using Python 2.6, SQLAlchemy 0.7.1, MySQL 5.5 and MySQLdb 1.2.3. I'm creating engine with following connect string: mysql+mysqldb://localhost/test_1?charset=utf8&sql_mode=STRICT_ALL_TABLES' and convert_unicode parameter set to True. Now when I call connection.execute(sql) I'm receiving utf-8 encoded strings for each entry from VARCHAR column. The same thing happens when I call query(myModel).all(). From what I can see in SA docs I should've receive unicode objects instead. When I change my connect string by adding use_unicode=0, apparently SA starts to convert rows returned by MySQLdb and query(myModel).all() returns unicode objects, not strings. But still connection.execute(sql) returns strings encoded with utf-8. I suppose that MySQLdb fails to convert strings to unicode, and when I explicitly mark that I don't want such conversion with use_unicode=0, SA adds processor to each VARCHAR column but it works only when I use ORM constructs. When I try executing raw SQL queries SA doesn't do any type conversion. Now is this expected behaviour, or is SA supposed to convert all string output to unicode when convert_unicode is set to True, even when it is result of plain SQL execution? Does anyone know how to fix it - should I set some additional flag in MySQLdb or in SA when I create engine or connection? Regards, Adam Bielański. -- 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] intersect_all vs chaining of filter
What is the best practice: to chain filters or to collect queries in a list and then apply intersect_all()? Thanks -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] dynamically set table_name at runtime
> -Original Message-
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
> On Behalf Of Cody Django
> Sent: 20 June 2011 19:37
> To: sqlalchemy
> Subject: [sqlalchemy] dynamically set table_name at runtime
>
> Hello!
>
> I would like to dynamically set/change the table that is mapped in my
> python object, as instantiated through the declarative style.
>
>
> class Feature(Base, GeometryTableMixIn):
> """ this is dynamically created to use a table and pk_column
> determined at runtime """
>
> __table_args__ = {
> "schema": 'a_schema',
> "autoload": True,
> "autoload_with": Session.bind,
> "useexisting": True
> }
>
> wkb_geometry = GeometryColumn('wkb_geometry', Geometry(srid=4269))
>
> def __init__(self, *args, **kwargs):
> self.__tablename__ = kwargs['tablename']
> self.pk_id = Column('%s' % kwargs['pk_id'], types.Integer,
> primary_key=True, autoincrement=False)
> super(Feature, self).__init__(*args, **kwargs)
>
>
>
>
> This doesn't work:
>
> InvalidRequestError: Class
> does not have a __table__ or __tablename__ specified and does not
> inherit from an existing table-mapped class.
>
>
>
> Could this possibly be done through another approach? Suggestions
> are
> greatly appreciated.
>
Can you describe your use case? The solution presented in the
StackOverflow article seems like a hack at best. A cleaner way to do the
same thing might be:
def make_feature_class(tablename):
class Feature(Base, GeometryTableMixIn):
__table__ = tablename
# etc.
return Feature
...but the whole thing feels strange. What are you actually trying to
do?
Cheers,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
