Package grizzled :: Package db :: Module sqlserver
[hide private]
[frames] | no frames]

Source Code for Module grizzled.db.sqlserver

  1  # $Id: f38a8ecb542b475e96a9f613b3bd9bc269f2473d $ 
  2   
  3  """ 
  4  SQL Server extended database driver. 
  5  """ 
  6   
  7  __docformat__ = "restructuredtext en" 
  8   
  9  # --------------------------------------------------------------------------- 
 10  # Imports 
 11  # --------------------------------------------------------------------------- 
 12   
 13  import os 
 14  import sys 
 15   
 16  from grizzled.db.base import (DBDriver, Error, Warning, TableMetadata, 
 17                                IndexMetadata, RDBMSMetadata) 
 18   
 19  # --------------------------------------------------------------------------- 
 20  # Exports 
 21  # --------------------------------------------------------------------------- 
 22   
 23  # --------------------------------------------------------------------------- 
 24  # Classes 
 25  # --------------------------------------------------------------------------- 
 26   
27 -class SQLServerDriver(DBDriver):
28 """DB Driver for Microsoft SQL Server, using the pymssql DB API module.""" 29
30 - def get_import(self):
31 import pymssql 32 return pymssql
33
34 - def get_display_name(self):
35 return 'SQL Server'
36
37 - def do_connect(self, 38 host='localhost', 39 port=None, 40 user='', 41 password='', 42 database='default'):
43 dbi = self.get_import() 44 self.db_name = database 45 if port == None: 46 port = '1433' 47 return dbi.connect(host='%s:%s' % (host, port), 48 user=user, 49 password=password, 50 database=database)
51
52 - def get_tables(self, cursor):
53 cursor.execute("select name from %s..sysobjects where xtype = 'U'" % 54 self.db_name) 55 table_names = [] 56 rs = cursor.fetchone() 57 while rs is not None: 58 table_names += [rs[0]] 59 rs = cursor.fetchone() 60 61 return table_names
62
63 - def get_rdbms_metadata(self, cursor):
64 product = '' 65 version = '' 66 vendor = 'Microsoft Corporation' 67 cursor.execute('xp_msver'); 68 rs = cursor.fetchone() 69 while rs is not None: 70 name = rs[1].lower() 71 value = rs[3] 72 if name == 'productname': 73 product = value 74 75 elif name == 'productversion': 76 version = value 77 78 elif name == 'companyname': 79 vendor == value 80 81 rs = cursor.fetchone() 82 83 return RDBMSMetadata(vendor, product, version)
84
85 - def get_table_metadata(self, table, cursor):
86 self._ensure_valid_table(cursor, table) 87 dbi = self.get_import() 88 cursor.execute("SELECT column_name, data_type, " \ 89 "character_maximum_length, numeric_precision, " \ 90 "numeric_scale, is_nullable "\ 91 "FROM information_schema.columns WHERE "\ 92 "LOWER(table_name) = '%s'" % table) 93 rs = cursor.fetchone() 94 results = [] 95 while rs is not None: 96 is_nullable = False 97 if rs[5] == 'YES': 98 is_nullable = True 99 100 data = TableMetadata(rs[0], rs[1], rs[2], rs[3], rs[4], is_nullable) 101 results += [data] 102 rs = cursor.fetchone() 103 return results
104
105 - def get_index_metadata(self, table, cursor):
106 self._ensure_valid_table(cursor, table) 107 dbi = self.get_import() 108 cursor.execute("EXEC sp_helpindex '%s'" % table) 109 rs = cursor.fetchone() 110 results_by_name = {} 111 while rs is not None: 112 name = rs[0] 113 description = rs[1] 114 columns = rs[2].split(', ') 115 results_by_name[name] = IndexMetadata(name, columns, description) 116 rs = cursor.fetchone() 117 118 names = results_by_name.keys() 119 names.sort() 120 result = [] 121 for name in names: 122 result += [results_by_name[name]] 123 124 return result
125