RE: [sqlalchemy] Kerberos authentication with sybase
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 ? The biggest problem here is the serialization of those values to string and de-serializing them when sqlalchemy sets them to python-sybase. It could be ok if we would know the type of the property but as far as I see it cannot be introspected from the sybase library. We could have an algorithm serializing/deserializing the value: - if it's starting with CS_, we use it as the name of the variable in the Sybase module - if we can convert it to an integer we use it as an integer - otherwise we use it as string specified Based on the above, my kerberos connection url would be the following (missing username+pw in this case): sybase+pysybase://hostname/?CS_SEC_NETWORKAUTH=CS_TRUECS_SEC_SERVERPRINCIPAL=sybase/some_host It's just an idea, I don't know how it could fit into the design of sqlalchemy. 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.
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
RE: [sqlalchemy] Kerberos authentication with sybase
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. 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.
RE: [sqlalchemy] Sybase reports conversion from datatype 'CHAR' to 'INT' is not allowed. when using string instead of int for filterby
SQLAlchemy doesn't do type coercion out of the box. Some DBAPIs do, which is why you may have not noticed this issue on other platforms. You can build that functionality yourself using TypeDecorator: http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.htm?highlight=typedecorator#sqlalchemy.types.TypeDecorator I've fixed my problem with the TypeDecorator you suggested. Thanks. Just to fix that, sqlalchemy doesn't do type coercion by default, and it will never do it. Is it correct? In my test application I was able to replace the Integer type to the decorated one, which is fine, but requires the same 4-line code for my projects. It would be better if sqlalchemy would do it for me by default but if it doesn't fit to the design I can accept that. 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.
[sqlalchemy] Kerberos authentication with sybase
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.
[sqlalchemy] Sybase reports conversion from datatype 'CHAR' to 'INT' is not allowed. when using string instead of int for filterby
Hi all,
We're trying to use sybase dialect in sqlalchemy, but it creates invalid select
parameters when we are trying to use a python-string (or unicode object)
variable to specify a sql-int variable.
That problem occurs when we use pylons web framework with formalchemy with
sqlalchem+sybase - which actually specifies all the parameters in unicode
objects to filter_by() method.
I'm using the nightly snapshot from sqlalchemy.org.
A simple example to re-produce this error is the following (only the relevant
parts):
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(64)),
Column('fullname', String(64)),
Column('password', String(64))
)
session.query(User).filter_by(id=1).order_by(User.id)
Here, we want to filter to id=1, where 1 is a string value, but it's an
integer in the database. If I execute the example, it says (again, only the
relevant parts):
2010-06-09 16:59:50,458 INFO sqlalchemy.engine.base.Engine.0x...ae2c BEGIN
2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c SELECT
users.id AS users_id, users.name AS users_name, users.fullname AS
users_fullname, users.password AS users_password
FROM users
WHERE users.id = @id_1 ORDER BY users.id
2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c {'@id_1':
'1'}
sqlalchemy.exc.DatabaseError: (DatabaseError) Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the
CONVERT function to run this query.
As you can see, sqlalchemy constructed a parameters dictionary containing a
string as a value, however this field is integer typed in the database and
sybase requires the value to be specified as an integer (it doesn't convert it
to int automatically). If I specify int typed variable in the example above to
filter_by then it's ok.
I think it's a bug in the sqlalchemy as the ORM should do the type conversion
in this case.
I've attached a patch (diffed to the nightly snapshot) which solved me this
problem. As I'm not an sqlalchemy expert I assume this will convert my str
python type to int when I'm using Integer in sqlalchemy in any query. Do you
see any problem with it? If not, could it be merged to trunk? :)
Thank you in advance,
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.
--- pysybase.py 2010-06-09 11:16:55.0 -0400
+++ pysybase.py.new 2010-06-09 11:17:54.0 -0400
@@ -33,6 +33,12 @@
else:
return sqltypes.Numeric.result_processor(self, dialect, type_)
+class _SybInteger(sqltypes.Integer):
+def bind_processor(self, dialect):
+def process(value):
+return int(value)
+return process
+
class SybaseExecutionContext_pysybase(SybaseExecutionContext):
def set_ddl_autocommit(self, dbapi_connection, value):
@@ -62,7 +68,8 @@
colspecs={
sqltypes.Numeric:_SybNumeric,
- sqltypes.Float:sqltypes.Float
+ sqltypes.Float:sqltypes.Float,
+ sqltypes.Integer:_SybInteger
}
@classmethod
