[sqlalchemy] Re: sqlite unicode/datetime issue in 0.4
Is there a different way to do this that's compatible with both versions, or is there a new 0.4 way of doing this, or have I just been doing something wrong all along and only 0.4 is catching it? DateTime objects in sqlite currently expect a datetime.datetime object, and not a string. previous versions accepted both. a lot of people are getting this issue so i think im going to see what psycopg2 and mysqldb do, and emulate them. historically ive been against hardcoding a particular string format. What's odd here is that I'm not setting the value myself -- it's getting set in pre_exec by the column default, which (I believe) is running the func in sqlite and returning the results, which I'd think should wind up with a datetime.datetime. I'll try to put together a minimal test case that shows what's happening. JP --~--~-~--~~~---~--~~ 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: sqlite unicode/datetime issue in 0.4
Yup, that fixed things. Thanks! On Aug 16, 10:49 am, Michael Bayer [EMAIL PROTECTED] wrote: ok thats in r3322 --~--~-~--~~~---~--~~ 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] sqlite unicode/datetime issue in 0.4
I'm (finally) working on 0.4 compatibility in a couple of small
applications at work. One issue that is cropping up is that when using
sqlite, columns that are defined like this:
Column('established', TIMESTAMP(timezone=True), nullable=False,
default=func.current_timestamp(type=TIMESTAMP)),
which work correctly in 0.3.10, in 0.4 cause this error:
File ./sqlalchemy_trunk/lib/sqlalchemy/engine/default.py, line
245, in pre_execution
self.pre_exec()
File ./sqlalchemy_trunk/lib/sqlalchemy/engine/default.py, line
258, in pre_exec
self.parameters =
self.__convert_compiled_params(self.compiled_parameters)
File ./sqlalchemy_trunk/lib/sqlalchemy/engine/default.py, line
228, in __convert_compiled_params
parameters = parameters.get_raw_list(processors)
File ./sqlalchemy_trunk/lib/sqlalchemy/sql.py, line 867, in
get_raw_list
return [
File ./sqlalchemy_trunk/lib/sqlalchemy/databases/sqlite.py, line
41, in process
return value.strftime(self.__format__)
AttributeError: 'unicode' object has no attribute 'strftime'
Is there a different way to do this that's compatible with both
versions, or is there a new 0.4 way of doing this, or have I just been
doing something wrong all along and only 0.4 is catching it?
JP
--~--~-~--~~~---~--~~
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: a renaming proposal
So here's what I was thinking:
datasource = create_datasource('posgresql:///test')
connection = datasource.connect()
+0 -- I think it's moderately better than engine but for me, not
enough to want to change, though I wouldn't mind seeing it changed.
catalog = Catalog()
Table('foo', catalog, autoload=True)
catalog.bind = datasource
catalog.bind = 'sqlite:///'
+1
Catalog is, I think, the name we were all trying to come up with back
when metadata first appeared. Really makes it clear what the function
of the object is. Very nice.
JP
--~--~-~--~~~---~--~~
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: DynamicMetaData question
can I have some feedback from the list who is using the thread-local capability of DynamicMetaData, and/or are using global_connect() ? (another feature i didnt like but it was me bowing to early pressure from TG users). Probably this will be no surprise, since I contributed most of the original ProxyEngine. ;) I use DynamicMetaData exactly as you described the main pylons/TG use case: I define tables at the module level, using a DynamicMetaData instance, and then call meta.connect(uri) at the start of each request. For me, this is the most sensible way to handle things in a WSGI-friendly web app. Minimal overhead, maximal flexibility. meta.connect(uri, threadlocal=True) or meta.connect_threadlocal(uri) would be ok, but I think worse as an API since they put too much responsibility on the caller and cause too much repetition. JP --~--~-~--~~~---~--~~ 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: fetchmany() vs sqlite
I filed a ticket with a patch: http://www.sqlalchemy.org/trac/ticket/505 JP On Mar 7, 11:17 am, Michael Bayer [EMAIL PROTECTED] wrote: OK its looking like having it as positional might just be what they all commonly had in mind... On Mar 7, 2007, at 5:15 AM, Tim Golden wrote: On Mar 6, 11:37 pm, Michael Bayer [EMAIL PROTECTED] wrote: OK who can try Mysql, MS-SQL, Oracle, Firebird for me ? while it seems like it should work positionally for all of them, it sort of seems it should work keyword-wise as well if the DBAPI authors actually read the spec they were developing for. MSSQL with pyodbc gives: Traceback (most recent call last): File stdin, line 1, in ? File c:\work_in_progress\sqlalchemy\lib\sqlalchemy\engine\base.py, line 982, in fetchmany rows = self.cursor.fetchmany(size=size) TypeError: fetchmany() takes no keyword arguments MSSQL with adodbapi works ok MSSQL with pymssql works ok The pyodbc cursor.fetchmany *does* allow the rowcount as a positional argument: code from sqlalchemy import * db = create_engine (MSSQL://VODEV1/DEV) q = db.raw_connection ().cursor () q.execute (SELECT * FROM wb_parcels) q.fetchmany (2) # [pyodbc.Row object at 0x00A7A728, pyodbc.Row object at 0x00A7A368] /code TJG --~--~-~--~~~---~--~~ 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: psycopg2 default transaction inefficiency
Yeah, I think I explained badly.
What I was trying to show with the sqlalchemy vs postgres query logs
is that extra BEGIN that psycopg2 is sending before the SELECT that
sqlalchemy executes. The BEGIN is implicitly added by psycopg2 because
it's in its default transaction isolation state of read comitted.
Which I didn't know was the DBAPI default -- so strike what I said
about changing any default behavior.
But It would be nice to be able to have total control over the
transactional state of the connection, so that when I know that I'm
just doing a select or two I don't have to have the overhead of a
BEGIN that I know is useless, but as things stand I can't do that,
because do_begin isn't implemented for any of the dialects. I guess
that's because sqlalchemy is depending on the underlying DBAPI
implementation to handle sending BEGIN statements before the first
query that is outside of a transaction?
I noticed a TODO about implementing true autocommit behavior, which is
what I need -- a way to tell the dbapi module *not* to start those
implicit transactions. If the engine/connection could be put into true
autocommit mode, and issue connection.begin() from do_begin when in
that mode, then I could do everything I want to do and I don't think
anything would break, since the orm calls do_begin before each flush.
I know how to change the isolation level in the sqlite and postgres
dialects -- would you be interested in a patch or work on a branch
where I added an isolation_level property to the engine, implemented
it for those two, and for those two dialects had do_begin issue a
connection.begin() when the isolation_level was set to autocommit? I
could probably tackle mysql as well, but I have no access to mssql or
oracle, so I can't do those.
Hopefully I'm making some more sense this time...
JP
On Feb 12, 12:42 am, Michael Bayer [EMAIL PROTECTED] wrote:
im confused. the django thread seems to be saying that it is setting
psycopg2 *into* autocommit mode, which causes the problem; the default
setting of autocommit for DBAPI (and maintained in SA) is False. When
i wrote frameworks years ago I always thought autocommit mode was
the way to go but i since learned that issues like this arise so SA
*never* uses autocommit mode on the connection; instead it implements
its own autocommitting behavior in a manner similar to hibernate -
it looks at the statement being executed, determines if its a CRUD/
CREATE/DROP expression, and then explicitly calls COMMIT if no SA
transaction is already in progress.
the log you have above doesnt prove that anything unusual is going on,
since you are illustrating a transactional operation, then a SELECT,
then another transactional operation, then another SELECT. SA does an
explicit COMMIT for the CREATE statements since they are required to
complete the table creation operation.
this test confirms that psycopg2 defaults to autocommit as false and
doesnt do anything with transaction modes after the connection is
opened:
import psycopg2 as psycopg
conn = psycopg.connect(user='scott', password='tiger',
host='127.0.0.1', database='test')
for x in range(0, 5):
curs = conn.cursor()
curs.execute(SELECT 1)
curs.close()
log output:
LOG: statement: SET DATESTYLE TO 'ISO'
LOG: statement: SHOW client_encoding
LOG: statement: SHOW default_transaction_isolation
LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: statement: SELECT 1
LOG: statement: SELECT 1
LOG: statement: SELECT 1
LOG: statement: SELECT 1
LOG: statement: SELECT 1
LOG: statement: ABORT
one connection, five new cursors, only one set of setup
corresponding to the connection.
On Feb 9, 3:52 pm, JP [EMAIL PROTECTED] wrote:
I noticed this thread on django-devs:
http://groups.google.com/group/django-developers/browse_frm/thread/52...
Which notes that psycopg2 by default starts transactions behind the
scenes, even for select statements. If you happen to be running a web
app where each hit starts a new cursor, and you only do a few selects,
this adds a *lot* of overhead relative to autocommit.
I wondered if sqlalchemy suffered from the same problem with psycopg2,
and it does. Take a look at what sqlalchemy thinks it was doing for a
short example, vs what the postgres query log contains.
The code:
import sqlalchemy as sa
meta = sa.DynamicMetaData()
users = sa.Table('users', meta, sa.Column('id', sa.Integer,
primary_key=True, autoincrement=True), sa.Column('name', sa.String(50)))
meta.connect('postgres://snip', debug=True)
meta.create_all()
users.select().execute()
sqlalchemy query log:
select relname from pg_class where lower(relname) = %(name)s
CREATE TABLE users (
id SERIAL NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
)
COMMIT
SELECT users.id, users.name FROM users
postgres query log:
LOG: statement: SET DATESTYLE TO 'ISO'
LOG
[sqlalchemy] Re: psycopg2 default transaction inefficiency
like the example illustrates, there is no BEGIN being issued for every SELECT statement when using psycopg2 in non-autocommit mode, which applies to SQLAlchemy as well. therefore there is no performance bottleneck; this is a django issue only. I guess we're reading the example differently. I see this BEGIN: LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOG: statement: SELECT users.id, users.name FROM users in the postgres log, but as far as I can tell sqlalchemy is not sending it; it's being implicitly inserted by psycopg2 because the isolation level of the connection is read-committed and it sees a statement outside of a transaction. If the connection were in the autocommit isolation level, doing the same thing in sqlalchemy would not result in that BEGIN, only the SELECT would be issued. I noticed a TODO about implementing true autocommit behavior, which is what I need -- a way to tell the dbapi module *not* to start those implicit transactions. it doesnt. run the example - no implicit transaction beyond the initial connection (which is required, since it must implement rollback() and commit()). Either I'm very confused or I'm having lots of trouble getting my point across. In my experience, it is absolutely the case that the dbapi drivers are starting implicit transactions when they aren't in autocommit mode -- otherwise I don't see how any transactions would work correctly, since do_begin is a no-op everywhere -- so if the dbapi modules aren't inserting the BEGIN to start transactions, I don't see where it could be coming from. What am I missing? If I'm wrong about what's going on here, how does sqlalchemy start transactions when it needs to, and where is that BEGIN before the select in my example coming from? I have an example that I think illustrates what I'm talking about pretty well, so I'm going to file a ticket and attach the example and the postgres query log I see when I run it. Hopefully that will clear things up one way or another. JP --~--~-~--~~~---~--~~ 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] psycopg2 default transaction inefficiency
I noticed this thread on django-devs:
http://groups.google.com/group/django-developers/browse_frm/thread/521a03a726d526e1/b1bacc5628341129?lnk=gstq=psycopg2rnum=1#b1bacc5628341129
Which notes that psycopg2 by default starts transactions behind the
scenes, even for select statements. If you happen to be running a web
app where each hit starts a new cursor, and you only do a few selects,
this adds a *lot* of overhead relative to autocommit.
I wondered if sqlalchemy suffered from the same problem with psycopg2,
and it does. Take a look at what sqlalchemy thinks it was doing for a
short example, vs what the postgres query log contains.
The code:
import sqlalchemy as sa
meta = sa.DynamicMetaData()
users = sa.Table('users', meta, sa.Column('id', sa.Integer,
primary_key=True, autoincrement=True), sa.Column('name', sa.String(50)))
meta.connect('postgres://snip', debug=True)
meta.create_all()
users.select().execute()
sqlalchemy query log:
select relname from pg_class where lower(relname) = %(name)s
CREATE TABLE users (
id SERIAL NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
)
COMMIT
SELECT users.id, users.name FROM users
postgres query log:
LOG: statement: SET DATESTYLE TO 'ISO'
LOG: statement: SHOW client_encoding
LOG: statement: SHOW default_transaction_isolation
LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: statement: select relname from pg_class where lower(relname) =
'users'
LOG: statement:
CREATE TABLE users (
id SERIAL NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id)
)
LOG: statement: END
LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: statement: SELECT users.id, users.name FROM users
I think it would be better for sqlalchemy to set psycopg2 to
autocommit by default, and implement do_begin in the dialect so that
transactions are only started when desired, rather than implicitly on
the first statment seen by a cursor when there's no current
transaction, as seems to be the case now.
Mike, would you be interested in a patch that implemented that
behavior? Does anyone disagree that that would be better?
JP
--~--~-~--~~~---~--~~
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: SA 0.3.4 released
I cant keep up with all thats going on at this point, and the trunk
was just burstingso I need to get out what we have thus far and
see how far it flies.
Not very far around here, I'm afraid. Just updated and ran my test
suite (which passes with 0.3.3) and I'm getting batches of errors like:
File (redacted), line 162, in test_offer_interfaces
offer_resp.options.append(offer_opt)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 418, in append
self._data_appender(item)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 390, in __getattr__
return getattr(self.data, attr)
File /home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/query.py,
line 301, in __getattr__
raise AttributeError(key)
AttributeError: _data_appender
The relation is a normal one-many, set up using assign_mapper on the
many side:
assign_mapper(CTX, OfferOption, offer_option, properties={
'offer': relation(OfferResponse, backref='options')
})
I've also seen errors with inherited mappers seeming to no longer
populate foreign keys properly on flush, but those are a little harder
to boil down to a simple, non-NDS-violating example.
Should I file a ticket for the _data_appender error? What else can I do
to help track it down?
JP
--~--~-~--~~~---~--~~
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: SA 0.3.4 released
I filed #438 for the _data_appender thing:
http://www.sqlalchemy.org/trac/ticket/438.
For the other one, I'll try to put together a simple test case outside
of the code that belongs to my employer. I'll also see what I can do
about setting up a buildbot in our system to test our stuff against SA
trunk. Both of those are likely to take a few days, time is a bit tight
this week.
JP
On Jan 24, 11:41 am, Michael Bayer [EMAIL PROTECTED] wrote:
see, i wish folks would use the trunk more. yeah, file tickets, and
every bug fixed becomes a unit test. this one seems like something
trivial. the inheritance thing, try to track that down...we have *so
many* inheritance tests at this point i cant believe theres still
basic issues coming up.
On Jan 24, 2007, at 11:26 AM, JP wrote:
I cant keep up with all thats going on at this point, and the trunk
was just burstingso I need to get out what we have thus far and
see how far it flies.
Not very far around here, I'm afraid. Just updated and ran my test
suite (which passes with 0.3.3) and I'm getting batches of errors
like:
File (redacted), line 162, in test_offer_interfaces
offer_resp.options.append(offer_opt)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 418, in append
self._data_appender(item)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 390, in __getattr__
return getattr(self.data, attr)
File /home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/query.py,
line 301, in __getattr__
raise AttributeError(key)
AttributeError: _data_appender
The relation is a normal one-many, set up using assign_mapper on the
many side:
assign_mapper(CTX, OfferOption, offer_option, properties={
'offer': relation(OfferResponse, backref='options')
})
I've also seen errors with inherited mappers seeming to no longer
populate foreign keys properly on flush, but those are a little harder
to boil down to a simple, non-NDS-violating example.
Should I file a ticket for the _data_appender error? What else can
I do
to help track it down?
JP
--~--~-~--~~~---~--~~
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: SA 0.3.4 released
I added some info from pdb and from the patch you attached to the
ticket. Both agree that for some reason, the attribute is a Query
instance instead of an InstrumentedList instance, which is more than a
little confusing. I'm trying to come up with a simplified test case,
but so far I haven't been able to reproduce the error outside of the
full context.
JP
On Jan 24, 12:44 pm, Michael Bayer [EMAIL PROTECTED] wrote:
OK we have a lot of tests that pass with assign_mapper...can you give
me a little more of a test case for 438 ? its a weird error since
not much has changed with attributes...
On Jan 24, 2007, at 12:12 PM, JP wrote:
I filed #438 for the _data_appender thing:
http://www.sqlalchemy.org/trac/ticket/438.
For the other one, I'll try to put together a simple test case outside
of the code that belongs to my employer. I'll also see what I can do
about setting up a buildbot in our system to test our stuff against SA
trunk. Both of those are likely to take a few days, time is a bit
tight
this week.
JP
On Jan 24, 11:41 am, Michael Bayer [EMAIL PROTECTED] wrote:
see, i wish folks would use the trunk more. yeah, file tickets, and
every bug fixed becomes a unit test. this one seems like something
trivial. the inheritance thing, try to track that down...we have *so
many* inheritance tests at this point i cant believe theres still
basic issues coming up.
On Jan 24, 2007, at 11:26 AM, JP wrote:
I cant keep up with all thats going on at this point, and the trunk
was just burstingso I need to get out what we have thus far and
see how far it flies.
Not very far around here, I'm afraid. Just updated and ran my test
suite (which passes with 0.3.3) and I'm getting batches of errors
like:
File (redacted), line 162, in test_offer_interfaces
offer_resp.options.append(offer_opt)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 418, in append
self._data_appender(item)
File
/home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/attributes.py,
line 390, in __getattr__
return getattr(self.data, attr)
File /home/jhp/work/sqlalchemy_0_3_4/lib/sqlalchemy/orm/
query.py,
line 301, in __getattr__
raise AttributeError(key)
AttributeError: _data_appender
The relation is a normal one-many, set up using assign_mapper on the
many side:
assign_mapper(CTX, OfferOption, offer_option, properties={
'offer': relation(OfferResponse, backref='options')
})
I've also seen errors with inherited mappers seeming to no longer
populate foreign keys properly on flush, but those are a little
harder
to boil down to a simple, non-NDS-violating example.
Should I file a ticket for the _data_appender error? What else can
I do
to help track it down?
JP
--~--~-~--~~~---~--~~
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: SA 0.3.4 released
(repeating my last ticket comment) Ok, I think I've tracked this down. The error is occurring because the name of the backref is 'options', which as of r2205 is being overwritten by assign_mapper. I'm not sure what's the right thing to do here. I don't think assign_mapper can avoid stepping on backrefs like this one, since (I think) the backref doesn't exist when assign_mapper is monkeypatching the domain model class. JP --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: New Plugin - AssociationProxy
This is really awesome. It would be even awesomer if append()/create()
could optionally take keyword arguments that would be passed to the
creator, so that the creator would be able to populate other
association fields if it knows how. I'm thinking of something like:
post.tags.append('awesomer', user=me)
If that seems interesting to others, I can probably whip up a patch
this weekend (though it's been a while since I got my hands dirty on
the insides of sqlalchemy).
JP
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
