RE: [sqlalchemy] Kerberos authentication with sybase
Behind the scenes the c library calls ct_con_props C function: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mainframeconnect_12.6.occprc/html/occprc/X38419.htm You can find the properties at: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mainframeconnect_12.6.occprc/html/occprc/occprc46.htm And at: http://infocenter.sybase.com/help/topic/com.sybase.help.sdk_12.5.1.ctref/html/ctref/X29866.htm I think it you want to make it flexible there should be a dictionary or a two-dimensional list specifying which options should be set, so in case of kerberos it would have two elements. Unfortunatelly these options cannot be specified for the connect() function of python-sybase. Zsolt -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Friday, June 11, 2010 17:07 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Kerberos authentication with sybase On Jun 11, 2010, at 4:50 AM, Cserna, Zsolt wrote: The DBAPI is python-sybase (http://python-sybase.sourceforge.net/). Here is an example: conn = Sybase.connect(hostname, , , delay_connect=1) conn.set_property(Sybase.CS_SEC_NETWORKAUTH, Sybase.CS_TRUE) conn.set_property(Sybase.CS_SEC_SERVERPRINCIPAL, principal) conn.connect() The variable principal would come from connection string. OK that is fine, is the above part of some larger pattern of connection styles ? like is there some other series of things I can do ater setting NETWORKAUTH to TRUE , other options ? if we want to make a feature out of this, it would be best to suit the whole range of use cases. Zsolt -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, June 10, 2010 15:58 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Kerberos authentication with sybase if you want to show me how that's done with your DBAPI (what DBAPI is this?), sure, though if its something that you pass in via **kw to DBAPI.connect(), you can already do that by passing connect_args to create_engine(), or adding them onto the querystring db://...?foo=bar. On Jun 10, 2010, at 9:10 AM, Cserna, Zsolt wrote: Hi all, Is there any plan to add kerberos authentication to the sybase engine in sqlalchemy? I've implemented it but it's using the creator parameter of the create_engine function, which is ok, but in certain circumstances when the application using sqlalchemy uses configuration from a text file, I'm not able to do it easily (in pylons I need to modify my templates, or add a bunch of code to each project). It would be nice if sqlalchemy would be able to accept the server's principal specified in the connection string somehow - and if it's specified use kerberos to authenticate the client. Is it possible to implement this modification in sqlalchemy? I'm happy to contribute my current implementation. Thanks, Zsolt - - NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law. -- 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. -- 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. -- NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law. -- 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. -- You received this message because you are subscribed to the
[sqlalchemy] Unexpected SessionTransaction behaviour
I posted earlier[1] about an threading anomaly I am getting relating to SQLite with pooling, and was advised to use NullPool. However, it seems like session.remove() for a scoped session is not always cleaning up things as it should, the problem only showing up with SQLite because of its constraints on threaded usage. So I set up to run under a development server (single threaded) and added some additional logging statements to pool.py, orm/session.py and orm/ scoping.py to try to see what was going on. I have tested with two identical requests to a web app. Processing this request results in a fetch of a User (mapped class) given its ID (stored in an HTTP session), followed by some fetches of data to populate comboboxes in the response. There are only SELECT queries - no INSERT/UPDATE/DELETE queries. For the first request, everything appears to behave as expected. For the second (identical) request, an extra checkout is done and a phantom SessionTransaction appears, which results in the connection not being returned to the pool at the end of the request. I'd be grateful for some pointers to what is happening - is it what would be expected, and why are the two identical requests resulting in different flow through SQLA code? Annotated log output: [ersatz.core:706] DEBUG dispatching request GET http://localhost:5000/admin/book/add/ [ersatz.core:677] DEBUG locales: ['en_GB', 'es_ES'] ... [ersatz.contrib.dbauth:539] DEBUG Querying user via id [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9bc922c [sqlalchemy.orm.session:310] DEBUG SessionTransaction._connection_for_bind: not in connections: SessionTransaction/0x9bc922c, Engine(sqlite:tmp/demo1.db), [] As you would expect, there's no connection for this request, so one is created, checked out from the pool. Likewise, a SessionTransaction is created. [sqlalchemy.orm.session:323] DEBUG SessionTransaction._connection_for_bind: calling bind.contextual_connect [sqlalchemy.pool.SingletonThreadPool.0x...338c:319] DEBUG _ConnectionFairy.__init__ [sqlalchemy.pool.SingletonThreadPool.0x...338c:275] DEBUG Created new connection sqlite3.Connection object at 0x9a3a7a0 [sqlalchemy.pool.SingletonThreadPool.0x...338c:333] DEBUG Connection sqlite3.Connection object at 0x9a3a7a0 checked out from pool [sqlalchemy.pool.SingletonThreadPool.0x...338c:387] DEBUG _ConnectionFairy.checkout: counter = 1 There follow the calls to populate the comboboxes; presumably the existing connection is used, so we don't see any pool-related logging messages. [ersatz.db:904] DEBUG DBMultiSelectField: get_choices: authors [ersatz.db:879] DEBUG DBSelectField: get_choices: publisher [ersatz.db:904] DEBUG DBMultiSelectField: get_choices: tags The request processing is now complete, and cleanup is done using session.remove(). [ersatz.core:747] DEBUG 0.335: time for http://localhost:5000/admin/book/add/ The scoped session closes the session which is stored in a thread- local: [sqlalchemy.orm.scoping:61] DEBUG ScopedSession.remove registry has, closing: sqlalchemy.orm.session.Session object at 0x9bc908c [sqlalchemy.orm.session:762] DEBUG Session.close Which in turn closes the SessionTransaction object created earlier. [sqlalchemy.orm.session:766] DEBUG Session.close: closing transaction: sqlalchemy.orm.session.SessionTransaction object at 0x9bc922c [sqlalchemy.pool.SingletonThreadPool.0x...338c:432] DEBUG _ConnectionFairy.close: counter = 1 Because the refcount for the connection is 1, an underlying _close is called which returns the connection to the pool. [sqlalchemy.pool.SingletonThreadPool.0x...338c:438] DEBUG _ConnectionFairy._close [sqlalchemy.pool.SingletonThreadPool.0x...338c:283] DEBUG _finalize_fairy: sqlite3.Connection object at 0x9a3a7a0 [sqlalchemy.pool.SingletonThreadPool.0x...338c:304] DEBUG Connection sqlite3.Connection object at 0x9a3a7a0 being returned to pool [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9c814cc During the request processing above, SQLA behaves in an understandable way. Now for the second request, things don't go the same way, which is the thing that puzzles me. [ersatz.core:706] DEBUG dispatching request GET http://localhost:5000/admin/book/add/ [ersatz.core:677] DEBUG locales: ['en_GB', 'es_ES'] ... [ersatz.contrib.dbauth:539] DEBUG Querying user via id [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9c8448c [sqlalchemy.orm.session:310] DEBUG SessionTransaction._connection_for_bind: not in connections: SessionTransaction/0x9c8448c, Engine(sqlite:tmp/demo1.db), [] As before, there's no connection yet for this request, so one is created, checked out from the pool. Likewise, a SessionTransaction is created. [sqlalchemy.orm.session:323] DEBUG SessionTransaction._connection_for_bind: calling bind.contextual_connect [sqlalchemy.pool.SingletonThreadPool.0x...338c:319] DEBUG _ConnectionFairy.__init__ [sqlalchemy.pool.SingletonThreadPool.0x...338c:333] DEBUG Connection sqlite3.Connection
Re: [sqlalchemy] Kerberos authentication with sybase
On Jun 14, 2010, at 5:16 AM, Cserna, Zsolt wrote: Behind the scenes the c library calls ct_con_props C function: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mainframeconnect_12.6.occprc/html/occprc/X38419.htm You can find the properties at: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mainframeconnect_12.6.occprc/html/occprc/occprc46.htm And at: http://infocenter.sybase.com/help/topic/com.sybase.help.sdk_12.5.1.ctref/html/ctref/X29866.htm I think it you want to make it flexible there should be a dictionary or a two-dimensional list specifying which options should be set, so in case of kerberos it would have two elements. Unfortunatelly these options cannot be specified for the connect() function of python-sybase. the goal here is so that the options can all be embedded in the URL at least as key/value pairs. How would the Sybase.XXX symbols be embedded ? Zsolt -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Friday, June 11, 2010 17:07 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Kerberos authentication with sybase On Jun 11, 2010, at 4:50 AM, Cserna, Zsolt wrote: The DBAPI is python-sybase (http://python-sybase.sourceforge.net/). Here is an example: conn = Sybase.connect(hostname, , , delay_connect=1) conn.set_property(Sybase.CS_SEC_NETWORKAUTH, Sybase.CS_TRUE) conn.set_property(Sybase.CS_SEC_SERVERPRINCIPAL, principal) conn.connect() The variable principal would come from connection string. OK that is fine, is the above part of some larger pattern of connection styles ? like is there some other series of things I can do ater setting NETWORKAUTH to TRUE , other options ? if we want to make a feature out of this, it would be best to suit the whole range of use cases. Zsolt -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, June 10, 2010 15:58 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Kerberos authentication with sybase if you want to show me how that's done with your DBAPI (what DBAPI is this?), sure, though if its something that you pass in via **kw to DBAPI.connect(), you can already do that by passing connect_args to create_engine(), or adding them onto the querystring db://...?foo=bar. On Jun 10, 2010, at 9:10 AM, Cserna, Zsolt wrote: Hi all, Is there any plan to add kerberos authentication to the sybase engine in sqlalchemy? I've implemented it but it's using the creator parameter of the create_engine function, which is ok, but in certain circumstances when the application using sqlalchemy uses configuration from a text file, I'm not able to do it easily (in pylons I need to modify my templates, or add a bunch of code to each project). It would be nice if sqlalchemy would be able to accept the server's principal specified in the connection string somehow - and if it's specified use kerberos to authenticate the client. Is it possible to implement this modification in sqlalchemy? I'm happy to contribute my current implementation. Thanks, Zsolt - - NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law. -- 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. -- 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. -- NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law. -- 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
[sqlalchemy] SA-oracle bitwise operator
Hi
I am using SA-oracle for my application.
I need to write an oracle query in which i need to use BITAND
oracle bitwise operator
function.
Is SA provides this functionality?
DECODE(BITAND(task_calendars.day ,day1)))
The SA QUERY is
SELECT task_calendars.cal_id AS task_calendars_cal_id,
task_calendars.task_id AS task_calendars_task_id, task_calendars.dow
AS task_calendars_dow, task_calendars.month AS task_calendars_month,
task_calendars.day AS task_calendars_day, task_calendars.hour AS
task_calendars_hour, task_calendars.minute AS task_calendars_minute,
tasks_1.task_id AS tasks_1_task_id, tasks_1.task_type AS
tasks_1_task_type, tasks_1.name AS tasks_1_name, tasks_1.entity_id AS
tasks_1_entity_id, tasks_1.entity_name AS tasks_1_entity_name,
tasks_1.context AS tasks_1_context, tasks_1.params AS tasks_1_params,
tasks_1.kw_params AS tasks_1_kw_params, tasks_1.processors AS
tasks_1_processors, tasks_1.user_name AS tasks_1_user_name,
tasks_1.submitted_on AS tasks_1_submitted_on, tasks_1.repeating AS
tasks_1_repeating, tasks_1.cancellable AS tasks_1_cancellable \nFROM
task_calendars LEFT OUTER JOIN tasks tasks_1 ON tasks_1.task_id =
task_calendars.task_id
WHERE (task_calendars.dow = :dow_1 OR (task_calendars.dow :dow_2))
:param_1' {'dow_2': 1, 'dow_1': 0, 'param_1': False}
I would appreciate any help.
Thanks
--
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.
Re: [sqlalchemy] Unexpected SessionTransaction behaviour
On Jun 14, 2010, at 9:56 AM, Vinay Sajip wrote: I posted earlier[1] about an threading anomaly I am getting relating to SQLite with pooling, and was advised to use NullPool. However, it seems like session.remove() for a scoped session is not always cleaning up things as it should, the problem only showing up with SQLite because of its constraints on threaded usage. So I set up to run under a development server (single threaded) and added some additional logging statements to pool.py, orm/session.py and orm/ scoping.py to try to see what was going on. I have tested with two identical requests to a web app. Processing this request results in a fetch of a User (mapped class) given its ID (stored in an HTTP session), followed by some fetches of data to populate comboboxes in the response. There are only SELECT queries - no INSERT/UPDATE/DELETE queries. For the first request, everything appears to behave as expected. For the second (identical) request, an extra checkout is done and a phantom SessionTransaction appears, which results in the connection not being returned to the pool at the end of the request. I'd be grateful for some pointers to what is happening - is it what would be expected, and why are the two identical requests resulting in different flow through SQLA code? Annotated log output: [ersatz.core:706] DEBUG dispatching request GET http://localhost:5000/admin/book/add/ [ersatz.core:677] DEBUG locales: ['en_GB', 'es_ES'] ... [ersatz.contrib.dbauth:539] DEBUG Querying user via id [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9bc922c [sqlalchemy.orm.session:310] DEBUG SessionTransaction._connection_for_bind: not in connections: SessionTransaction/0x9bc922c, Engine(sqlite:tmp/demo1.db), [] As you would expect, there's no connection for this request, so one is created, checked out from the pool. Likewise, a SessionTransaction is created. [sqlalchemy.orm.session:323] DEBUG SessionTransaction._connection_for_bind: calling bind.contextual_connect [sqlalchemy.pool.SingletonThreadPool.0x...338c:319] DEBUG _ConnectionFairy.__init__ [sqlalchemy.pool.SingletonThreadPool.0x...338c:275] DEBUG Created new connection sqlite3.Connection object at 0x9a3a7a0 [sqlalchemy.pool.SingletonThreadPool.0x...338c:333] DEBUG Connection sqlite3.Connection object at 0x9a3a7a0 checked out from pool [sqlalchemy.pool.SingletonThreadPool.0x...338c:387] DEBUG _ConnectionFairy.checkout: counter = 1 There follow the calls to populate the comboboxes; presumably the existing connection is used, so we don't see any pool-related logging messages. [ersatz.db:904] DEBUG DBMultiSelectField: get_choices: authors [ersatz.db:879] DEBUG DBSelectField: get_choices: publisher [ersatz.db:904] DEBUG DBMultiSelectField: get_choices: tags The request processing is now complete, and cleanup is done using session.remove(). [ersatz.core:747] DEBUG 0.335: time for http://localhost:5000/admin/book/add/ The scoped session closes the session which is stored in a thread- local: [sqlalchemy.orm.scoping:61] DEBUG ScopedSession.remove registry has, closing: sqlalchemy.orm.session.Session object at 0x9bc908c [sqlalchemy.orm.session:762] DEBUG Session.close Which in turn closes the SessionTransaction object created earlier. [sqlalchemy.orm.session:766] DEBUG Session.close: closing transaction: sqlalchemy.orm.session.SessionTransaction object at 0x9bc922c [sqlalchemy.pool.SingletonThreadPool.0x...338c:432] DEBUG _ConnectionFairy.close: counter = 1 Because the refcount for the connection is 1, an underlying _close is called which returns the connection to the pool. [sqlalchemy.pool.SingletonThreadPool.0x...338c:438] DEBUG _ConnectionFairy._close [sqlalchemy.pool.SingletonThreadPool.0x...338c:283] DEBUG _finalize_fairy: sqlite3.Connection object at 0x9a3a7a0 [sqlalchemy.pool.SingletonThreadPool.0x...338c:304] DEBUG Connection sqlite3.Connection object at 0x9a3a7a0 being returned to pool [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9c814cc During the request processing above, SQLA behaves in an understandable way. Now for the second request, things don't go the same way, which is the thing that puzzles me. [ersatz.core:706] DEBUG dispatching request GET http://localhost:5000/admin/book/add/ [ersatz.core:677] DEBUG locales: ['en_GB', 'es_ES'] ... [ersatz.contrib.dbauth:539] DEBUG Querying user via id [sqlalchemy.orm.session:228] DEBUG SessionTransaction.__init__: 0x9c8448c [sqlalchemy.orm.session:310] DEBUG SessionTransaction._connection_for_bind: not in connections: SessionTransaction/0x9c8448c, Engine(sqlite:tmp/demo1.db), [] As before, there's no connection yet for this request, so one is created, checked out from the pool. Likewise, a SessionTransaction is created. [sqlalchemy.orm.session:323] DEBUG SessionTransaction._connection_for_bind: calling bind.contextual_connect
Re: [sqlalchemy] SA-oracle bitwise operator
please use func. for database functions.
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=func#sqlalchemy.sql.expression.func
On Jun 14, 2010, at 10:09 AM, dhanil anupurath wrote:
Hi
I am using SA-oracle for my application.
I need to write an oracle query in which i need to use BITAND
oracle bitwise operator
function.
Is SA provides this functionality?
DECODE(BITAND(task_calendars.day ,day1)))
The SA QUERY is
SELECT task_calendars.cal_id AS task_calendars_cal_id,
task_calendars.task_id AS task_calendars_task_id, task_calendars.dow
AS task_calendars_dow, task_calendars.month AS task_calendars_month,
task_calendars.day AS task_calendars_day, task_calendars.hour AS
task_calendars_hour, task_calendars.minute AS task_calendars_minute,
tasks_1.task_id AS tasks_1_task_id, tasks_1.task_type AS
tasks_1_task_type, tasks_1.name AS tasks_1_name, tasks_1.entity_id AS
tasks_1_entity_id, tasks_1.entity_name AS tasks_1_entity_name,
tasks_1.context AS tasks_1_context, tasks_1.params AS tasks_1_params,
tasks_1.kw_params AS tasks_1_kw_params, tasks_1.processors AS
tasks_1_processors, tasks_1.user_name AS tasks_1_user_name,
tasks_1.submitted_on AS tasks_1_submitted_on, tasks_1.repeating AS
tasks_1_repeating, tasks_1.cancellable AS tasks_1_cancellable \nFROM
task_calendars LEFT OUTER JOIN tasks tasks_1 ON tasks_1.task_id =
task_calendars.task_id
WHERE (task_calendars.dow = :dow_1 OR (task_calendars.dow :dow_2))
:param_1' {'dow_2': 1, 'dow_1': 0, 'param_1': False}
I would appreciate any help.
Thanks
--
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.
--
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] Mapeamento de campo Real do PostGreSQL.
Olá, Sou novo no Python, estou usando o Pylons e PostGreeSQL, estou com dificuldade para mapear no pylons os campos que no postgre são definidos no tipo Real. Alguém tem algum exemplo, já perguntei ao google mas não encontrei nada específico sobre isso... Valeu... -- 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.
Re: [sqlalchemy] Abridged summary of sqlalchemy@googlegroups.com - 14 Messages in 7 Topics
Aa Sent from my iPhone On Jun 14, 2010, at 12:39 AM, sqlalchemy+nore...@googlegroups.com sqlalchemy+nore...@googlegroups.com wrote: Today's Topic Summary Group: http://groups.google.com/group/sqlalchemy/topics - Multiple databases or multiple schemas? #group_thread_0 [2 Updates] - Error trying to use session.execute #group_thread_1 [4 Updates] - Windows and Linux Tips #group_thread_2 [1 Update] - Questions about session #group_thread_3 [1 Update] - Where can I learn more #group_thread_4 [2 Updates] - Using SQL aggregate fcns on a PGArray column #group_thread_5 [2 Updates] - Add a unit to column #group_thread_6 [2 Updates] Topic: Multiple databases or multiple schemas?http://groups.google.com/group/sqlalchemy/t/c93e1ab26c4ae5a6 Henry Precheur he...@precheur.org Jun 13 01:27PM -0700 ^ #digest_top I'm starting a new project which is roughly hosting a bunch of sites. I want the sites to be isolated, they'll all have the same schema (data definition), but will store their data on different more...http://groups.google.com/group/sqlalchemy/msg/92246cf43927e2b8 Faheem Mitha fah...@email.unc.edu Jun 14 02:53AM +0530 ^ #digest_top [This message has also been posted.] Solution #2 is not supported natively by SQLAlchemy. Each time a request comes-in I'll have to issue an additional query SET search_path TO more... http://groups.google.com/group/sqlalchemy/msg/839cc05d071b70cd Topic: Error trying to use session.executehttp://groups.google.com/group/sqlalchemy/t/3fd8f8ef8e0a546e Michael Bayer mike...@zzzcomputing.com Jun 12 07:58PM -0400 ^#digest_top this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's more...http://groups.google.com/group/sqlalchemy/msg/df5de254e3d22d43 Faheem Mitha fah...@email.unc.edu Jun 13 01:33PM +0530 ^ #digest_top [This message has also been posted.] On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer the difference in result is between the session's default of transactionalized execution and the more...http://groups.google.com/group/sqlalchemy/msg/c6f9b0eac14d3212 Faheem Mitha fah...@email.unc.edu Jun 13 12:51PM ^ #digest_top I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. [Following up to myself] Adding more... http://groups.google.com/group/sqlalchemy/msg/f23140f7980082f8 Michael Bayer mike...@zzzcomputing.com Jun 13 11:20AM -0400 ^#digest_top On Jun 13, 2010, at 4:03 AM, Faheem Mitha wrote: aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no When an exception more...http://groups.google.com/group/sqlalchemy/msg/867ef9217ee2689 Topic: Windows and Linux Tipshttp://groups.google.com/group/sqlalchemy/t/5b8270f1f9ce392e pavelhaque pavelha...@gmail.com Jun 13 05:01AM -0700 ^ #digest_top Hi friends, Do u need Windows and Linux Tips? Please visit the following site: http://windowsandlinuxtips.blogspot.com/ more... http://groups.google.com/group/sqlalchemy/msg/ab5809a268b14de9 Topic: Questions about sessionhttp://groups.google.com/group/sqlalchemy/t/9ed8ef9a193fbe5e Az azfarul.is...@gmail.com Jun 12 05:45PM -0700 ^ #digest_top Hi Conor, Many apologies for being pushy but since I'm pretty much in the processing of finishing up my code (due in two days), I wonder if you could just take a look at the last three posts of more...http://groups.google.com/group/sqlalchemy/msg/8dc1429e8fba0a64 Topic: Where can I learn morehttp://groups.google.com/group/sqlalchemy/t/a3036c1cf8b66187 Paul Hemans p_hem...@hotmail.com Jun 12 05:27PM -0700 ^ #digest_top Where can I learn more about creating a dialect for SQLalchemy? I could just copy one of the existing dialects, but I don't know what I am looking for in what needs to be changed. I need a kind of more...http://groups.google.com/group/sqlalchemy/msg/731aa49491b7ad01 Michael Bayer mike...@zzzcomputing.com Jun 12 08:39PM -0400 ^#digest_top Essential SQLAlchemy unfortunately is out of date in most areas and certainly with regards to dialect creation (as a note to anyone reading this, we are interested in authors for a new SQLAlchemy more...http://groups.google.com/group/sqlalchemy/msg/b9561f6d7316898e Topic: Using SQL aggregate fcns on a PGArray columnhttp://groups.google.com/group/sqlalchemy/t/901593fa025c327c David Korz david.k...@gmail.com Jun 11 05:55PM -0700 ^ #digest_top I have a table like such: CREATE TABLE test ( mykey integer not null, mydata double precision [2][10] ) without oids; with a table defn like: test = Table(test,metadata,
[sqlalchemy] Mapping Real field PostGreSQL.
Hello, I'm using Pylons and PostgreSQL, I'm difficulty in mapping in pylons fields that are in Postgres defined like type Real. Does anyone have any example, already asked google but not found nothing specific about it ... Thanks ... -- 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.
Re: [sqlalchemy] Multiple databases or multiple schemas?
On Jun 13, 2010, at 4:27 PM, Henry Precheur wrote:
I'm starting a new project which is roughly hosting a bunch of sites. I
want the sites to be isolated, they'll all have the same schema (data
definition), but will store their data on different 'name-spaces'.
The front end will be a python web app. And I'd like to have this
application talk to all the 'name-spaces' at the same time:
With a request on example.com/site1, the web app will use the
'name-space' site1, with example.com/site2 it will use site2.
I am using Postgres. There are 2 options for the 'name-space': Database
or Schema [1]:
1. One database per site
2. One database for all the sites and 1 schema per site
Solution #1 would require to maintain 1 connection per site per python
process. That means: lots of connections, lots of memory needed. One
the other hand, this solution is supported by SQLAlchemy out-of-the-box.
I'll have a dictionary like that:
{'site1': Engine('postgres://.../site1',
'site2': Engine('postgres://.../site2', ...}
And whenever a request comes in I get the right engine via this
dictionary.
Solution #2 is not supported natively by SQLAlchemy. Each time a request
comes-in I'll have to issue an additional query SET search_path TO
MY_SITE where MY_SITE is the schema associated with the site.
Solution #2 seems much more lightweight to me. The only problem is the
small overhead that might be created by the additional query.
What do you guys think? Will I get into trouble with solution #2?
If you have alternative suggestions I'd like to hear them :)
you can do the multi-schema thing using either a PoolListener, or a Table
subclass that implements a @property for the schema attribute - in both cases
these would be linked to a thread local variable that you would set per
request. If the load per site is low, you can also forego the usage of
pooling to eliminate the issue of dormant connections (possibly look into
something like pgpool, perhaps they have some feature that optimizes this use
case).
As far as approach, consider the load each per-user site will have, and how you
might scale out to include multiple database servers. I'd grep around the
PG docs and wiki carefully to get a full grasp of the implications of each
approach.
--
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] Primary key not unique?
I've got an error that says the primary key is not unique. This shouldn't be possible since my primary keys are unique IDs :S This is only happening after I switched to a physical sqlite3 database from the :memory: one #BEGIN# File Main.py, line 97, in module MCS.addToTable() File /XXX/MonteCarloSimulation.py, line 77, in addToTable session.flush() File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ session.py, line 1354, in flush self._flush(objects) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ session.py, line 1432, in _flush flush_context.execute() File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ unitofwork.py, line 261, in execute UOWExecutor().execute(self, tasks) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ unitofwork.py, line 753, in execute self.execute_save_steps(trans, task) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ unitofwork.py, line 768, in execute_save_steps self.save_objects(trans, task) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ unitofwork.py, line 759, in save_objects task.mapper._save_obj(task.polymorphic_tosave_objects, trans) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ mapper.py, line 1428, in _save_obj c = connection.execute(statement.values(value_params), params) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/ engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/ engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/ engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/ engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.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) PRIMARY KEY must be unique u'INSERT INTO projs (proj_id, proj_allocated, proj_blocked, proj_sup, presim_pop) VALUES (?, ?, ?, ?, ?)' [111, None, None, 44, 0] #END# -- 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.
Re: [sqlalchemy] Multiple databases or multiple schemas?
On Mon, Jun 14, 2010 at 01:06:04PM -0400, Michael Bayer wrote: you can do the multi-schema thing using either a PoolListener, or a Table subclass that implements a @property for the schema attribute - in both cases these would be linked to a thread local variable that you would set per request. If the load per site is low, you can also forego the usage of pooling to eliminate the issue of dormant connections (possibly look into something like pgpool, perhaps they have some feature that optimizes this use case). Both approach would work. The PoolListener looks a bit better, the Table subclass looks a bit hackish. BTW I am not planning to use threads, I'll just use a dozen python processes to make sure the site is responsive under heavy load. This should simplify pooling quite a bit, I'll just use a StaticPool. As far as approach, consider the load each per-user site will have, and how you might scale out to include multiple database servers. I'd grep around the PG docs and wiki carefully to get a full grasp of the implications of each approach. Yes, scaling was one of the motivation for this approach, I definitely need to learn more about how PG would handle this. Simplifying the architecture was the main motivation. I don't want to have a column `site_id` in every table. Also I think that putting everything in its own schema would improve performance dramatically: instead of big indexes for all sites, there'll be small indexes for each site. Thank-you so much, your reply helped a great deal. -- Henry Prêcheur -- 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.
Re: [sqlalchemy] Re: Multiple databases or multiple schemas?
On Sun, Jun 13, 2010 at 09:23:22PM +, Faheem Mitha wrote:
Sqlalchemy's table can take the qschema as argument, eg.
pheno_table = Table(
'pheno', metadata,
Column('patientid', String(60), primary_key=True),
Column('famid', String(60), nullable=True),
Column('sex_id', None, ForeignKey(schemaname+'.sex.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('race_id', None, ForeignKey(schemaname+'.race.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('phenotype', SmallInteger),
schema = schemaname,
)
So I don't think you do have to do that.
The thing is that each table will be present in every schema. So I can't
use the schema parameter (or I can subclass Table like Mike suggested).
Cheers,
--
Henry Prêcheur
--
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: Error trying to use session.execute
On Sun, 13 Jun 2010 11:20:58 -0400, Michael Bayer mike...@zzzcomputing.com wrote: When an exception occurs in a transaction while Postgresql, you in most cases must issue a rollback() (that is what (InternalError) current transaction is aborted, commands ignored until end of transaction block means, thats a PG message). Therefore you cannot attempt an operation with PG inside a transaction, have it fail, ignore the failure, then continue in the same transaction. You need to roll the transaction back and start a new one, or use an autocommit mode which accomplishes the same thing. As far as 2, I'm not sure what transactionalized execution is. It means a statement is executed while a transaction is in progress. Each= subsequent statement occurs within the same transaction as the previous, until a rollback() or commit() is issued. This is the opposite of autocommit, where each statement occurs in a distinct transaction. Hi Mike, Thanks for the clarifications. I figured out that the failure was the problem, but didn't understand exactly why. So pg doesn't automatically roll back the transaction, apparently. Regards, Faheem. -- 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: Unexpected SessionTransaction behaviour
On Jun 14, 3:14 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 14, 2010, at 9:56 AM, Vinay Sajip wrote: There's really no way to tell what's happening in your app without an example that illustrates the issue, such that if unusual behavior were observed, one could issue a pdb and step around to see the state of things as they progress. You can check the source code of Session.remove() yourself - it issues a close() on the session which detaches all objects, and then the whole thing is just thrown away. Its all Python GC accomplishing the work here, and after remove() nothing on the SQLA side is referencing that Session. Its not just SQLite that would have issues if Session.remove() left connection resources lying around - the connection would still have open transactional resources on it, which would cause locking issues with databases such as Postgresql and MS-SQL. My advice would be, assuming you cannot isolate the issue inside a small test case, to trap the application at the point at which it appears to be opening a mystery SessionTransaction, dropping into pdb, and checking around why there's more than one Session in memory, as well as checking what gc.collect() accomplishes here. That's fine, I'll keep on digging - I was just hoping for some guidance on where to dig most productively. The difficulty with making a small test case is that the memory footprint would be quite different, and the problem could fail to manifest - but I completely understand that from your point of view, it's hard to comment further without the small test case. About the mystery SessionTransaction, for example - having put a log statement in SessionTransaction.__init__, I would expect every SessionTransaction creation to be logged. I couldn't easily see how that SessionTransaction was created, though perhaps some more digging through the source would enlighten me. Regards, Vinay Sajip -- 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: Unexpected SessionTransaction behaviour
On Jun 14, 3:14 pm, Michael Bayer mike...@zzzcomputing.com wrote: My advice would be, assuming you cannot isolate the issue inside a small test case, to trap the application at the point at which it appears to be opening a mystery SessionTransaction, dropping into pdb, and checking around why there's more than one Session in memory, as well as checking what gc.collect() accomplishes here. Okay, I found the phantom transaction - it's being created in the session.remove() call in the cleanup of the previous request, in the following code in orm/session.py, in SessionTransaction.close(): if not self.session.autocommit: self.session.begin() I hadn't expected the SessionTransaction.close() to create a brand new SessionTransaction, sorry I missed that. But this means that SQLA starting state is different for two consecutive, identical requests - the session.remove() for the first request leaves a state which causes a different path to be taken by SQLA on the second request. How do I completely clean up after each request so that the next identical request is processed in the same way? Regards, Vinay Sajip -- 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.
Re: [sqlalchemy] Re: Unexpected SessionTransaction behaviour
On Jun 14, 2010, at 4:49 PM, Vinay Sajip wrote:
On Jun 14, 3:14 pm, Michael Bayer mike...@zzzcomputing.com wrote:
My advice would be, assuming you cannot isolate the issue inside a small
test case, to trap the application at the point at which it appears to be
opening a mystery SessionTransaction, dropping into pdb, and checking
around why there's more than one Session in memory, as well as checking what
gc.collect() accomplishes here.
Okay, I found the phantom transaction - it's being created in the
session.remove() call in the cleanup of the previous request, in the
following code in orm/session.py, in SessionTransaction.close():
if not self.session.autocommit:
self.session.begin()
I hadn't expected the SessionTransaction.close() to create a brand new
SessionTransaction, sorry I missed that. But this means that SQLA
starting state is different for two consecutive, identical requests -
the session.remove() for the first request leaves a state which causes
a different path to be taken by SQLA on the second request. How do I
completely clean up after each request so that the next identical
request is processed in the same way?
the new SessionTransaction that occurs in close() does not request any
connection resources, and is discarded immediately along with the session that
is the subject of remove().I don't see how it could be affected by any
previous requests. Each request should have a totally new Session object, with
a totally new SessionTransaction object. The old one is gone as soon as
remove() completes.
Here is an example. Everything, including SessionTransactions, checked out
connections, are gone after remove():
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.session import *
from sqlalchemy.pool import _ConnectionFairy as ConnectionFairy, NullPool
import gc
engine = create_engine('sqlite://', poolclass=NullPool)
Session = scoped_session(sessionmaker(bind=engine))
for i in range(10):
Session()
print do things with session !
trans_objects = [c.__class__ for c in gc.get_objects()
if isinstance(c, (SessionTransaction, ConnectionFairy))]
# one SessionTransaction exists
assert len(trans_objects) == 1
# use a connection
Session.execute(select 1)
trans_objects = [c.__class__ for c in gc.get_objects()
if isinstance(c, (SessionTransaction, ConnectionFairy))]
# one SessionTransaction, one ConnectionFairy exists
assert len(trans_objects) == 2
Session.commit()
Session.remove()
# ensure unreachable objects cleaned up
gc.collect()
trans_objects = [c.__class__ for c in gc.get_objects()
if isinstance(c, (SessionTransaction, ConnectionFairy))]
# zero SessionTransactions, ConnectionFairy objects exist
assert len(trans_objects) == 0
output:
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
do things with session !
--
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] How to save a null value in a field of type REAL PostGreSQL.
Hello, I'm using Pylons, Sqlalchimy and PostgreSQL, and am having trouble to save a field with null value in a postgres table where the field was defined by the type REAL and allowing null value. With the pylons while trying to save have an error saying that the value for the field can not be null. Anyone know why this happens? Thank you. -- 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.
