[sqlalchemy] Re: mysql to sqlalchemy table definition in .py file?
On Friday 10 October 2008 02:06:51 Lukasz Szybalski wrote: On Tue, Sep 30, 2008 at 2:35 AM, [EMAIL PROTECTED] wrote: well it's up to you to extend it to mysql... i don't use mysql, nor i know much about sql anyway; all specific stuff there is a steal/copy/try/error. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc /metadata/autoload.py?revision=208view=markup give it dburl Does this code actually prints the table structure in a sqlalchemy way? or just moves the data from one db to another? does print. have u tried it? moving is in the other 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: auto incrementing
hi there. i had similar thread some months ago http://groups.google.com/group/sqlalchemy/browse_thread/thread/1a3790d58e819f01/4d8e175fc04055aa the option 2 has 2 subcases. - one is to keep all numberings in one table and have a somewhat twisted query+update mechanism (Sequence if u have it, or manual +locks otherwise) - we had that before; - another one that i invented is to have a separate table for each kind of numbering, and each number is a reference to a record in that table. This needs no locking, querying and does not rely on Sequence or similar DB-query-and-update mechanims. it also can work on any scheme for numbering - the number itself can be anything, e.g. text, or list dept2-room3-pos7. but i guess can be somewhat too verbose. On Thursday 09 October 2008 23:14:27 Lukasz Szybalski wrote: Hello, I was wondering if anybody has a good strategy for auto incrementing fields. I want to auto increment field called case# . I have a choice of database auto increment on field case# or do it myself? (correct? No other choices exists? or something in between?) 1. I would like to be able to do pick a number where we will start doing a case#? 2. Reserver a case# for a special group which can auto increment case# between 2,000,000-2,999,999, and add them as they come. 3. I don't want to use (system_id) So it seems as the only way is to make my primary key: case# - unique key, primary, not auto incrementing and let some program manage auto incrementing. What options do I have with sqlalchemy to manage any range of these primary keys? 1. let db auto increment 2. Hold the next case# in a separate database table, and let my program use it to find next case# value. How would I lock/unlock the next case# to make there is no race condition and each case# is taken/successfully saved. 3. Any other options? Have people exeperienced with other strategy that is semi-automatic, and would for for these cases.? Thanks, Lucas --~--~-~--~~~---~--~~ 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 0.5 rc1 - Mysql Unicode(1) decode error
Hi I needed a Unicode(1) Column in one of my tables. It was translated into a char(1) column in MySQL. When querying the table, I get a: AttributeError: 'Set' object has no attribute 'decode' Which disappears if I make the column a Unicode(2), so there might be a small bug in the MySQL code translating Unicode(1) to char(1) ??? Martijn Van: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] Namens Heston James - Cold Beans Verzonden: Thursday, October 09, 2008 5:33 PM Aan: sqlalchemy@googlegroups.com Onderwerp: [sqlalchemy] Can't connect to local MySQL server Hello Guys, I'm receiving errors in my application on a fairly regular basis now and I'm not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I'd appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, 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: ratio on multiple tables
On Oct 9, 2008, at 10:01 PM, John Hunter wrote: To simplify the problem, I have written code that simply connects the reportdate of the fundamental data with a give price data point, eg class PriceData(Base): __tablename__ = 'price_data' ticker = sa.Column(sa.String(12), primary_key=True) date = sa.Column(sa.Date, primary_key=True) price = sa.Column(sa.FLOAT) reportdate = orm.column_property( sa.select( [FundamentalData.reportdate], (ticker==FundamentalData.ticker) (date=FundamentalData.reportdate) ).order_by (FundamentalData.reportdate.desc()).limit(1).label('reportdate')) This works fine, as in the complete example posted below (the code identifies a report date less-than-or-equal-to a price date for each date). But I am confused by the effect of the limit method. My intention is to use it to limit the results to one match, the most recent reportdate before date. But if I remove the limit method call, I still get the same result, a single reportdate, though I would expect a sequence instead since there are multiple reportdates prior to the price date in the example included below. Or if I write limit(4) I still get a single result for reportdate, though I would expect multiple matches. Any ideas what is going on here? Compete example below whats probably happening is that when you access reportdate, SQLA uses a lazy loader to fetch the result. Since its known to be a scalar attribute, only the first row is fetched. The better way to do these correlated column properties it to devise the query in such a way that LIMIT is not needed. In this case, you probably want to be selecting func.max(FundamentalData.reportdate) which should eliminiate the need for LIMIT. That would also make your attribute more compatible with eagerloading too (although theres some trac tickets involving eagerloading in conjunction with correlated subqueries - its doable but is a little quirky atm). --~--~-~--~~~---~--~~ 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: object_session(remote_device_object) returns noneType
On Oct 10, 2008, at 8:03 AM, Heston James - Cold Beans wrote: Morning guys, When calling object_session(my_object), it returns a NoneType object so I’m unable to call commit() and close() on the session, what does this mean? Does it mean my object is not attached to a session? My understanding is that if I did something like: my_object = session.query(object).get(object_id) then pass my_object around the application and wish to commit its session I should be able to use the object_session() method to do that? Is that not correct? that is all correct, however if you remove all strong references to the session, it gets garbage collected and the objects are detached. --~--~-~--~~~---~--~~ 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] session becomes sort of transactional after using begin() / commit()
Hi all, I have a non-transactional session accessing a MySQL database with InnoDB tables (SA 0.4.7). It's created via sessionmaker(bind=self.engine, autoflush=True, transactional=False). For some queries, I use begin() / commit() for explicit transactions. However, after commit()ing, SQLAlchemy wraps all subsequent operations on the same session in BEGIN/COMMIT statements (as observable in MySQL's SQL log), even though I did not call begin() again! Not even close() changes this behavior. Is this intended and if so, how can I make it untransactional again? Thanks, 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA 0.5 rc1 - Mysql Unicode(1) decode error
Martijn Moeling wrote: Hi I needed a Unicode(1) Column in one of my tables. It was translated into a char(1) column in MySQL. When querying the table, I get a: AttributeError: 'Set' object has no attribute 'decode' Which disappears if I make the column a Unicode(2), so there might be a small bug in the MySQL code translating Unicode(1) to char(1) ….??? Try upgrading your MySQL-python library. I've seen it do that on older versions. -j --~--~-~--~~~---~--~~ 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: OrderingList and list.sort()
Adam Dziendziel wrote: Hi, It seems that sorting of ordering list doesn't work. Attribute object.items is an OrderingList: object.items.sort(cmp=my_cmp) The list is sorted, but the ordering column is not updated. I need to call explicitly: object.items._reorder() Maybe override sort() in OrderingList to invoke self._reorder() after sorting? Sure, makes sense. reverse() too. Interested in making a patch for the implementation in sqlalchemy/ext/ and add a new test? -j --~--~-~--~~~---~--~~ 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: select count group_by where ?
On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote: The statement looks good in my book...what does system error mean exactly? On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote: Hello, Could anybody tell me what is wrong with this select statement? Records is a mapper. group=sqlalchemy.select([Records.TRANS_TYPE,Records.TR_DATE,func.count(Records.TR_DATE).label('date_count')], Records.TRANS_TYPE==29).group_by([Records.TRANS_TYPE,Records.TR_DATE]).execute().fetchall() I get system error.. Ideas? I can't find a full example that works nowhere on the doc pages. Thanks, Lucas -- Did I do the where statement correctly? Lucas here is the error.//. File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/sql/expression.py, line 1108, in execute return e.execute_clauseelement(self, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 1227, in execute_clauseelement return connection.execute_clauseelement(elem, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 899, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 911, in _execute_compiled self.__execute_raw(context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 920, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 962, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 814, in do_execute super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 490, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self.identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception --~--~-~--~~~---~--~~ 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 attach instance; another instance with key (?)
Hi all, I'm using SqlAlchemy 0.5rc1 to track jobs with a sqlite datatabe in a state machine used by a server. Each job represents a currently active job in the server and will be alive for awhile as it takes time for the server to process each job. When a job is done it is removed from the system, and the database. Each job has an integer autoincrementing primary key. This is just a flat table with no relations to any other table, with records being created and deleted as the jobs come into and go out of existance. The server runs for awhile (hours) but I've been getting these exceptions: Can't attach instance [EMAIL PROTECTED]; another instance with key (, (220,)) is already present in this session. What is this trying to tell me? Does this mean that a newly created job is trying to use the primary key of an already existing job? Thanks in advance for your help! Doug --~--~-~--~~~---~--~~ 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: infinity with mysql backend
MySQL apparently is unable to store infinity in float columns, so SA can't do it either. Google have me this: http://forums.mysql.com/read.php?39,220571,220573#msg-220573 On 9 Okt., 18:22, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have a sqlalchemy table with a float column, and I would like to be able to store +/- infinity. I am using numpy, and have access to np.inf. However, if I try and store this value, I get OperationalError: (OperationalError) (1054, Unknown column 'Infinity' in 'field list') Is there a way to store infinity using sqlalchemy with a mysql backend? In [128]: sa.__version__ Out[128]: '0.5.0beta4' [EMAIL PROTECTED]:~ mysql --version mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386) --~--~-~--~~~---~--~~ 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: select count group_by where ?
Actually, if TRANS_TYPE is a mapped attribute of the RecordClass class (or whatever its name is), it should be Records.c.TRANS_TYPE. Also, group_by does not take a list but all individual columns as parameters (so just omit those []s). On 10 Okt., 17:38, Lukasz Szybalski [EMAIL PROTECTED] wrote: On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote: The statement looks good in my book...what does system error mean exactly? On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote: Hello, Could anybody tell me what is wrong with this select statement? Records is a mapper. group=sqlalchemy.select([Records.TRANS_TYPE,Records.TR_DATE,func.count(Records.TR_DATE).label('date_count')], Records.TRANS_TYPE==29).group_by([Records.TRANS_TYPE,Records.TR_DATE]).execute().fetchall() I get system error.. Ideas? I can't find a full example that works nowhere on the doc pages. Thanks, Lucas -- Did I do the where statement correctly? Lucas here is the error.//. File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/sql/expression.py, line 1108, in execute return e.execute_clauseelement(self, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 1227, in execute_clauseelement return connection.execute_clauseelement(elem, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 899, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 911, in _execute_compiled self.__execute_raw(context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 920, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 962, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 814, in do_execute super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 490, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self.identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception --~--~-~--~~~---~--~~ 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 connect to local MySQL server
make sure your mysqld.sock is in the location mysql expects it to be. default is /tmp/mysql.sock; if you are using darwinports, depending on mysql version it's /opt/local/var/run/mysqld/mysqld.sock (append mysql version to mysqld). otherwise, head over to forums.mysql.com ; i'm pretty sure that's a recurring topic On Oct 9, 10:32 am, Heston James - Cold Beans [EMAIL PROTECTED] wrote: Hello Guys, I'm receiving errors in my application on a fairly regular basis now and I'm not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I'd appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, Heston database error.txt 2KViewDownload --~--~-~--~~~---~--~~ 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 attach instance; another instance with key (?)
On Friday 10 October 2008 19:42:19 Doug Farrell wrote: Hi all, I'm using SqlAlchemy 0.5rc1 to track jobs with a sqlite datatabe in a state machine used by a server. Each job represents a currently active job in the server and will be alive for awhile as it takes time for the server to process each job. When a job is done it is removed from the system, and the database. Each job has an integer autoincrementing primary key. This is just a flat table with no relations to any other table, with records being created and deleted as the jobs come into and go out of existance. The server runs for awhile (hours) but I've been getting these exceptions: Can't attach instance [EMAIL PROTECTED]; another instance with key (, (220,)) is already present in this session. What is this trying to tell me? Does this mean that a newly created job is trying to use the primary key of an already existing job? could be that, but that means the autoincrement has wrapped over maxint? otherwise such thing happens in scerarios like: x = X() sess.save(x) sess.flush() sess.clear() y = sess.query(X).one() #now u have two same instances: x and y use sess.merge in this case, like x=sess.merge(x) before using x further --~--~-~--~~~---~--~~ 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: InnoDB - Foreign Key must be an Index
I'm running on 5.0.51a and InnoDB as well and have had no problems creating references. Here's what I use: referenced = Table('Referenced', self.metadata, Column('id', MSInteger(unsigned=True), primary_key=True), mysql_engine='InnoDB' ) referencing = Table('Referencing', self.metadata, Column('referenced_id', MSInteger(unsigned=True), ForeignKey('Referenced.id'), mysql_engine='InnoDB' ) Simon On 1 Okt., 14:59, GustaV [EMAIL PROTECTED] wrote: Hi all, I'm experiencing an issue on MySQL (5.0.51a) when sqlalchemy create the tables with foreign keys. The SQL issued : CREATE TABLE `referenced` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ) TYPE = InnoDB; CREATE TABLE `referencing` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `f` INT NOT NULL, FOREIGN KEY(f) REFERENCES referenced(id) ) TYPE = InnoDB; I got an error (#1005 - Can't create table './seed-online/ referencing.frm' (errno: 150) )... I solve this when I specify explicitly the foreign key being an index (looks like it is the normal way to do this) CREATE TABLE `referencing` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `f` INT NOT NULL, INDEX(f), FOREIGN KEY(f) REFERENCES referenced(id) ) TYPE = InnoDB; But I don't know how to tell sqlalchemy to explicitely set that index. On the other hand, at home (using wampserver2.0c, same version of MySQL) it works : it looks like the index is set automatically if not already set (I red this in the MySQL docs). So I'm looking for either : - an option to tell sqlalchemy to set the index explicitely - or, the option in MySQL to turn 'on' to have this INDEX automatically! Thanks a lot! Guillaume --~--~-~--~~~---~--~~ 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: select count group_by where ?
group_by does not take a list but all individual columns as parameters (so just omit those []s). has fixed the error. I think the newer version of sqlachemy no longer has Record.c its just Record.somefield... What also confused me was the fact that in order to do where zyx =2 in query you issue filter() and in select you don't have filter() function and you enter your where stuff inside of select. It works now. Thanks, Lucas On Fri, Oct 10, 2008 at 11:46 AM, Simon [EMAIL PROTECTED] wrote: Actually, if TRANS_TYPE is a mapped attribute of the RecordClass class (or whatever its name is), it should be Records.c.TRANS_TYPE. Also, group_by does not take a list but all individual columns as parameters (so just omit those []s). On 10 Okt., 17:38, Lukasz Szybalski [EMAIL PROTECTED] wrote: On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote: The statement looks good in my book...what does system error mean exactly? On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote: Hello, Could anybody tell me what is wrong with this select statement? Records is a mapper. group=sqlalchemy.select([Records.TRANS_TYPE,Records.TR_DATE,func.count(Records.TR_DATE).label('date_count')], Records.TRANS_TYPE==29).group_by([Records.TRANS_TYPE,Records.TR_DATE]).execute().fetchall() I get system error.. Ideas? I can't find a full example that works nowhere on the doc pages. Thanks, Lucas -- Did I do the where statement correctly? Lucas here is the error.//. File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/sql/expression.py, line 1108, in execute return e.execute_clauseelement(self, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 1227, in execute_clauseelement return connection.execute_clauseelement(elem, multiparams, params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 899, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 911, in _execute_compiled self.__execute_raw(context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 920, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py, line 962, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 814, in do_execute super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs) File /usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py, line 490, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self.identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception -- Python and OpenOffice documents and templates http://lucasmanual.com/mywiki/OpenOffice Fast and Easy Backup solution with Bacula http://lucasmanual.com/mywiki/Bacula --~--~-~--~~~---~--~~ 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: session becomes sort of transactional after using begin() / commit()
On Oct 10, 2008, at 10:52 AM, Simon wrote: Hi all, I have a non-transactional session accessing a MySQL database with InnoDB tables (SA 0.4.7). It's created via sessionmaker(bind=self.engine, autoflush=True, transactional=False). For some queries, I use begin() / commit() for explicit transactions. However, after commit()ing, SQLAlchemy wraps all subsequent operations on the same session in BEGIN/COMMIT statements (as observable in MySQL's SQL log), even though I did not call begin() again! Not even close() changes this behavior. Is this intended and if so, how can I make it untransactional again? if you're referring to the BEGIN/COMMIT around a flush(), flush() always uses its own transaction if no other transaction is already in progress. It should not be wrapping any SELECT statements inside of BEGIN/COMMIT however. Keep in mind that a DBAPI connection, when used, is *always* in a transaction of some kind, so it is impossible to avoid the effects of the database's transaction isolation behavior when querying. --~--~-~--~~~---~--~~ 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: auto incrementing -serializable isolation
On Fri, Oct 10, 2008 at 2:06 AM, [EMAIL PROTECTED] wrote: hi there. i had similar thread some months ago http://groups.google.com/group/sqlalchemy/browse_thread/thread/1a3790d58e819f01/4d8e175fc04055aa the option 2 has 2 subcases. - one is to keep all numberings in one table and have a somewhat twisted query+update mechanism (Sequence if u have it, or manual +locks otherwise) - we had that before; - another one that i invented is to have a separate table for each kind of numbering, and each number is a reference to a record in that table. This needs no locking, querying and does not rely on Sequence or similar DB-query-and-update mechanims. it also can work on any scheme for numbering - the number itself can be anything, e.g. text, or list dept2-room3-pos7. but i guess can be somewhat too verbose. On Thursday 09 October 2008 23:14:27 Lukasz Szybalski wrote: Hello, I was wondering if anybody has a good strategy for auto incrementing fields. I want to auto increment field called case# . I have a choice of database auto increment on field case# or do it myself? (correct? No other choices exists? or something in between?) 1. I would like to be able to do pick a number where we will start doing a case#? 2. Reserver a case# for a special group which can auto increment case# between 2,000,000-2,999,999, and add them as they come. 3. I don't want to use (system_id) So it seems as the only way is to make my primary key: case# - unique key, primary, not auto incrementing and let some program manage auto incrementing. What options do I have with sqlalchemy to manage any range of these primary keys? 1. let db auto increment 2. Hold the next case# in a separate database table, and let my program use it to find next case# value. How would I lock/unlock the next case# to make there is no race condition and each case# is taken/successfully saved. 3. Any other options? Have people exeperienced with other strategy that is semi-automatic, and would for for these cases.? It seems as a good solution will be to take the next case# to a different table and use that with isolation/serialization so there is no deadlocks in the application. For this thread (http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/653b6b2f49a940b8) it seems as postgreSQL is a way to go, compared to mssql for example. Am I missing anything/other options ? Lucas --~--~-~--~~~---~--~~ 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 to sqlalchemy table definition in .py file?
On Fri, Oct 10, 2008 at 1:50 AM, [EMAIL PROTECTED] wrote: On Friday 10 October 2008 02:06:51 Lukasz Szybalski wrote: On Tue, Sep 30, 2008 at 2:35 AM, [EMAIL PROTECTED] wrote: well it's up to you to extend it to mysql... i don't use mysql, nor i know much about sql anyway; all specific stuff there is a steal/copy/try/error. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc /metadata/autoload.py?revision=208view=markup give it dburl Does this code actually prints the table structure in a sqlalchemy way? or just moves the data from one db to another? does print. have u tried it? moving is in the other files. python autoload.py mysql://user:[EMAIL PROTECTED]/production Traceback (most recent call last): File autoload.py, line 203, in ? assert 0, 'unsupported engine.dialect:'+str( engine.dialect) AssertionError: unsupported engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at 0xb782a02c but I found sqlaclhemy-migrate which did the job. http://code.google.com/p/sqlalchemy-migrate/wiki/MigrateVersioning And the code is: easy_install sqlalchemy-migrate migrate create_model --url=mysql://user:[EMAIL PROTECTED]/production --repository=some model.py I can actually use this to manage the db conversion from now on. Thanks, Lucas --~--~-~--~~~---~--~~ 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] Session does not write updated object attribute (no SQL update) (SA 0.4.6)
Hi, I created a custom type that represents a string as list of int(elements), e.g.: - [2, 2, 2, 2] The class I created looks as follows: class IntList(types.TypeDecorator): List of integers that is represented by string in the RDB impl=String def process_bind_param(self, value, engine): if value == [] or value is None: return None else: return ''.join((str(x) for x in value)) def process_result_value(self, value, engine): if value is None: return [] else: return [int(x) for x in value] This data type is then used in one of my tables, which is represented by an object. The problem I have is that changing items in the list seems not to trigger SA, so that a session.flush() does not perform an update, e.g. myobj.intlist[0] = 8 When I, however, do the following, it works: l = list(myobj.intlist) l[0] = 8 myobj.intlist = l On the other hand, the following does not: myobj.intlist[0] = 8 l = list(myobj.intlist) myobj.intlist = l Can please someone enlighten me why this happens, and what I can do about it? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---