def get_column_names(table, db_context=None, table_schema=None):
"""Returns a list of the names of the columns in a table, quoted with []
Parameters
==========
table : str or TableSpec
The table
db_context : DBContext
The database context that contains the table. If the table is a TableSpec
object that specifies a db_context property, you can omit this.
table_schema : str
The name of the schema in which the table is located. If you omit
this, the value will be read from the tablespec or from the db_context.
This will usually lead to the default value of [dbo] being used.
"""
table_spec = get_table_spec(table, db_context, table_schema)
table_name = db_identifier_unquote(table_spec.table_name)
table_schema = db_identifier_unquote(table_spec.table_schema)
db_context = table_spec.db_context
return [
'[' + row.column_name + ']'
for row in db_context.execute(_GET_COLUMN_NAMES_QUERY, (table_name,
table_schema))
]
def drop_table_if_exists(table, db_context=None, table_schema=None):
"""Drop the identified table if it exists in the database
"""
table_spec = get_table_spec(table, db_context, table_schema)
name = db_identifier_unquote(table_spec.table_name)
schema = db_identifier_unquote(table_spec.table_schema)
db_context = table_spec.db_context
# We are using "for" as an "if"--ugly, but effective
for row in table_spec.db_context.execute(_TEST_EXISTS_TABLE_QUERY,
(name, schema)):
db_context.executeNoResults(
_DROP_TABLE_QUERY_2.format(table=table_spec))
break
def populate_from_connection( self ):
"""Record the layout of an existing table in a database
"""
self.clear()
for row in self.db_context.execute( _GET_TABLE_SPEC_QUERY, (
db_identifier_unquote( self.table_name ),
db_identifier_unquote( self.table_schema ) ) ):
field_spec = self.field_spec_class()
field_spec.populate_from_information_schema( row )
self.add( field_spec )
for row in self.db_context.execute( _GET_PRIMARY_KEY_QUERY, (
db_identifier_unquote( self.table_name ),
db_identifier_unquote( self.table_schema ) ) ):
self.primary_key.append( self[ row.column_name ] )
def drop_assembly_if_exists(assembly_name, db_context):
"""Drop a "CLR assembly" from the database
A CLR Assembly is an external library containing stored procedures and user-defined
functions.
"""
assembly_name = db_identifier_unquote(assembly_name)
for row in db_context.execute(_TEST_EXISTS_ASSEMBLY_QUERY,
(assembly_name, )):
# First, drop the defined functions and procs that are defined in the assembly
for row in db_context.execute(_GET_ASSEMBLY_FUNCTIONS_QUERY,
(assembly_name, )):
t = row.type
object_name = row.object_name
object_schema = row.object_schema
if t in ('AF', 'FS', 'FT', 'TA'):
db_context.executeNoResults(
_DROP_FUNCTION_QUERY.format(name=object_name,
schema=object_schema))
elif t == 'PC':
db_context.executeNoResults(
_DROP_PROC_QUERY.format(name=object_name,
schema=object_schema))
# Now drop the assembly
db_context.executeNoResults(
_DROP_ASSEMBLY_QUERY.format(assembly_name=assembly_name))
break
def assembly_exists(assembly_name, db_context):
"""Test whether a "CLR assembly" exists in the database
A CLR Assembly is an external library containing stored procedures and user-defined
functions.
"""
assembly_name = db_identifier_unquote(assembly_name)
for row in db_context.execute(_TEST_EXISTS_ASSEMBLY_QUERY,
(assembly_name, )):
return True
return False
def _get_colnames( self, excel, sheet ):
""" This function gets the column names from the file. If the file doesn't contain
column names (specified in self.getNames) then we create generic column names,
i.e. Column_1, Column_2, etc.
Returns
----------
A list of column names where each name is enclosed in square brackets [ ]
"""
values = self._getValues( excel, sheet, self.skip, self.skip )
if self.getNames:
i = 0
names = []
for x in values[0]:
if x in names or x is None:
names.append( db_identifier_unquote( 'Column_{}'.format( i ) ) )
else:
names.append( db_identifier_unquote( str( x ) ) )
i += 1
else:
names = [ db_identifier_unquote( 'Column_{}'.format( i ) ) for i in xrange( len( values[0] ) ) ]
return names
def n_obs(table, db_context=None, table_schema=None):
"""Return number of rows in a table
Parameters
==========
table : str or TableSpec
The table whose rows you need counted
db_context : DBContext
The database context that contains the table. If the table is a TableSpec
object that specifies a db_context property, you can omit this.
table_schema : str
The name of the schema in which the table is located. If you omit
this, the value will be read from the tablespec or from the db_context.
This will usually lead to the default value of [dbo] being used.
"""
table_spec = get_table_spec(table, db_context, table_schema)
for row in table_spec.db_context.execute(
_N_OBS_QUERY.format(
table=db_identifier_unquote(table_spec.table_name),
schema=db_identifier_unquote(table_spec.table_schema))):
return row[0]
def table_exists(table, db_context=None, table_schema=None):
"""Test whether the identified table exists in the database
Parameters
==========
table : str or TableSpec
The table
db_context : DBContext
The database context that contains the table. If the table is a TableSpec
object that specifies a db_context property, you can omit this.
table_schema : str
The name of the schema in which the table is located. If you omit
this, the value will be read from the tablespec or from the db_context.
This will usually lead to the default value of [dbo] being used.
"""
table_spec = get_table_spec(table, db_context, table_schema)
for row in table_spec.db_context.execute(
_TEST_EXISTS_TABLE_QUERY,
(db_identifier_unquote(table_spec.table_name),
db_identifier_unquote(table_spec.table_schema))):
return True
return False
def get_table_names(db_context, table_schema=None):
"""Returns a list of all of the tables in a database schema
Parameters
----------
db_context : :class:`DBContext`
The DBContext object defining the database to examine
table_schema : str optional
The schema within which we will look for tables. If not supplied, will
search the default schema for the `db_context` (usually `[dbo]`)
"""
table_schema = db_identifier_unquote(
_get_best_schema(db_context, table_schema))
return [
'[' + row.table_name + ']'
for row in db_context.execute(_GET_TABLE_NAMES_QUERY, (table_schema, ))
]
def _f_maker( field ):
name = db_identifier_unquote( field.field_name )
if field.basic_type == "NVARCHAR":
if field.has_float:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
else:
row.__setitem__( name, str( val ) )
elif field.has_int:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
elif isinstance( val, Number ):
row.__setitem__( name, str( int( val ) ) )
else:
row.__setitem__( name, str( val ) )
else:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
else:
row.__setitem__( name, str( val ) )
elif field.basic_type in ( "TINYINT", "SMALLINT", "INT", "BIGINT" ):
if field.has_str:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
elif isinstance( val, ( str, unicode ) ):
if val in ( '', '.' ):
row.__setitem__( name, None )
else:
val = val.split('.')[0]
row.__setitem__( name, int( val ) )
else:
row.__setitem__( name, int( val ) )
else:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
else:
row.__setitem__( name, int( val ) )
elif field.basic_type == "FLOAT":
if field.has_str:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
elif isinstance( val, ( str, unicode ) ):
if val in ( '', '.' ):
row.__setitem__( name, None )
else:
row.__setitem__( name, float( val ) )
else:
def f( row, val ):
if val is None:
row.__setitem__( name, None )
else:
row.__setitem__( name, float( val ) )
else:
def f( row, val ):
row.__setitem__( name, val )
return f
def getRows( self, factory = None ):
"""The spreadsheet contents in Python, as a sequence of "row" objects
By default, the row objects will be dictionaries if getNames is True,
otherwise they will be lists. The default behavior can be overridden by
explicitly providing a callable parameter to serve as the row factory
This method is a generator. The row objects are created as needed when
you iterate through the return values. If you need to create all of the
rows at once, do something like::
values = [ x for x in reader.getRows() ]
Arguments
=========
factory : callable
A function, method or constructor that will be used to generate the row
objects. This callable must accept two arguments: a list of names and a
tuple of values. See :ref:`dictFactory` and :ref:`listFactory`
"""
self._validate_shared_inputs()
if os.path.splitext( self.filename )[1].lower() == '.txt':
found,sheet,excel = self._import_text()
else:
found,sheet,excel = self._get_worksheet_from_file()
try:
names = self._get_colnames( excel, sheet )
if db_identifier_unquote( self.importOrder ) in names:
raise ValueError( "Error: import_order column '" + self.importOrder + "' already exists in table. It must be a new column." )
#create self.outputTable as a tablespec here
if self.outputTable is None or isinstance(self.outputTable, (str,unicode)):
# Even if we are importing into Python, we use a TableSpec to store type information
self.outputTable = TableSpec( self.outputTable, None )
del self.outputTable[:]
for colname in names:
self.outputTable.add( FieldSpec( colname,'int',data_length=1 ) )
# Determine correct column types
row_counter = self.skip
if self.getNames:
row_counter += 1
self._determine_column_types( excel, sheet, row_counter )
# Create a row factory based on the found column types
if factory is None:
factory = row_factory( self.outputTable )
# Read the data
while ( True ):
values = self._getValues( excel, sheet, row_counter, self.buffer_size + row_counter - 1 )
row_counter += self.buffer_size
if values is None or len( values ) == 0:
break
for row in values:
if any( row ):
yield factory( names, row )
finally:
self._close_workbook( found,excel )
def _normalize_property( name ):
return _WHITESPACE_PATTERN.sub( '_', db_identifier_unquote( name ) )