diff options
author | Alexandre Fayolle <alexandre.fayolle@logilab.fr> | 2009-12-14 15:07:07 +0100 |
---|---|---|
committer | Alexandre Fayolle <alexandre.fayolle@logilab.fr> | 2009-12-14 15:07:07 +0100 |
commit | b5787352cea7b205ad9da5c4a7aa0385f8aa2052 (patch) | |
tree | aa6b9e67e458d53b38691c976b15989de820b674 | |
parent | a252b7db2c66ca371902a12f5c57bbda56045113 (diff) | |
download | logilab-common-b5787352cea7b205ad9da5c4a7aa0385f8aa2052.tar.gz |
added SQL Server support using Pyodbc
-rw-r--r-- | adbh.py | 26 | ||||
-rw-r--r-- | db.py | 96 |
2 files changed, 122 insertions, 0 deletions
@@ -570,11 +570,37 @@ class _MyAdvFuncHelper(_GenericAdvFuncHelper): allindices += self.list_indices(cursor, table) return allindices +class _SqlServer2005FuncHelper(_GenericAdvFuncHelper): + backend_name = 'sqlserver2005' + ilike_support = False + # modifiable but should not be shared + FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() + TYPE_MAPPING = { + 'String' : 'ntext', + 'Int' : 'integer', + 'Float' : 'float', + 'Decimal' : 'decimal', + 'Boolean' : 'bit', + 'Date' : 'datetime', + 'Time' : 'time', + 'Datetime' : 'datetime', + 'Interval' : 'interval', + 'Password' : 'varbinary(255)', + 'Bytes' : 'varbinary(max)', + 'TIMESTAMP': 'datetime', + } + + def list_tables(self, cursor): + """return the list of tables of a database""" + return [row.table_name for row in cursor.tables()] + def binary_value(self, value): + return StringIO.StringIO(value) ADV_FUNC_HELPER_DIRECTORY = {'postgres': _PGAdvFuncHelper(), 'sqlite': _SqliteAdvFuncHelper(), 'mysql': _MyAdvFuncHelper(), + 'sqlserver2005': _SqlServer2005FuncHelper(), } @@ -601,7 +601,97 @@ class _MySqlDBAdapter(DBAPIAdapter): finally: cursor.execute("DROP TABLE _type_code_test") +class _PyodbcAdapter(DBAPIAdapter): + driver = 'Override in subclass' + + def connect(self, host='', database='', user='', password='', port=None): + """Handles pyodbc connection format""" + pyodbc = self._native_module + + class PyodbcCursor(object): + """cursor adapting usual dict format to pyodbc format + in SQL queries + """ + def __init__(self, cursor): + self._cursor = cursor + def _replace_parameters(self, sql, kwargs): + if isinstance(kwargs, dict): + new_sql = re.sub(r'%\(([^\)]+)\)s', r'?', sql) + key_order = re.findall(r'%\(([^\)]+)\)s', sql) + args = tuple([kwargs[key] for key in key_order]) + return new_sql, args + + # XXX dumb + return re.sub(r'%s', r'?', sql), kwargs + + def execute(self, sql, kwargs=None): + if kwargs is None: + self._cursor.execute(sql) + else: + final_sql, args = self._replace_parameters(sql, kwargs) + self._cursor.execute(final_sql , args) + + def executemany(self, sql, kwargss): + if not isinstance(kwargss, (list, tuple)): + kwargss = tuple(kwargss) + self._cursor.executemany(self, self._replace_parameters(sql, kwargss[0]), kwargss) + def __getattr__(self, attrname): + return getattr(self._cursor, attrname) + + class PyodbcCnxWrapper: + def __init__(self, cnx): + self._cnx = cnx + def cursor(self): + return PyodbcCursor(self._cnx.cursor()) + def __getattr__(self, attrname): + return getattr(self._cnx, attrname) + + cnx_string_bits = ['DRIVER={%(driver)s}'] + if host: + cnx_string_bits.append('SERVER=%(host)s') + if database: + cnx_string_bits.append('DATABASE=%(database)s') + if user: + cnx_string_bits.append('UID=%(user)s') + if password: + cnx_string_bits.append('PWD=%(password)s') + variables = {'host' : host, + 'database' : database, + 'user' : user, 'password' : password, + 'driver': self.driver} + cnx_string = ';'.join(cnx_string_bits) % variables + cnx = self._native_module.connect(cnx_string) + return self._wrap_if_needed(PyodbcCnxWrapper(cnx), user) + + def process_value(self, value, description, encoding='utf-8', binarywrap=None): + # if the dbapi module isn't supporting type codes, override to return value directly + typecode = description[1] + assert typecode is not None, self + if typecode == self.STRING: + if isinstance(value, str): + return unicode(value, encoding, 'replace') + elif typecode == self.BINARY: # value is a python buffer + if binarywrap is not None: + return binarywrap(value[:]) + else: + return value[:] + elif typecode == self.UNKNOWN: + # may occurs on constant selection for instance (e.g. SELECT 'hop') + # with postgresql at least + if isinstance(value, str): + return unicode(value, encoding, 'replace') + + return value + + +class _PyodbcSqlServer2000Adapter(_PyodbcAdapter): + driver = "SQL Server" + +class _PyodbcSqlServer2005Adapter(_PyodbcAdapter): + driver = "SQL Native Client" +class _PyodbcSqlServer2008Adapter(_PyodbcAdapter): + driver = "SQL Native Client 10.0" ## Drivers, Adapters and helpers registries ################################### @@ -610,6 +700,9 @@ PREFERED_DRIVERS = { "postgres" : [ 'psycopg2', 'psycopg', 'pgdb', 'pyPgSQL.PgSQL', ], "mysql" : [ 'MySQLdb', ], # 'pyMySQL.MySQL, ], "sqlite" : ['pysqlite2.dbapi2', 'sqlite', 'sqlite3',], + "sqlserver2000" : ['pyodbc'], + "sqlserver2005" : ['pyodbc'], + "sqlserver2008" : ['pyodbc'], } _ADAPTERS = { @@ -622,6 +715,9 @@ _ADAPTERS = { 'sqlite' : { 'pysqlite2.dbapi2' : _PySqlite2Adapter, 'sqlite' : _SqliteAdapter, 'sqlite3' : _PySqlite2Adapter, }, + "sqlserver2000" : {'pyodbc': _PyodbcSqlServer2000Adapter}, + "sqlserver2005" : {'pyodbc': _PyodbcSqlServer2005Adapter}, + "sqlserver2008" : {'pyodbc': _PyodbcSqlServer2008Adapter}, } # _AdapterDirectory could be more generic by adding a 'protocol' parameter |