Module DBUtil

Execute a single SQL query / command against the database. If the description attribute is not None, this implies this was a select statement that produced a result set which will be returned by the fetchall() method.

If the description is null, then at least return the rowcount affected by the query. This may be -1 or None still if it is a non-row affecting command (e.g. create / drop).

If includeColumnNames is true and the query yields a result set, then one row (list) will be added to the beginning which contains the names of each column as extracted from the cursor.description.

If incTypeCodes is true and the query yields a result set, a row (list) will be added to the beginning (but after column names if those are included as well), which contains the numerical type codes of each column as extracted from the cursor.description.

This method is probably not terribly efficient and should only be used for prototype testing and short command line functions. For retrieving data to send to stdout or some other stream, add the formatter parameter as an instance of a ResultFormatter object to pipe the data through one fetch at a time. In that case, the full results (which are presumably large) will NOT be returned by the method.

Given a DB script file object (caller should handle the opening by filename or other method), run each command as a SQL statement, delimited by semicolons (;) at the end of a line.

Insert the contents of a whitespace-delimited text file into the database.

Inserts the contents of the <sourceFile> into the database under the <tableName>. One line is expected in the <sourceFile> per row in the database, with each item delimited by the <delim> character. These items will be inserted under the respective order of the given list of columnNames.

It is possible for a single parameter (text in particular) to span multiple lines by enclosing it in double-quotes ("). Otherwise, a newline is always considered a delimiter.

If idFile is provided, then will try to run SQL from identityQuery method after each insert, and write out the contents, one per line to the idFile.

Update the database with the contents of a whitespace-delimited text file.

Updates the contents of the <tableName> with the data from the <sourceFile>. One line is expected in the <sourceFile> per row in the database, with each item delimited by the <delim> character (specify None for any whitespace). These items will be inserted under the respective order of the given list of <columnNames>. If the columnNames parameter is not provided, assume the first line of the <sourceFile> contains the column names.

To know which rows to update, assume the last column listed in <columnNames> is the ID column to identify rows by. In that case, the data value there from the <sourceFile> will not be used to update the row, but will instead be used to identify the row to update the rest of the data by. If more than one column is necessary to identify a row (composite key), indicate how many of the last columns in <columnNames> should be used with <nIdCols>. Note that these key ID values must not be None / null. The query looks for rows where columnname = value, and the = operator always returns false when the value is null.

Adapted from Jocelyne's handy method to search the named table in database for a row whose attributes match the key-value pairs specified in searchDict.

If one exists, then return the column (probably the primary key) named by retrieveCol. Otherwise, insert a row into the table with the data specified in the insertDict key-value pairs and try accessing the retrieveCol again (presumably the one just inserted).

If forceUpdate is specified as True, then, even if the row already exists in the database, update the row to match the contents of the insertDict.

The connection object to the database (conn) can be specified, otherwise it will just default to that returned by the connection() method. If no insertDict is specified, use the searchDict as necessary. If no retrieveCol is specified, then will attempt to find the default primary key column based on the table name.

Adapted from Jocelyne's function.  Given a dictionary object (RowItemModel)
representing a row of a database table, and identified by the key value(s),
update the database to match the dictionary object.

idCol is the name of the key column(s).  Will assume a default based on the 
    table name if this not supplied.  Or, supply a list of values if a composite key is used.
idValue has the value of the key columns used to identify the row we wish to update.  
    Again, supply a list if a composite key is used.

Given a table and a list of column names under that table, including the primary key ID column, construct a parameterized SQL update query.

Given a SQL query string and tuple of parameters, replace all of the SQL_PLACEHOLDER strings in the query with the respective representation of the parameters. For the most part, this will just be the string representation of the parameter except in the case of str objects which should be enclosed in ' quote marks, and have all such internal quote marks "escaped" to '

This is usually done internally by the DB-API modules, but this replicates the behavior externally for the purpose of generating query strings.