[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
Thanks Mike for your views. Basically I wanted to execute the statement explicitly along with the other updates I wanted to do in a transaction and return None. So I am basically stuck with my use-case. I want to do some additional updates whenever an insert is done by the user. And these updates along with the original insert should be in a transaction as either all these should be successfull or none. What is the best way to do this then? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
Hi, Can anyone suggest a solution to the last two posts I 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
Also based on certain conditions I would not like to execute the insert at all... So is there a way to just return from execute method of MyProxy class with out actually executing the insert statement? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
I want to some updates before doing any insert. So I needed the above. Now the issue I am facing is that I need all these updates and the current insert to be in a transaction. That is either all should be successful or none. How is that possible with a connection proxy (something like below)? === class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): if isinstance(clauseelement, Insert): trans = self.connection.begin() try: # Do some updates # Execute the current insert also ... trans.commit() except: trans.rollback() = -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] API that allows me to do additional database operations just before insert execution for SQL Expression
Is there any API that allows me do some processing (I want to do additional updates based on the insert statement) just before executing an insert statement using SQL Expression? I dont want to do this during compile time (@Compiles(Insert)) as I will be doing some database updates and this is not desirable for every compilation e.g., just a simple print should not do this additional processing. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to find columns being updated/inserted given a Update/Insert object
Thanks Michael. But this wont probably work if I want to find the columns within @Compiles(Insert) I have a use-case for the below. Can you help @compiles(Insert) def contextual_insert(insert, compiler, **kw): print insert.compile().params- This will gointo infinite loop. return compiler.visit_insert(insert, **kw) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to find columns being updated/inserted given a Update/Insert object
Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? t = Table(abc, MetaData(conn), Column(x, String), Column(y, Integer)) u = t.update().values(x='a') # Now I want to get list of columns being updated (i.e., x) given u (i.e., Update object) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Context based execution
Hi, Thanks a lot. Can someone answer this question also = @compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Context based execution
Thanks Michael Trier. Michael Bayer, Can you help me with the other two questions I asked ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Context based execution
@compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? The caveat however for update/insert/delete when used with the ORM is that the mapper caches its compilation of these constructs Why does mapper cache for update/insert/delete and can you confirm it does not do this for select and I can safely use this approach for select with ORM? The idiomatic way to modify the insert/update/delete construct within a flush is to use MapperExtension before_insert/before_update/before_delete to modify the mapped object's state right before it's flushed. Is there a recipe/code which I can quickly refer to get started on before_insert and also how to modify a insert statement in before_insert? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Context based execution
Thanks Michael. I will try your suggestion. But I would like the date context to be applied while executing and not actually modify the select statement to support the use-case where I have a select statement and would the same select to be executed in different date contexts. === t1 = table('t1', column('a'), column('b'), column('c')) s = select([t1]) With DateContext(b = 12) conn.execute(s) This would actually execute select a, b, c from t1 where b = 12 With DateContext(b = 13) conn.execute(s) -- This would actually execute select a, b, c from t1 where b = 13 Also I have these questions 1. Can we acheive the same thing if user uses sessions to query With DateContext(b = 12): result = session.query(MyObj)--- b =12 condtion should be automatically applied here. 2. Can we acheive the same thing with updates/inserts and not just with selects -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Context based execution
I want to set some context and would like sqlalchemy to respect that context. For Eg., I want to set a context where all my select queries should have an additional condition t.c.from_date 20110301 if the table has a column named from_date. with fromDateContext(from_date = 20110301): s1 = select([t, t1]) conn.execute(s1) s2 = select([t]) conn.execute(s2) u1 = t.update.where(a = '1') conn.execute(u1) So in the above examples I would like to append the additional condtion t.c.from_date 20110301 for all select/update queries with in the context before executing them. Is this possible ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unable to create a view that has where condition
Shouldn't these formats be handled properly? Or am I missing something? I think this is a limitation of sqlite if I am not wrong... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unable to create a view that has where condition
Using literal_column does not give any error, but the condition actually does not work and produces wrong results for me. For example, in the below program I inserted a record with value of date column 'd' as '20110210'. And a view with a condition d '20110310' still outputs this record. Let me know if I am missing something. Program: === from sqlalchemy import * from sqlalchemy.sql import * from dateutil import parser conn = create_engine(sqlite:u/my.db).connect() t = Table(x, MetaData(conn), Column(z, String), Column(d, Date)) t.create(checkfirst=True) ins = t.insert().values(z='a', d=parser.parse('20110210')) conn.execute(ins) s = select([t]) print s result = conn.execute(s) for row in result: print row s = select([t], t.c.d literal_column('20110310')) print s conn.execute(CREATE TEMP VIEW myview AS %s % s.compile(conn)); v = Table(myview, MetaData(conn), autoload=True) result_view = conn.execute(select([v])) for row in result_view: print row === Output: = SELECT x.z, x.d FROM x (u'a', datetime.date(2011, 2, 10)) SELECT x.z, x.d FROM x WHERE x.d '20110310' (u'a', datetime.date(2011, 2, 10)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unable to create a view that has where condition
Here is the output after 'echo=True' is put. I see that date values are getting stored as '-mm-dd' format in sqlite (atleast that is what is getting printed using echo=True). And when I use 'mmdd' format in my view creation it produces wrong results. However if I use '-mm-dd' I seem to get correct results. Shouldn't these formats be handled properly? Or am I missing something? === [20110225 11:09:11:127 base.py:945 INFO] PRAGMA table_info(y) [20110225 11:09:11:128 base.py:946 INFO] () [20110225 11:09:11:141 base.py:945 INFO] CREATE TABLE y ( z VARCHAR, d DATE ) [20110225 11:09:11:141 base.py:946 INFO] () [20110225 11:09:11:149 base.py:745 INFO] COMMIT [20110225 11:09:11:150 base.py:945 INFO] INSERT INTO y (z, d) VALUES (?, ?) [20110225 11:09:11:150 base.py:946 INFO] ['a', '2011-02-10'] [20110225 11:09:11:154 base.py:745 INFO] COMMIT [20110225 11:09:11:158 base.py:945 INFO] SELECT y.z, y.d FROM y [20110225 11:09:11:158 base.py:946 INFO] [] (u'a', datetime.date(2011, 2, 10)) [20110225 11:09:11:161 base.py:945 INFO] CREATE TEMP VIEW myview AS SELECT y.z, y.d FROM y WHERE y.d '20110310' [20110225 11:09:11:161 base.py:946 INFO] () [20110225 11:09:11:161 base.py:745 INFO] COMMIT [20110225 11:09:11:162 base.py:945 INFO] PRAGMA table_info(myview) [20110225 11:09:11:162 base.py:946 INFO] () [20110225 11:09:11:163 base.py:945 INFO] PRAGMA foreign_key_list(myview) [20110225 11:09:11:163 base.py:946 INFO] () [20110225 11:09:11:163 base.py:945 INFO] PRAGMA index_list(myview) [20110225 11:09:11:163 base.py:946 INFO] () [20110225 11:09:11:163 base.py:945 INFO] SELECT myview.z, myview.d FROM myview [20110225 11:09:11:163 base.py:946 INFO] [] (u'a', datetime.date(2011, 2, 10)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Unable to create a view that has where condition
I am unable to create a view if I put a where condition See below for error. However I am able to create a view if I do not have a where condition. Any idea why this restriction is posed by Sql Alchemy? = mySelect = select([table], table.c.vt_start == '20101211') engine.execute(CREATE TEMP VIEW myview AS %s % mySelect.compile(engine)); == Stack Trace: = engine.execute(CREATE TEMP VIEW myview AS %s % mySelect.compile(engine)); File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 888, in _execute_text return self.__execute_context(context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.OperationalError: (OperationalError) parameters are not allowed in views 'CREATE TEMP VIEW myview AS SELECT eee.z, eee.tt_start, eee.tt_end, eee.vt_start, eee.vt_end \nFROM eee \nWHERE eee.vt_start = ?' () -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Unable to select on Table subclass
I wrote a simple subclass of Table class MyTable(Table): def __init__(self, name, metadata, *args, **kwargs): super(MyTable, self).__init__(name, metadata, *args, **kwargs) def select(self, whereclause=None): return super(BitemporalTable, self).select(self.c.z 1) == select(t) where t is an object of MyTable is giving this error. What is the solution... Traceback (most recent call last): File t.py, line 16, in module select(t) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/sql/ expression.py, line 246, in select s = Select(columns, whereclause=whereclause, from_obj=from_obj, **kwargs) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/sql/ expression.py, line 3239, in __init__ [_literal_as_column(c) for c in columns] TypeError: 'MyTable' object is not iterable -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unable to select on Table subclass
def select(self, whereclause=None): return super(MyTable, self).select(self.c.z 1) You can ignore this method. This error comes with out this method also. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
Thanks a lot for bearing with me. explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) I still want a clarification about the above case. When we use explicit execution like the above, will each conn.execute() statement be in a transaction or not? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
I am not an expert in these concepts, so just trying to make sure I understand what you said. 1. If I use connection.execute(), then then every sql statements is not put in its own transactions. 2. But If I use connection-less execution like table.execute or engine.execute() then every statement is put in its own transaction. Can you confirm if the above understanding is correct. Also what is the DBAPI that you generally refer to ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
If I dont use autocommit:True option, it seems the driver will be in a chained transaction mode and results in every single statement (including selects) being run in a new transaction. This is not desirable either. Is there a way out ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
I have put a self contained script that can probably reproduce this for you. I think that the argument 'connect_args': {'autocommit' : True } is causing the transactions to not work properly. Is this expected and if so can you explain the reason. Thanks in advance. from sqlalchemy import * from sqlalchemy.sql import * def main(): Main body of the script. meta= MetaData() kwargs = { 'echo' : True, 'module_name' : 'pyodbc', 'connect_args': {'autocommit' : True }, } engine = create_engine(mssql://login:password@/database? driver=FreeTDSdsn=DBDEV, **kwargs) connection = engine.connect() meta.bind = engine table = Table(bbb, meta, Column('id', Integer, primary_key=True), Column('name', String), Column('tt_start', Date, primary_key=True), Column('tt_end', Date, primary_key=True), Column('vt_start', Date, primary_key=True), Column('vt_end', Date, primary_key=True)) table.create() for row in connection.execute(select([table])): print row trans = connection.begin() try: ins = table.insert().values(id=122, name='k', tt_start='20100101', tt_end='20100101', vt_start='20100101', vt_end='20100101') connection.execute(ins) ins = table.insert().values(id=121, name='k') connection.execute(ins) trans.commit() except: trans.rollback() for row in connection.execute(select([table])): print row raise if __name__ == __main__: main() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Return Custom Date objects instead of python inbuilt datetime.datetime objects
Sql alchemy returns datetime.datetime objects upon querying a datetime column. We have our own custom datetime classes and I would like Sql alchemy to understand or return these custom datetime objects instead of datetime.datetime objects. I would also like sql alchemy to understand our custom datetime objects while constructing the query. How can I teach sql alchemy to do all these? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] SQLalchemy Date type creates Datetime column
I am using sqlalchemy 0.5 with mssql server 2008. I see that when I want to create a 'Date' type column, sql alchemy creates a datetime column instead? Is this a bug in sql alchemy? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] rollback not working
rollback is not working for me. I wrote a small test script that inserts two rows, with the second row creating a violation. Upon throwing exception, I would expect no rows to be inserted due to rollback, but I find that the first insert is persisted. The surprising thing is that the echo output clearly says that sqlalchemy has rollbacked the transaction. Please help. == CODE: = trans = connection.begin() try: ins = table.insert().values(id=122, name='k', tt_start='20100101', tt_end='20100101', vt_start='20100101', vt_end='20100101') connection.execute(ins) ins = table.insert().values(id=121, name='k') connection.execute(ins) trans.commit() except: trans.rollback() raise == OUTPUT: === [20110112 19:15:51:203 base.py:723 INFO] BEGIN INSERT INTO bbb (id, name, tt_start, tt_end, vt_start, vt_end) VALUES (?, ?, ?, ?, ?, ?) [20110112 19:15:51:205 base.py:945 INFO] INSERT INTO bbb (id, name, tt_start, tt_end, vt_start, vt_end) VALUES (?, ?, ?, ?, ?, ?) [20110112 19:15:51:205 base.py:946 INFO] [122, 'k', '20100101', '20100101', '20100101', '20100101'] [20110112 19:15:51:502 base.py:945 INFO] INSERT INTO bbb (id, name) VALUES (?, ?) [20110112 19:15:51:502 base.py:946 INFO] [121, 'k'] [20110112 19:15:51:531 base.py:733 INFO] ROLLBACK Traceback (most recent call last): File test_trans.py, line 48, in module main() File test_trans.py, line 39, in main t.connection.execute(ins) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /prod/tools/infra/SQLAlchemy-0.5.5-py2.5.egg/sqlalchemy/engine/ base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', [23000] [FreeTDS][SQL Server]Cannot insert the value NULL into column 'tt_start', table 'IAM_Equities.dbo.bbb'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW)) u'INSERT INTO bbb (id, name) VALUES (?, ?)' [121, 'k'] -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
FYI I am using sqlserver 2008 and sqlalchemy 0.5 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.