[sqlalchemy] How do you get the underlying sql (without executing it) from a Statement or a compiled_statement?
Is there any way to have sqlalchemy return the sql that it is going to execute without actually executing it? I'm using the statement class. Vineet --~--~-~--~~~---~--~~ 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: How do you get the underlying sql (without executing it) from a Statement or a compiled_statement?
When I print the statement it just prints the query with placeholders for the paramters (and not the actual parameters). I would like to get the actual sql that it is going to execute. Vineet On Apr 30, 10:36 am, Gaetan de Menten [EMAIL PROTECTED] wrote: Just use print your_statement_variable Or, if you need it in a variable: str(your_statement_variable) should do the trick. On 4/30/07, vinjvinj [EMAIL PROTECTED] wrote: Is there any way to have sqlalchemy return the sql that it is going to execute without actually executing it? I'm using the statement class. Vineet -- Gaëtan de Mentenhttp://openhex.org --~--~-~--~~~---~--~~ 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: Data from mysql db not returned by query
each query, or just returning it to the connection pool via close()
(which also calls rollback()) and then re-acquiring it from the pool
as needed.
I have a wrapper function called execute() whcih traps any errors and
then recreates the mysql engine object and tries to resubmit the qry:
def initialize_connection():
mysql.db = create_engine('mysql://%s:[EMAIL PROTECTED]/%s' %
(config.DB_USER,
config.DB_PASS, config.DB_HOST, config.DB_NAME))
mysql.conn = mysql.db.connect()
initialize_connection()
def execute(qry):
try:
_rows = mysql.conn.execute(qry)
except:
initialize_connection()
_rows = mysql.conn.execute(qry)
rows = []
for i, row in enumerate(_rows):
row_dict = attrdict()
if i == 0:
column_headers = row.keys()
for key in column_headers:
row_dict[key] = getattr(row, key)
rows.append(row_dict)
return rows
Should I add:
mysql.conn = mysql.db.connect()
mysq.close()
at the beginning and end of the function?
I convert the result set to a list of dicts since I get not compress
and pickle the objects being returned by sqlalchemy.
Thanks,
Vineet
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
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: Data from mysql db not returned by query
conn = mysql.db.connect() do stuff with conn conn.close() Thanks. That fixed the problem. Is sqlalchemy using a connection pool when I do this or is a new connection opened and closed each time? Vineet --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] 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: How would you build a mapper with a calculated field based on a join from two tables
a = user_table() a.a_column_in_user_table_1 + a.additional_info.your_column My logic is not as simple as (a.a_column_in_user_table_1 + a.additional_info.your_column). It's about 20 lines of code. Also I'm trying to understand how you would add additional attributes to a python User object which is set up by the sqlalchemy mysql object. Vineet --~--~-~--~~~---~--~~ 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] How would you build a mapper with a calculated field based on a join from two tables
Say I have a python class defined:
def class User(object):
get_calculated_field(self):
return self.a_column_in_user_table_1 +
self.a_column_in_additional_info
user_table = Table('user_table', metadata, autoload=True)
additional_info = Table('additional_info', metadata, autoload=True)
Now I need to do the following:
1. Add an additional read only attribute to the User object which is
calculated from columns on the user_table and additional_info table
2. Be able to generate a join between a user_table and additional_info
table and then for each row that is returned by the db, have the User
object add calculated_field as a read only attribute (by calling the
function get_calculated_field)
Thanks,
Vineet
--~--~-~--~~~---~--~~
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] Best practices for database connection errors
Hi, In my application I'm working with three different databases. Case 1: For 2 databases I'm using the connection object directly execute sql and Case 2: In the 3rd database I'm using the statement object and then doing the statement.execute, where the db is implicitly associated with the statement through the columns. What is the best way to handle database connection errors in both the cases above. Ideally you would want the database layer to recreate the connection and then try to execute the statement again. Thanks, Vineet --~--~-~--~~~---~--~~ 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: Best practices for database connection errors
throw consistent errors at consistent points of execution. if you
think your database was stopped and is now re-started, you can call
invalidate() on an individual connection and then close() it for it
to be replaced in the pool, or more likely you can call dispose() on
your engine and it will load up a new connection pool upon the next
usage.
So where should I put this logic. In the past I would have created
wrappers around the db.execute command and in that I would have
checked if there were any errors. If there were any errors I would try
to see if there were any connection related errors and then try to
reconect and then resubmit the sql statement.
However, I'm a little confused on how I would do this with sql
alchemy. More sepcifically with all the introspection that goes on.
For instance for one of my connections I do the following:
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
So would I do the following:
connect_to_db():
[recreate the mysqlDb engine]
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
execute_statement(statement):
try:
statement.execute()
except: [check for connection related errors]
connect_to_db
statement.execute()
Or,
I'm assuming the above is not enough, because all the other views
would be invalidated as well.
--~--~-~--~~~---~--~~
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: Cannot connect to Oracle DB: AttributeError: 'module' object has no attribute 'NCLOB'
Thanks that solved the problem. Vineet On Mar 6, 6:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: old bug, upgrade to sqlalchemy 0.3.5 On Mar 6, 2007, at 5:51 PM, vinjvinj wrote: I get the following error: File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 266, in execute File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 271, in execute_t ext File build\bdist.win32\egg\sqlalchemy\databases\oracle.py, line 326, in crea te_result_proxy_args AttributeError: 'module' object has no attribute 'NCLOB' When I try to connect to the oracle DB. I have cx_oracle module installed. Any ideas what I'm doing wrong. Thanks, VJ --~--~-~--~~~---~--~~ 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] MS Sql Server: Cannot create new connection because in manual or distributed
My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: MS Sql Server: Cannot create new connection because in manual or distributed
This link explains the problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;272358 Unfortunately, I'm not sure what the solution is. I've already tried to create a transaction in a session. VJ On Mar 7, 2:05 pm, vinjvinj [EMAIL PROTECTED] wrote: My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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: MS Sql Server: Cannot create new connection because in manual or distributed
I figured the problem out: I was doing the following: rows_from_sql1 = db_conn.execute(select1) rows_from_sql2 = db_conn.execute(select2) You can't do the above since rows_from_sql1 is an iterator. You have to iterate through all the rows before you can execute the second query. I can potentially see certain situations where you would have to be able to execute two queries before you iterate through them all. Are there any work around this? Thanks, VJ On Mar 7, 4:05 pm, Rick Morrison [EMAIL PROTECTED] wrote: This is an ADODBAPI /OLEDB issue. Solution is to either use client-side cursor for the first query, or to fetch all the results from the first query before issuing another one on another connection. Just so you know, there are others working on pyodbc for the MSSQL SA module, which I think (and hope) is not subject to this problem. Neither is pymssql. Rick On 3/7/07, vinjvinj [EMAIL PROTECTED] wrote: This link explains the problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;272358 Unfortunately, I'm not sure what the solution is. I've already tried to create a transaction in a session. VJ On Mar 7, 2:05 pm, vinjvinj [EMAIL PROTECTED] wrote: My first query works but the second query gets this error. I'm using the db_conn.execute function to execute direct sql. I also tried to create a session object and explicitly create a transaction and commit the transaction thinking that might solve the problem. However, that did not fix the problem. VJ Exception: adoRetVal=self.cmd.Execute() File COMObject ADODB.Command, line 3, in Execute File C:\Python24\Lib\site-packages\win32com\client\dynamic.py, line 258, in _ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes ) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) --~--~-~--~~~---~--~~ 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 we include this new url traversal with cherrypy?
There was a post about a new url traversal implemented in turbogears on top of cherrypy (with ideas being borrowed from Nevow): https://projects.isotoma.com/tgnewtraversal Looks very cool indeed. Is there any way to integrate this with the cherrypy distribution? Vineet --~--~-~--~~~---~--~~ 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: How do I get columns in sqlalchemy group by function
Thanks. On Feb 27, 11:20 am, Jonathan Ellis [EMAIL PROTECTED] wrote: group_by_column.clauses[0] is the object you passed to func.sum On 2/27/07, vinjvinj [EMAIL PROTECTED] wrote: I have the following columns in some of my select columns: group_by_column = sqlalchemy.func.sum(column1) How do I get column1 from the group_by_column object (In other words is there any way to find out what columns the function is going to be applied to?). I did a dir(group_by_column) and saw that it had a field called columns, but that did not have the values I was looking for. Thanks, VJ --~--~-~--~~~---~--~~ 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] How do I get columns in sqlalchemy group by function
I have the following columns in some of my select columns: group_by_column = sqlalchemy.func.sum(column1) How do I get column1 from the group_by_column object (In other words is there any way to find out what columns the function is going to be applied to?). I did a dir(group_by_column) and saw that it had a field called columns, but that did not have the values I was looking for. Thanks, VJ --~--~-~--~~~---~--~~ 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] Get error: 'list' object has no attribute 'accept_visitor'
I'm getting the following error when I build the select clause. select_columns = [table.c.col1, table.c.col2] statement = table.select(select_columns) File build\bdist.win32\egg\sqlalchemy\sql.py, line 1351, in select File build\bdist.win32\egg\sqlalchemy\sql.py, line 65, in select File build\bdist.win32\egg\sqlalchemy\sql.py, line 1503, in __init__ File build\bdist.win32\egg\sqlalchemy\sql.py, line 1575, in append_whereclause File build\bdist.win32\egg\sqlalchemy\sql.py, line 1581, in _append_condition AttributeError: 'list' object has no attribute 'accept_visitor' Any idea what this means? VJ --~--~-~--~~~---~--~~ 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: Get error: 'list' object has no attribute 'accept_visitor'
Ok. I need to do the following: select col1, col2, sum(col3), sum(col4) where col1 = test group by col1, col2 Q1: How do I add sum functions in the column list Q2: Do I add group_by using the group_by parameter on the sqlalchemy.select call Q3: Do I add where conditions to the select using statement.append_whereclause So from what you are saying I would do the following: select_columns = [table.c.col1, table.c.col2] statement = sqlalchemy.select(select_columns, group_by=select_columns) statement.append_whereclause(table.c.col1 == test) res = statement.execute().fetchall() Thanks, VJ On Feb 20, 10:18 am, Sébastien LELONG [EMAIL PROTECTED] securities.fr wrote: select_columns = [table.c.col1, table.c.col2] statement = table.select(select_columns) You probably want to: select_columns = [table.c.col1, table.c.col2] statement = sqlalchemy.select(select_columns) res = statement.execute().fetchall() # or the like... The table.select form you try to get working is used to specify eg. where clauses, not to specify which columns to get. -- Sébastien LELONG sebastien.lelong[at]sirloon.net On Tuesday 20 February 2007 16:10, vinjvinj wrote: I'm getting the following error when I build the select clause. select_columns = [table.c.col1, table.c.col2] statement = table.select(select_columns) File build\bdist.win32\egg\sqlalchemy\sql.py, line 1351, in select File build\bdist.win32\egg\sqlalchemy\sql.py, line 65, in select File build\bdist.win32\egg\sqlalchemy\sql.py, line 1503, in __init__ File build\bdist.win32\egg\sqlalchemy\sql.py, line 1575, in append_whereclause File build\bdist.win32\egg\sqlalchemy\sql.py, line 1581, in _append_condition AttributeError: 'list' object has no attribute 'accept_visitor' Any idea what this means? VJ --~--~-~--~~~---~--~~ 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: Support for SQL views?
Views as normal tables work with mysql. view as a table (using autoload). The application I currently work on --~--~-~--~~~---~--~~ 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] Is there any way to call a python function for primary key generation
I use the mx.UID package to generate all my keys. Is there any way to specify that this function be called to generate the primary key for inserts? Thanks, VJ --~--~-~--~~~---~--~~ 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: Is there any way to call a python function for primary key generation
Found the answer:
# a function to create primary key ids
i = 0
def mydefault():
global i
i += 1
return i
t = Table(mytable, meta,
# function-based default
Column('id', Integer, primary_key=True, default=mydefault),
# a scalar default
Column('key', String(10), default=default)
)
Thanks,
VJ
On Feb 13, 1:18 pm, vinjvinj [EMAIL PROTECTED] wrote:
I use the mx.UID package to generate all my keys. Is there any way to
specify that this function be called to generate the primary key for
inserts?
Thanks,
VJ
On Feb 13, 1:18 pm, vinjvinj [EMAIL PROTECTED] wrote:
I use the mx.UID package to generate all my keys. Is there any way to
specify that this function be called to generate the primary key for
inserts?
Thanks,
VJ
--~--~-~--~~~---~--~~
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: Is there any way to call a python function for primary key generation
This counter is going to start over every time you run your program. The second time you run it, it's going to start creating IDs that already exist, You missed my first post, which stated: I use the mx.UID package to generate all my keys. Is there any way to specify that this function be called to generate the primary key for inserts? I just needed to know how to call UID on inserts. Thanks, VJ --~--~-~--~~~---~--~~ 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: Is there any way to call a python function for primary key generation
Nice thing about using mx.UID package is that it is portable between database. The UID that is generated has a portion that is derived from the machine and time, so it is always going to be unique. On Feb 13, 8:52 pm, Sean Davis [EMAIL PROTECTED] wrote: On 2/13/07, vinjvinj [EMAIL PROTECTED] wrote: I use the mx.UID package to generate all my keys. Is there any way to specify that this function be called to generate the primary key for inserts? Thanks, Could this not be done on the database side (depends on the RDBMS)? Sean --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
