[sqlalchemy] Re: Connecting to MySQL
Column('created', DateTime, default=func.now()),
Column('updated', DateTime, onupdate=func.now()))
You can set both default= and onupdate= on the same Column if you want
'updated' to be non-NULL on insert.
That sounds like a nice clean way of doing this Jason, I'm more than happy with
that, it seems the most logical way of implementing it.
Cheers for the advice,
Heston
--~--~-~--~~~---~--~~
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: Connecting to MySQL
Hello Rick, These mapper extensions look very good, I've used a similar concept in other ORM's in the past for all manner of things and have a couple of decent ways to utilize them in this current application. Cheers, Heston From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rick Morrison Sent: 10 July 2008 17:37 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Connecting to MySQL That's exactly what the problem was :-) Is there any reason I should avoid using 0.5? I'm running python 2.4 at the moment, are they compatible? 0.5 is still in beta, and I don't have much experience with it myself, but if were just starting out, I would probably be using that, otherwise you'll need to migrate later; it's easier to just start out with the new API. Next quick question: I have a habbit of using 'created' and 'modified' columns on my tables, is there any way in which I can have the ORM update the dates for me when creating and modifying rows? Yes, check out mapper extensions in the docs, you're going to want after_insert and after_update extensions. --~--~-~--~~~---~--~~ 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] autoload=True and asdecimal=False
Hello When using autoload the created Numeric columns have asdecimal set to True (I use postgres, dunno if it's different with other databases). If would like that they have asdecimal set to False. Can I force that without having to override the results of the autoload with explicit Numeric(asdecimal=False) columns in my Table object? Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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] miruku 0.1a6 released.
miruku is a migration toolkit for SQLAlchemy. I'm pleased to release miruku 0.1a6. There are several bugs fixed since 0.1a3. You may need to re-read the tutorial to adapt some not backward-compatible updates. If you have any suggestion or question, welcome to report at http://groups.google.com/group/ollix-miruku :) CHANGE LOG 0.1a6 (July 11th, 2008) * Fixed deprecated arguments with sessionmaker for SQLAlchemy = 0.5. * The configuration format now support SQLAlchemy's `engine_from_config` function. 0.1a5 (July 7th, 2008) * Fixed error occurs when upgrading a table containing a PickleType column. 0.1a4 (July 7th, 2008) * Remove Python 2.5 specific syntax. * Fixed the error occurs when column's default or onupdate attribut is set to function. * Support multiple metadatas corresponding to a database. * Improve the help text of each commands. * Replace dafualt column to index when running `miruku info` Download and find resources at http://trac.ollix.org/miruku --~--~-~--~~~---~--~~ 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] M2M relationship
Good morning all, So, this morning's challenge has been learning many-to-many relationships, after reading through the tutorial I understand most of the core concepts of how it should work but I'm struggling to actually make it do so, I thought I would come and rely on you good people to help me in the right direction. I have 3 tables configured in my MySQL database, for arguments sake let's say they're called 'post', 'keyword' and 'post_keyword'. I'm declaring my 'post' class like so in a module called post.py: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relation, backref # Configure the delarative base for SQL Alchemy. Base = declarative_base() # Define the Remote Device class. class post(Base): # Define the table for SQL Aclchemy __tablename__ = post # Define the class properties for SQL Alchemy id = Column(String, primary_key=True) content = Column(String) keywords = relation(keyword, secondary=post_keyword, backref='keywords') I then have a pretty much identical class declaration for 'post' but with the obvious changes to its name and property. However, when trying to use this class I get an exception thrown by SQLAlchemy saying: NameError: name 'remote_device_message' is not defined Which is fair enough, as it isn't, I wonder if I'm meant to import it somehow into that post class? All the examples I've found seem to focus on the idea of A) having these two classes defined in the same file and B) using an in memory database where you 'create' the association table in the script, whereas with mine it already exists in the database. It's making me a little confused I think. I'd really appreciate your help on showing me how this implements. Cheers, Heston --~--~-~--~~~---~--~~ 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: M2M relationship
NameError's are thrown usualy by import'ing or similar mechanisms. have a look on your code. eventualy post the whole traceback? On Friday 11 July 2008 12:14:12 Heston James - Cold Beans wrote: Good morning all, So, this morning's challenge has been learning many-to-many relationships, after reading through the tutorial I understand most of the core concepts of how it should work but I'm struggling to actually make it do so, I thought I would come and rely on you good people to help me in the right direction. I have 3 tables configured in my MySQL database, for arguments sake let's say they're called 'post', 'keyword' and 'post_keyword'. I'm declaring my 'post' class like so in a module called post.py: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relation, backref # Configure the delarative base for SQL Alchemy. Base = declarative_base() # Define the Remote Device class. class post(Base): # Define the table for SQL Aclchemy __tablename__ = post # Define the class properties for SQL Alchemy id = Column(String, primary_key=True) content = Column(String) keywords = relation(keyword, secondary=post_keyword, backref='keywords') I then have a pretty much identical class declaration for 'post' but with the obvious changes to its name and property. However, when trying to use this class I get an exception thrown by SQLAlchemy saying: NameError: name 'remote_device_message' is not defined Which is fair enough, as it isn't, I wonder if I'm meant to import it somehow into that post class? All the examples I've found seem to focus on the idea of A) having these two classes defined in the same file and B) using an in memory database where you 'create' the association table in the script, whereas with mine it already exists in the database. It's making me a little confused I think. I'd really appreciate your help on showing me how this implements. Cheers, Heston --~--~-~--~~~---~--~~ 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] Can't reconnect until invalid transaction is rolled back after application sits idle overnight.
Using SQLAlchemy 0.4.4
I am having a problem with SQLAlchemy where after the application that
uses SQLAlchemy has been sitting overnight and a user makes the first
queries of the day thru the app that uses SA, SQLAlchemy throws an
error saying 'MySQL server has gone away', which I understand the
reason to be that my mysql server has cut the idle connections, but I
have pool_recycle = 3600 set and I thought that was the solution to
keeping connections alive?
After that error message I get two more exceptions when the user tries
the request again of Can't reconnect until invalid transaction is
rolled back, which I don't understand at all because the application
only queries the DB and never deals with transactions. After those
three attempts, the forth request will go through just fine. :-\ Any
pointers on what I should look for or do?
Here is the full traceback of the exceptions...
Module vendormiddleware.model:47 in by_company_code
count = codes.count()
Module sqlalchemy.orm.query:1087 in count
return q._count()
Module sqlalchemy.orm.query:1108 in _count
return self.session.scalar(s, params=self._params, mapper=self.mapper)
Module sqlalchemy.orm.session:612 in scalar
return self.__connection(engine, close_with_result=True).scalar(clause,
params or {}, **kwargs)
Module sqlalchemy.engine.base:836 in scalar
return self.execute(object, *multiparams, **params).scalar()
Module sqlalchemy.engine.base:846 in execute
return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:897 in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
Module sqlalchemy.engine.base:909 in _execute_compiled
self.__execute_raw(context)
Module sqlalchemy.engine.base:918 in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
Module sqlalchemy.engine.base:962 in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
Module sqlalchemy.engine.base:944 in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) (2006, 'MySQL server has gone
away') u'SELECT count(company.company_id) AS count_1 \nFROM company
\nWHERE company.company_code = %s' [u'CUSTOMER1']
Module vendormiddleware.model:47 in by_company_code
count = codes.count()
Module sqlalchemy.orm.query:1087 in count
return q._count()
Module sqlalchemy.orm.query:1108 in _count
return self.session.scalar(s, params=self._params, mapper=self.mapper)
Module sqlalchemy.orm.session:612 in scalar
return self.__connection(engine, close_with_result=True).scalar(clause,
params or {}, **kwargs)
Module sqlalchemy.engine.base:836 in scalar
return self.execute(object, *multiparams, **params).scalar()
Module sqlalchemy.engine.base:846 in execute
return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:897 in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
Module sqlalchemy.engine.base:906 in _execute_compiled
context = self.__create_execution_context(compiled=compiled,
parameters=distilled_params)
Module sqlalchemy.engine.base:950 in __create_execution_context
return self.engine.dialect.create_execution_context(connection=self,
**kwargs)
Module sqlalchemy.databases.mysql:1464 in create_execution_context
return MySQLExecutionContext(self, connection, **kwargs)
Module sqlalchemy.engine.default:178 in __init__
self.cursor = self.create_cursor()
Module sqlalchemy.engine.default:275 in create_cursor
return self._connection.connection.cursor()
Module sqlalchemy.engine.base:583 in connection
raise exceptions.InvalidRequestError(Can't reconnect until invalid
transaction is rolled back)
InvalidRequestError: Can't reconnect until invalid transaction is
rolled back
--~--~-~--~~~---~--~~
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: M2M relationship
NameError's are thrown usualy by import'ing or similar mechanisms. have a look on your code. eventualy post the whole traceback? Hello Mate, I think you're right, but the problem is that I don't know what I 'should' be importing into the class. See, I have two files; Post.py and Keyword.py, both of which contain a single class of the same name. I want to form a M2M relationship between them. If I want to declare a relationship in Post.py do I have to import Keyword.py? and what about the joining/association table? Do I need to create a class which represents that too? Cheers, Heston --~--~-~--~~~---~--~~ 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: Can't reconnect until invalid transaction is rolled back after application sits idle overnight.
On Jul 11, 2008, at 9:23 AM, Ryan Parrish wrote: Using SQLAlchemy 0.4.4 I am having a problem with SQLAlchemy where after the application that uses SQLAlchemy has been sitting overnight and a user makes the first queries of the day thru the app that uses SA, SQLAlchemy throws an error saying 'MySQL server has gone away', which I understand the reason to be that my mysql server has cut the idle connections, but I have pool_recycle = 3600 set and I thought that was the solution to keeping connections alive? After that error message I get two more exceptions when the user tries the request again of Can't reconnect until invalid transaction is rolled back, which I don't understand at all because the application only queries the DB and never deals with transactions. After those three attempts, the forth request will go through just fine. :-\ Any pointers on what I should look for or do? this indicates your application has checked out a Connection from the pool and is keeping it open, in a transaction. the connection then times out, and upon reaccess the next day would like to reconnect itself on next access; for a current Connection, this can reconnect its internal DBAPI connection transparently and you get to use the same Connection object. But Connection won't allow you to do this if its in a transaction, i.e. begin(); when the disconnect exception is thrown, it expects that to be within a try/except block which will do a rollback on error. The reason you're getting the disconnect exception in the first place is because the pool_recycle feature only works upon checkout from the pool. So the solution is the same, ensure all connections are returned to the pool after operations are complete. --~--~-~--~~~---~--~~ 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: M2M relationship
On Jul 11, 2008, at 9:59 AM, Heston James - Cold Beans wrote: NameError's are thrown usualy by import'ing or similar mechanisms. have a look on your code. eventualy post the whole traceback? Hello Mate, I think you’re right, but the problem is that I don’t know what I ‘should’ be importing into the class. See, I have two files; Post.py and Keyword.py, both of which contain a single class of the same name. I want to form a M2M relationship between them. If I want to declare a relationship in Post.py do I have to import Keyword.py? and what about the joining/association table? Do I need to create a class which represents that too? the association table is an instance of Table, and does not need its own class. It's easiest to declare the association table in the same module as that which it is used, in this case post.py. When you create post.py, that creates Post, and in order to create the relation either needs to import keyword.py directly, or, when a third module uses post.py it would have to also import keyword.py before using the Post class. Basiclally nothing happens until the classes are first used.When the classes are first used, all dependencies must have been imported at some point. It doesn't matter from where since they are all ultimately placing themselves in a SQLAlchemy-specific registry. --~--~-~--~~~---~--~~ 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 connection pooling and mysql last_insert_id()
As far as I know SqlAlchemy does not use LAST_INSERT_ID() (at least not for single row inserts), At the mysql protocol level, the id created for the auto inc column will be returned for each insert statement (as the result code for that command). The python MySQLdb dbapi driver will make this available trough the property 'lastrowid' on the cursor object. I guess this is wat SA uses. Because the id is returned on the same statement the connection pool will not be a problem. On Jul 11, 2:18 am, lilo [EMAIL PROTECTED] wrote: According my mysql, LAST_INSERT_ID() is connection specific, so there is no problem from race conditions. If I insert a record into a autoincremented table and do last_insert_id() on it, would there be a possibility where another insert happen just before selecting last_insert_id(). This won't be a problem with mysql if there isn't any connection pooling. Since sqlalchemy has support for connection pooling, would there be a chance where connection is shared with another insert just before selecting last_insert_id()? --~--~-~--~~~---~--~~ 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] SA Performance (some profiling on simple selects)
Hi
I did some profiling a simple select type query to check SA
performance for such use-cases
The test consiste of a very simple Order record:
order = Table('tblorder', metadata,
Column('order_id', Integer, primary_key = True),
Column('order_user_id', Integer),
Column('order_state', Integer),
Column('order_price', Integer),
Column('order_delivery_cost', Integer),
Column('order_creation_date', DateTime),
Column('order_modification_date', DateTime),
Column('order_last_payment_check', DateTime),
Column('order_delivery_address_id', Integer),
)
and some related log lines that keep track of what happens to the
order:
orderlog = Table('tblorderlog', metadata,
Column('orderlog_id', Integer, primary_key = True),
Column('orderlog_order_id', Integer,
ForeignKey('tblorder.order_id')),
Column('orderlog_creation_date', DateTime),
Column('orderlog_message', String),
Column('orderlog_priority', Integer),
)
On the ORM side of things these 2 have a simple 1-1 mapping with
classes Order and OrderLog and
the loglines are mapped to the order using:
'orderLogs': relation(OrderLog, backref='order')
The main loop of the test program is the following:
times = 1000
start = time.time()
for i in range(times):
order = Session.query(Order).get(72244)
for log in order.orderLogs:
pass
Session.clear()
end = time.time()
Note that it is the same Order that I am fetching all the time,
because I am interested in SA overhead and not in the speed of the
database (MySQL) itself, this way mysql will fetch from memory and not
be disk bound (also in this test I made sure I am not network bound).
Also note that the session is cleared each iteration trough the loop
to force SA to perform the 2 queries and corresponding mapping
overhead (1 qry for fetching order, 1 qry for fetching the 17
corresponding orderlog lines) on each iteration.
Profiling with cProfile (python 2.5.1) gave the following results
(sorted by total time spend in function):
2923724 function calls (2880671 primitive calls) in 5.965 CPU
seconds
Ordered by: internal time
List reduced from 498 to 20 due to restriction 20
ncalls tottime percall cumtime percall
filename:lineno(function)
20000.5850.0000.5850.000 {method 'query' of
'_mysql.connection' objects}
180000.1920.0001.7150.000 mapper.py:
1407(_instance)
180000.1600.0000.8010.000 mapper.py:
1381(populate_state)
20000.1530.0000.2590.000 base.py:
1448(_init_metadata)
20.1530.0000.1980.000 times.py:
43(DateTime_or_None)
360000.1470.0000.1470.000 identity.py:
91(__contains__)
160000.1390.0000.2700.000 compiler.py:
239(visit_column)
1120000.1300.0000.2400.000 base.py:
1363(__getitem__)
940000.1270.0000.3180.000 strategies.py:
71(new_execute)
180000.1220.0000.1220.000 attributes.py:
745(__init__)
42000/20000.1140.0001.2330.001 compiler.py:
176(process)
162036/1620220.1130.0000.1340.000 {getattr}
140000.1060.0000.5410.000 compiler.py:
228(visit_label)
20000.1030.0001.2220.001 compiler.py:
466(visit_select)
1120000.1030.0000.1100.000 base.py:1617(_get_col)
320000.0890.0000.1580.000 compiler.py:
401(_truncated_identifier)
720000.0860.0000.0960.000 util.py:806(add)
1351010.0850.0000.0850.000 {isinstance}
180000.0730.0000.1810.000 identity.py:104(add)
460000.0680.0000.0800.000 {built-in method sub}
What we see here is that of the total of 5.965 seconds, the test
spend only about 0.585 seconds performing the actual SQL querys, or
about 10% of the total time. The other 90% of the time we are spending
on SA overhead (except for the times.py line which is somewhere in the
MySQLdb driver).
If I look at the cummulative output of the profiler (ommitted for
brevity) I see for instance that SA spends 1.3 seconds compiling the
SQL expressions (it apparently recompiles the same SQL expression on
each iteration).
My question to the SA community would be how much ORM overhead do you
find acceptable? and how does SA compare to for instance to java's
Hibernate in this regard or other ORM solutions?
I would love to help out trying to reduce this overhead, but I have no
idea what the SA devs have in mind for the future of SA with regard to
speed (e.g. speed vs. feature completeness). In the tutorials and
documentation there is currently a strong emphasis on use cases
highlighting the very dynamic nature of building complex queries using
SA's ORM tools. Not so much on how to perform simple (and static)
queries fast using the ORM.
Maybe there is a way to meoize the results of the compile
[sqlalchemy] Re: M2M relationship
the association table is an instance of Table, and does not need its own class. It's easiest to declare the association table in the same module as that which it is used, in this case post.py. Ok this sounds fine, I've done this now, declaring the table in the post.py module. When you create post.py, that creates Post, and in order to create the relation either needs to import keyword.py directly, or, when a third module uses post.py it would have to also import keyword.py before using the Post class. That's fine too, I've now imports keyword into post. I'm now getting a new error thrown at me though: File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post' with which to generate a foreign key This is unusual as the table post does exist in the database, and is also declared as the class in the post.py module. Any ideas? Thanks Michael, Heston --~--~-~--~~~---~--~~ 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: Can't reconnect until invalid transaction is rolled back after application sits idle overnight.
On Jul 11, 10:03 am, Michael Bayer [EMAIL PROTECTED] wrote:. The reason you're getting the disconnect exception in the first place is because the pool_recycle feature only works upon checkout from the pool. So the solution is the same, ensure all connections are returned to the pool after operations are complete. So if i just setup sessionmaker(autoflush=True, transactional=False, bind=engine) rather than transactional=True which it is now; I would never have these implicit begin()'s started which are pointless since I'm only doing selects on the DB? Thus my problem would be solved? --~--~-~--~~~---~--~~ 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: Can't reconnect until invalid transaction is rolled back after application sits idle overnight.
On Jul 11, 2008, at 10:45 AM, Ryan Parrish wrote: On Jul 11, 10:03 am, Michael Bayer [EMAIL PROTECTED] wrote:. The reason you're getting the disconnect exception in the first place is because the pool_recycle feature only works upon checkout from the pool. So the solution is the same, ensure all connections are returned to the pool after operations are complete. So if i just setup sessionmaker(autoflush=True, transactional=False, bind=engine) rather than transactional=True which it is now; I would never have these implicit begin()'s started which are pointless since I'm only doing selects on the DB? Thus my problem would be solved? using transactional=False is one solution, but a better one is to simply rollback(), commit(), or close() the Session when operations are complete - transactional mode (which is called autocommit=False in 0.5) has the advantage that a series of select operations will all share the same isolated transactional context..this can be more or less important depending on the isolation mode in effect and the kind of application. DBAPI has no implicit autocommit mode so there is always a transaction implicitly in progress when queries are made. --~--~-~--~~~---~--~~ 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: M2M relationship
if u look up the stacktrace/traceback, u'll see which statement in your own code triggered the error. is it in the mapping-part or is still in table-declaration part? do all 3 tables use same metadata? On Friday 11 July 2008 17:31:31 Heston James - Cold Beans wrote: the association table is an instance of Table, and does not need its own class. It's easiest to declare the association table in the same module as that which it is used, in this case post.py. Ok this sounds fine, I've done this now, declaring the table in the post.py module. When you create post.py, that creates Post, and in order to create the relation either needs to import keyword.py directly, or, when a third module uses post.py it would have to also import keyword.py before using the Post class. That's fine too, I've now imports keyword into post. I'm now getting a new error thrown at me though: File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post' with which to generate a foreign key This is unusual as the table post does exist in the database, and is also declared as the class in the post.py module. Any ideas? Thanks Michael, Heston --~--~-~--~~~---~--~~ 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: Connecting to MySQL
Lukasz Szybalski wrote:
On Thu, Jul 10, 2008 at 11:59 AM, jason kirtland [EMAIL PROTECTED] wrote:
Lukasz Szybalski wrote:
On Thu, Jul 10, 2008 at 11:26 AM, Heston James - Cold Beans
[EMAIL PROTECTED] wrote:
Session.add is a version 0.5 method, you're maybe running 0.4.6?
In the 0.4.x series, it's going to be:
Session.save() for objects that are to be newly added to the session
Session.update() for objects that are already in the session, or
Session.save_or_update() to have the library figure it out as it does for
Session.add in v0.5.x
Hi Rick,
That's exactly what the problem was :-) Is there any reason I should avoid
using 0.5? I'm running python 2.4 at the moment, are they compatible?
Next quick question: I have a habbit of using 'created' and 'modified'
columns on my tables, is there any way in which I can have the ORM update
the dates for me when creating and modifying rows?
From the link I sent you previously:
sqlalchemy.Column('CreatedDate', sqlalchemy.Date,
default=datetime.now().date()),
sqlalchemy.Column('CreatedTime', sqlalchemy.Time,
default=datetime.now().time())
Not so much. That'll stamp every inserted row with the same time-
whatever time it was when python evaluated the Table definition.
Here's a cross-db way to get timestamps:
from sqlalchemy import Table, Column, DateTime, func
Table('abc', metadata,
...
Column('created', DateTime, default=func.now()),
Column('updated', DateTime, onupdate=func.now()))
What exactly is func ? Is that a function that just gets time or?
Can I use
onupdate=func.now().time() for time
onupdate=func.now().date() for date
I don't really prefer to have both date and time mixed in datetime field.
func is a SQL function expression builder: func.now() emits the sql
function NOW() as the column value in the insert, moving responsibility
for timestamp calculation to the database. func can build any function
the database supports, like current_date or current_time.
http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_everythingelse_functions
--~--~-~--~~~---~--~~
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: M2M relationship
if u look up the stacktrace/traceback, u'll see which statement in
your own code triggered the error. is it in the mapping-part or is
still in table-declaration part?
do all 3 tables use same metadata?
Thank you for your comments so far, I appreciate you helping me out on this.
The entire stack trace is below:
Traceback (most recent call last):
File test.py, line 9, in module
my_device = post.post(3F8ADE52-4F63-11DD-9AF0-90BB55D89593, Title,
Content)
File /var/lib/python-support/python2.5/sqlalchemy/orm/attributes.py,
line 1211, in init
extra_init(class_, oldinit, instance, args, kwargs)
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
798, in extra_init
self.compile()
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
350, in compile
mapper.__initialize_properties()
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
371, in __initialize_properties
prop.init(key, self)
File /var/lib/python-support/python2.5/sqlalchemy/orm/interfaces.py,
line 374, in init
self.do_init()
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 467, in do_init
self.__determine_joins()
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 521, in __determine_joins
self.secondaryjoin = _search_for_join(self.mapper,
self.secondary).onclause
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 514, in _search_for_join
return sql.join(mapper.local_table, table)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 116, in join
return Join(left, right, onclause, isouter)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 2275, in __init__
self.onclause = self.__match_primaries(self.left, self.right)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 2317, in __match_primaries
return sql_util.join_condition(primary, secondary)
File /var/lib/python-support/python2.5/sqlalchemy/sql/util.py, line 74,
in join_condition
col = fk.get_referent(a)
File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 755,
in get_referent
return table.corresponding_column(self.column)
File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788,
in column
foreign key % tname)
sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post'
with which to generate a foreign key
I've also attached the two modules and the test script I'm trying to use, if
you wouldn't mind taking a look an letting me know what I'm doing wrong, I
would really appreciate it.
Cheers,
Heston
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
# Class Imports
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
# Configure the delarative base for SQL Alchemy.
Base = declarative_base()
# Define the Remote Device class.
class keyword(Base):
# Define the table for SQL Aclchemy
__tablename__ = keyword
# Define the class properties for SQL Alchemy
keyword_id = Column(Integer, primary_key=True)
word = Column(String)
# I'm the class constructor method.
def __init__(self, keyword_id=, word=):
self.keyword_id = keyword
self.word = word
# Class Imports
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import relation, backref
import keyword
# Configure the delarative base for SQL Alchemy.
Base = declarative_base()
metadata = MetaData()
post_keyword = Table(post_keyword, metadata,
Column(post_id, String, ForeignKey('post.post_id')),
Column('keyword_id', Integer,
ForeignKey('keyword.keyword_id'))
)
# Define the Remote Device class.
class post(Base):
# Define the table for SQL Aclchemy
__tablename__ = post
# Define the class properties for SQL Alchemy
post_id = Column(String, primary_key=True)
title = Column(String)
content = Column(String)
# many to many BlogPost-Keyword
keywords = relation(keyword.keyword, secondary=post_keyword, backref=post)
# I'm the class constructor method.
def __init__(self, post_id=, title=, content=):
self.post_id = post_id
self.title = title
self.content = contentimport post
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(connectionstring, echo=False)
Session =
[sqlalchemy] Re: M2M relationship
i'm not very familiar with declarative but in any way i dont see where u bind a) the metadata to the engine, and b) the declarative-stuff to the metadata. maybe its something i'm missing but maybe read more on those. On Friday 11 July 2008 19:20:21 Heston James - Cold Beans wrote: if u look up the stacktrace/traceback, u'll see which statement in your own code triggered the error. is it in the mapping-part or is still in table-declaration part? do all 3 tables use same metadata? Thank you for your comments so far, I appreciate you helping me out on this. The entire stack trace is below: Traceback (most recent call last): File test.py, line 9, in module my_device = post.post(3F8ADE52-4F63-11DD-9AF0-90BB55D89593, Title, Content) File /var/lib/python-support/python2.5/sqlalchemy/orm/attributes.py, line 1211, in init extra_init(class_, oldinit, instance, args, kwargs) File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line 798, in extra_init self.compile() File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line 350, in compile mapper.__initialize_properties() File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line 371, in __initialize_properties prop.init(key, self) File /var/lib/python-support/python2.5/sqlalchemy/orm/interfaces.py, line 374, in init self.do_init() File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py, line 467, in do_init self.__determine_joins() File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py, line 521, in __determine_joins self.secondaryjoin = _search_for_join(self.mapper, self.secondary).onclause File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py, line 514, in _search_for_join return sql.join(mapper.local_table, table) File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py, line 116, in join return Join(left, right, onclause, isouter) File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py, line 2275, in __init__ self.onclause = self.__match_primaries(self.left, self.right) File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py, line 2317, in __match_primaries return sql_util.join_condition(primary, secondary) File /var/lib/python-support/python2.5/sqlalchemy/sql/util.py, line 74, in join_condition col = fk.get_referent(a) File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 755, in get_referent return table.corresponding_column(self.column) File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post' with which to generate a foreign key I've also attached the two modules and the test script I'm trying to use, if you wouldn't mind taking a look an letting me know what I'm doing wrong, I would really appreciate it. Cheers, Heston --~--~-~--~~~---~--~~ 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: mysql innodb table insert problem
If I do manual insert into sql server like INSERT INTO lookup
(username, shardname) VALUES ('0', 'shard1');, all works fine. But
sqlalchemy doesn't insert for whatever reason into innodb table.
Here is my shard session:
create_session_lookup = sessionmaker(class_=ShardedSession,
autoflush=True, transactional=True)
I have shard session set to transactional. Does this conflict with
innodb transaction?
--~--~-~--~~~---~--~~
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: mysql innodb table insert problem
I have shard session set to transactional. Does this conflict with innodb transaction? No, but it means your inner sess.begin() and sess.commit() are now within the scope of an outer transaction, so your inner sess.commit() has no effect. Since you immediately issue a sess.clear() after your ineffective sess.commit(), when the outer transaction finally gets a chance to commit, the changes are now gone. If you're going to be handling transaction state yourself, then don't use a transactional session. --~--~-~--~~~---~--~~ 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 Performance (some profiling on simple selects)
about (ways of) query compilation:
for example in dbcook i have query-filters expressed as plain python
functions, and that adds 3 or 4 more levels additional to the usual
query building. here the levels:
expr.translating:
-a: py-func - makeExpresion - expr
-b: expr - walk( Translator( context) ) - SA column expression
context needs plain vars, and vars-classes/mappers
-c: context binding: classes / mappers
-d: context binding: plain vars
-e: apply column-expression as select over a query
-f: sql.bindparams
way0: all everytime: a c, d,b,e,sql - no bindparams whatsoever
way1: a c, store; then d,b,e,sql - no bindparams whatsoever
way2: a b c, store; then d/bindparams, e,sql
way3: a b c, e, store; then d/bindparams, sql
way4: a b c, e, sql, store; then d/bindparams
i guess similar breakdown can be done on the SA-query-building itself,
with the most important division being query-into-statement,
statement+bindparams+exec, instantiation.
i admit it's a bit hard to build the model and app in a way that
allows usage of bindparams in their real power, which is: the whole
query statement is like a function and the bindparams are arguments
to that function. note i'm not talking about the constants that SA
automaticaly makes into bindparams, i'm talking about real
parameters.
i also must admit that i'm not too much sql-er and try to avoid it as
much as possible; still, if speed means replacing a 1000 hierarchical
per-node queries with one huge awfuly twisted query that gives those
1000 in one long shot, so be it (well but it takes.. weeks to
invent). i guess if SA was say 10 times faster i would not bother
doing it until i run it on some networked server and find that the
reality is about 10,000 nodes and it didn't realy matter if SA was
faster or slower - it's just a wrong way.
not that i like overheads either... but in the long run i think it's
correctness and consistency and re/usability that is important. What
was running in 5 secs yesterday will run in 3 secs next year on
same-price-equipment.
it would be quite interesting if u try these, in various independent
combinations, and compare the results:
a) the query-into-statement compilation, then binding+executing
that 1000 times
b) keep the instances cached
c) have just _one_ query returning same thing that current
1000-queries do, in one long shot - and run that once.
i guess combining a) with c) is meaningless, hence the useful
combinations seems: nothing, a, a+b, c, c+b
it may show interesting sides of the query execution
but give them more than 1000, maybe 3000 (~15sec).
ciao
svilen
On Friday 11 July 2008 12:19:26 Henk wrote:
Hi
I did some profiling a simple select type query to check SA
performance for such use-cases
The test consiste of a very simple Order record:
order = Table('tblorder', metadata,
Column('order_id', Integer, primary_key = True),
Column('order_user_id', Integer),
Column('order_state', Integer),
Column('order_price', Integer),
Column('order_delivery_cost', Integer),
Column('order_creation_date', DateTime),
Column('order_modification_date', DateTime),
Column('order_last_payment_check', DateTime),
Column('order_delivery_address_id', Integer),
)
and some related log lines that keep track of what happens to the
order:
orderlog = Table('tblorderlog', metadata,
Column('orderlog_id', Integer, primary_key = True),
Column('orderlog_order_id', Integer,
ForeignKey('tblorder.order_id')),
Column('orderlog_creation_date', DateTime),
Column('orderlog_message', String),
Column('orderlog_priority', Integer),
)
On the ORM side of things these 2 have a simple 1-1 mapping with
classes Order and OrderLog and
the loglines are mapped to the order using:
'orderLogs': relation(OrderLog, backref='order')
The main loop of the test program is the following:
times = 1000
start = time.time()
for i in range(times):
order = Session.query(Order).get(72244)
for log in order.orderLogs:
pass
Session.clear()
end = time.time()
Note that it is the same Order that I am fetching all the time,
because I am interested in SA overhead and not in the speed of the
database (MySQL) itself, this way mysql will fetch from memory and
not be disk bound (also in this test I made sure I am not network
bound). Also note that the session is cleared each iteration trough
the loop to force SA to perform the 2 queries and corresponding
mapping overhead (1 qry for fetching order, 1 qry for fetching the
17 corresponding orderlog lines) on each iteration.
Profiling with cProfile (python 2.5.1) gave the following results
(sorted by total time spend in function):
2923724 function calls (2880671 primitive calls) in 5.965
CPU seconds
Ordered by: internal time
List reduced from 498 to 20 due to
[sqlalchemy] autoload primary key
Hello Are there particular conditions for the autoload mechanism to discover the primary key. If I remove Line 12 of this code http://paste.turbogears.org/paste/3183, I get this traceback http://paste.turbogears.org/paste/3182. Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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: autoload primary key
ive never observed a scenario where SQLA could not reflect the primary key status of a column. Feel free to share wtih us what database youre using and the exact DDL used to generate the table in question. On Jul 11, 2008, at 4:07 PM, Eric Lemoine wrote: Hello Are there particular conditions for the autoload mechanism to discover the primary key. If I remove Line 12 of this code http://paste.turbogears.org/paste/3183, I get this traceback http://paste.turbogears.org/paste/3182. Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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: autoload primary key
On Fri, Jul 11, 2008 at 11:00 PM, Michael Bayer [EMAIL PROTECTED] wrote: ive never observed a scenario where SQLA could not reflect the primary key status of a column. Feel free to share wtih us what database youre using and the exact DDL used to generate the table in question. Well, the primary key wasn't actually set in the DB. Sorry for the noise. -- Eric --~--~-~--~~~---~--~~ 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: mysql innodb table insert problem
Thanks, I got it to work now. But why did it work for myisam table in the first place. Shouldn't session scope problem also have affected the inserts for myisam table. Insert into myisam table worked because it does not support transactions? On Jul 11, 4:03 pm, Rick Morrison [EMAIL PROTECTED] wrote: I have shard session set to transactional. Does this conflict with innodb transaction? No, but it means your inner sess.begin() and sess.commit() are now within the scope of an outer transaction, so your inner sess.commit() has no effect. Since you immediately issue a sess.clear() after your ineffective sess.commit(), when the outer transaction finally gets a chance to commit, the changes are now gone. If you're going to be handling transaction state yourself, then don't use a transactional session. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
