I apologize in advance for the size of this post, but I want to
provide as much info as possible. I work for a large company and I am
trying to use SqlAlchemy to implement a cleaner interface to one of
our databases.
The database is Sql Server 2005, the app runs on RHEL 3 under Python
2.5. I am using SqlAlchemy 0.4.0beta5.
Our corporate DBAs use OIDs, Binary(8) fields as key fields on our
tables. I am pulling this information for use by various applications
and need to be able to query the database based on these keys.
I have tried a couple different means of pulling the keys and re-
querying, and have yet to have a query succeed.
Here is the custom class I created to try and have the value display
in an intermediate format that is legible within the app and then
converted back when it comes time to query based on the key.
dbutils.py:
---------------
class OID(types.TypeDecorator):
"""Uninterprets the OID key value into a readable string"""
impl = types.Binary
def convert_bind_param(self, value, engine):
bytes = []
for x in range(0, len(value), 2):
bytes.append( chr( int (value[x:x+2], 16 ) ) )
return "%s" % ''.join( bytes )
def convert_result_value(self, value, engine):
return "%s" % ''.join(["%02X" % ord(x) for x in value])
This is the mapper file for the table in question. Notice the
commented out line for using my custom class and the line where I
tried to use the built-in Binary type.
specsections.py
------------------------
# Define spec_configuration_options table
spec_sections_table = Table('spec_sections', metadata,
#Column( 'section_OID', dbutils.OID(), primary_key=True ),
Column( 'section_OID', types.Binary(8), primary_key=True ),
Column( 'product_name', types.String(30), nullable=False ),
Column( 'density_value', types.String(8), nullable=False ),
Column( 'datasheet_type', types.String(50), nullable=False ),
Column( 'temp_range_min', types.String(10), nullable=False ),
Column( 'temp_range_max', types.String(10), nullable=False ),
Column( 'section_name', types.String(100), nullable=False ),
Column( 'design_id', types.String(50), nullable=True ),
Column( 'interface_type', types.String(50), nullable=True)
)
class SpecSections(object):
def __init__(self, section_OID, product_name, density_value,
datasheet_type,
temp_range_min, temp_range_max, section_name,
design_id, interface_type):
self.section_OID = section_OID
self.product_name = product_name
self.density_value = density_value
self.datasheet_type = datasheet_type
self.temp_range_min = temp_range_min
self.temp_range_max = temp_range_max
self.section_name = section_name
self.design_id = design_id
self.interface_type = interface_type
def __repr__(self):
return "::".join([self.section_OID, self.product_name,
self.density_value, self.datasheet_type,
self.temp_range_min,
self.temp_range_max, self.section_name, self.design_id,
self.interface_type] )
specsections_mapper = Session.mapper(SpecSections,
spec_sections_table)
Here is an example of a properly interpreted row using the dbutils.OID
class:
" 08C82B7C6A844743::SDRAM::64Mb::Marketing::0C::70C::DC Electrical
Characteristics::::"
Here is the binding statement being generated by SqlAlchemy:
2007-09-27 13:32:12,444 INFO sqlalchemy.engine.base.Engine.0x..cL
{'spec_section_symbols_section_OID': '\x08\xc8+|j\x84GC'}
I am not receiving any type of error, just an empty result set. I have
verified that the OID is being interpreted correctly by running an
interactive sql statement on the interpreted value shown.
I know in Perl I have to format the query without quotes or I don't
get any results. Something like this:
"select * from table where section_OID=0x08C82B7C6A844743"
I'm wondering if SqlAlchemy is quoting the parameter when it is bound
or if anyone has any other suggestions as to why I am not getting any
results.
Thanks.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
