Package Dbase :: Module DbUtils

Module DbUtils

a set of functions for interacting with databases

When possible, it's probably preferable to use a _DbConnection.DbConnect_ object

Functions

[hide private]

_take(fromL, what)

source code

GetColumns(dBase, table, fieldString, user='sysdba', password='masterkey', join='')
gets a set of data from a table... source code

GetData(dBase, table, fieldString='*', whereString='', user='sysdba', password='masterkey', removeDups=-1, join='', forceList=0, transform=None, randomAccess=1, extras=None)
a more flexible method to get a set of data from a table **Arguments** - fields: a string with the names of the fields to be extracted, this should be a comma delimited list - where: the SQL where clause to be used with the DB query - removeDups indicates the column which should be used to screen out duplicates. source code

DatabaseToExcel(dBase, table, fields='*', join='', where='', wrapper=None, user='sysdba', password='masterkey', lowMemory=False)
Pulls the contents of a database and puts them in an Excel worksheet **Arguments** - dBase: the name of the DB file to be used - table: the name of the table to query - fields: the fields to select with the SQL query - join: the join clause of the SQL query (e.g. source code

DatabaseToText(dBase, table, fields='*', join='', where='', wrapper=None, user='sysdba', password='masterkey', delim=',')
Pulls the contents of a database and makes a deliminted text file from them **Arguments** - dBase: the name of the DB file to be used - table: the name of the table to query - fields: the fields to select with the SQL query - join: the join clause of the SQL query (e.g. source code

TypeFinder(data, nRows, nCols, nullMarker=None)
finds the types of the columns in _data_ if nullMarker is not None, elements of the data table which are equal to nullMarker will not count towards setting the type of their columns.

source code

DetermineColTypes(wrapper, nullMarker=None)
This is kind of crude hack to automagically determine the types of columns in the active Excel sheet **Arguments** - wrapper: the _ExcelWrapper_ to be used in interacting with Excel - nullMarker: (optional) if this is not None, elements of the data table which are equal to nullMarker will not count towards setting the type of their columns.

source code

_AdjustColHeadings(colHeadings, maxColLabelLen)
*For Internal Use* removes illegal characters from column headings and truncates those which are too long.

source code

GetTypeStrings(colHeadings, colTypes, keyCol=None)
returns a list of SQL type strings...

source code

_insertBlock(conn, sqlStr, block, silent=False)

source code

_AddDataToDb(dBase, table, user, password, colDefs, colTypes, data, nullMarker=None, blockSize=100)
*For Internal Use*...

source code

ExcelToDatabase(dBase, table, wrapper=None, user='sysdba', password='masterkey', maxColLabelLen=31, keyCol=None, nullMarker=None, force=0)
convert the active excel worksheet into a database. source code

TextFileToDatabase(dBase, table, inF, delim=',', user='sysdba', password='masterkey', maxColLabelLen=31, keyCol=None, nullMarker=None)
loads the contents of the text file into a database. source code

DatabaseToDatabase(fromDb, fromTbl, toDb, toTbl, fields='*', join='', where='', user='sysdba', password='masterkey', keyCol=None, nullMarker='None')
FIX: at the moment this is a hack source code

Variables

[hide private]

haveExcel = 0

Function Details

[hide private]

GetColumns(dBase, table, fieldString, user=`'sysdba'`, password=`'masterkey'`, join=`''`)

source code

gets a set of data from a table

**Arguments**

 - dBase: database name

 - table: table name
 
 - fieldString: a string with the names of the fields to be extracted,
    this should be a comma delimited list

 - user and  password:

 - join: a join clause (omit the verb 'join')
   

**Returns**

 - a list of the data

GetData(dBase, table, fieldString=`'*'`, whereString=`''`, user=`'sysdba'`, password=`'masterkey'`, removeDups=-1, join=`''`, forceList=0, transform=None, randomAccess=1, extras=None)

source code

a more flexible method to get a set of data from a table

**Arguments**

 - fields: a string with the names of the fields to be extracted,
      this should be a comma delimited list

 - where: the SQL where clause to be used with the DB query

 - removeDups indicates the column which should be used to screen
    out duplicates.  Only the first appearance of a duplicate will
    be left in the dataset.

**Returns**

  - a list of the data


**Notes**

  - EFF: this isn't particularly efficient

DatabaseToExcel(dBase, table, fields=`'*'`, join=`''`, where=`''`, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, lowMemory=False)

source code

Pulls the contents of a database and puts them in an Excel worksheet

**Arguments**
  - dBase: the name of the DB file to be used

  - table: the name of the table to query

  - fields: the fields to select with the SQL query

  - join: the join clause of the SQL query
    (e.g. 'join foo on foo.bar=base.bar')

  - where: the where clause of the SQL query
    (e.g. 'where foo = 2' or 'where bar > 17.6')

  - wrapper: an _Excel.ExcelWrapper.ExcelWrapper_ to be used
    in interacting with Excel
    
  - user: the username for DB access

  - password: the password to be used for DB access

DatabaseToText(dBase, table, fields=`'*'`, join=`''`, where=`''`, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, delim=`','`)

source code

Pulls the contents of a database and makes a deliminted text file from them

**Arguments**
  - dBase: the name of the DB file to be used

  - table: the name of the table to query

  - fields: the fields to select with the SQL query

  - join: the join clause of the SQL query
    (e.g. 'join foo on foo.bar=base.bar')

  - where: the where clause of the SQL query
    (e.g. 'where foo = 2' or 'where bar > 17.6')

  - wrapper: an _Excel.ExcelWrapper.ExcelWrapper_ to be used
    in interacting with Excel
    
  - user: the username for DB access

  - password: the password to be used for DB access

**Returns**

  - the CSV data (as text)

DetermineColTypes(wrapper, nullMarker=None)

source code

This is kind of crude hack to automagically determine the types
  of columns in the active Excel sheet

**Arguments**

  - wrapper: the _ExcelWrapper_ to be used in interacting with Excel

  - nullMarker: (optional) if this is not None, elements of the
    data table which are equal to nullMarker will not count towards
    setting the type of their columns.

**Returns**

  - a list of the types of each column

**Note**

  - we make the assumption that there are only three possible types: int,
    float and string.

GetTypeStrings(colHeadings, colTypes, keyCol=None)

source code

returns a list of SQL type strings

_AddDataToDb(dBase, table, user, password, colDefs, colTypes, data, nullMarker=None, blockSize=100)

source code

*For Internal Use*

(drops and) creates a table and then inserts the values

ExcelToDatabase(dBase, table, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, maxColLabelLen=31, keyCol=None, nullMarker=None, force=0)

source code

convert the active excel worksheet into a database.

this isn't as smooth or slick as the conversion the other way... sad.

 **Arguments**

   - dBase: the name of the DB to use

   - table: the name of the table to create/overwrite

   - wrapper: the _ExcelWrapper_ to use

   - user: the user name to use in connecting to the DB

   - password: the password to use in connecting to the DB

   - maxColLabelLen: the maximum length a column label should be
     allowed to have (truncation otherwise)

   - keyCol: the column to be used as an index for the db
   
 **Notes**

   - if _table_ already exists, it is destroyed before we write
     the new data

TextFileToDatabase(dBase, table, inF, delim=`','`, user=`'sysdba'`, password=`'masterkey'`, maxColLabelLen=31, keyCol=None, nullMarker=None)

source code

loads the contents of the text file into a database.

**Arguments**

  - dBase: the name of the DB to use

  - table: the name of the table to create/overwrite

  - inF: the file like object from which the data should
    be pulled (must support readline())

  - delim: the delimiter used to separate fields  

  - user: the user name to use in connecting to the DB

  - password: the password to use in connecting to the DB
  
  - maxColLabelLen: the maximum length a column label should be
    allowed to have (truncation otherwise)

  - keyCol: the column to be used as an index for the db

**Notes**

  - if _table_ already exists, it is destroyed before we write
    the new data

  - we assume that the first row of the file contains the column names

Module DbUtils

GetColumns(dBase, table, fieldString, user='sysdba', password='masterkey', join='')

GetData(dBase, table, fieldString='*', whereString='', user='sysdba', password='masterkey', removeDups=-1, join='', forceList=0, transform=None, randomAccess=1, extras=None)

DatabaseToExcel(dBase, table, fields='*', join='', where='', wrapper=None, user='sysdba', password='masterkey', lowMemory=False)

DatabaseToText(dBase, table, fields='*', join='', where='', wrapper=None, user='sysdba', password='masterkey', delim=',')

DetermineColTypes(wrapper, nullMarker=None)

GetTypeStrings(colHeadings, colTypes, keyCol=None)

_AddDataToDb(dBase, table, user, password, colDefs, colTypes, data, nullMarker=None, blockSize=100)

ExcelToDatabase(dBase, table, wrapper=None, user='sysdba', password='masterkey', maxColLabelLen=31, keyCol=None, nullMarker=None, force=0)

TextFileToDatabase(dBase, table, inF, delim=',', user='sysdba', password='masterkey', maxColLabelLen=31, keyCol=None, nullMarker=None)

GetColumns(dBase, table, fieldString, user=`'sysdba'`, password=`'masterkey'`, join=`''`)

GetData(dBase, table, fieldString=`'*'`, whereString=`''`, user=`'sysdba'`, password=`'masterkey'`, removeDups=-1, join=`''`, forceList=0, transform=None, randomAccess=1, extras=None)

DatabaseToExcel(dBase, table, fields=`'*'`, join=`''`, where=`''`, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, lowMemory=False)

DatabaseToText(dBase, table, fields=`'*'`, join=`''`, where=`''`, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, delim=`','`)

ExcelToDatabase(dBase, table, wrapper=None, user=`'sysdba'`, password=`'masterkey'`, maxColLabelLen=31, keyCol=None, nullMarker=None, force=0)

TextFileToDatabase(dBase, table, inF, delim=`','`, user=`'sysdba'`, password=`'masterkey'`, maxColLabelLen=31, keyCol=None, nullMarker=None)