def dump(connection, file_name, table_name, where_clause=None, column_list=None, log=None):
"""Dump contents of table_name from connection to file_name
If where_clause or column_list are specified then they will be applied to the
generated query to affect the columns and rows returned.
@param connection: Valid database connection
@type connection: DB-API 2.0 compliant database connection
@param file_name: Name of the csv file to write
@type file_name: String
@param table_name: The name of a table accessible via L{connection}
@type table_name: String
@param where_clause: Optional where clause
@type where_clause: String
@param column_list: Optional list of columns to select. If this isn't specified we select all of the columns from L{table_name}
@type column_list: Sequence
@param log: Logging object
@type log: logging.log standard library object
@return: None
"""
if not log:
log = get_log()
log.debug("dump function: Creating cursor object")
cursor = connection.cursor()
stmt = "SELECT "
if column_list:
stmt += ','.join(column_list)
else:
stmt += "*"
stmt += " FROM %s " % table_name
if where_clause:
log.debug("Where clause %s" % where_clause)
stmt += "WHERE " + where_clause
log.debug("Executing %s" % stmt)
cursor.execute(stmt)
columns = [col[0] for col in cursor.description]
dump_to_file(result_iter(cursor, log=log), file_name, columns, log)
def dump_to_file(results, file_name, header_row=None, log=None):
"""Take results and write each element as a comma separated line to file_name
@param results: A sequence (of sequences), usually the results of a database query
@type results: Sequence
@param file_name: Name of file to write output to
@type file_name: String
@param header_row: An (optional) sequence to write as the first row of file_name, usually the names of the fields
@type header_row: Sequence
@param log: Logging object
@type log: logging.log standard library object
@return: None
"""
if not log:
log = get_log()
output_file = open(file_name, "wb") # wb required for correct line endings
csv_writer = csv.writer(output_file, dialect='excel', quoting=csv.QUOTE_NONNUMERIC)
if header_row:
csv_writer.writerow(header_row)
log.debug("Created file %s and written heading row" % file_name)
for row in results:
csv_writer.writerow(row)
output_file.close()
log.debug("Finished dumping to file %s" % file_name)
This module is inspired by (and somewhat borrows from) SQLObject's dbconnection.py, I've just purposely not included a lot of the baggage from that particular module.
This module is licensed under the BSD License (see LICENSE.txt)
To do;
- Add ODBC support via pyodbc - http://pyodbc.sourceforge.net/
"""
__version__ = (0, 4, 0)
__date__ = (2012, 2, 29)
__author__ = "Andy Todd <*****@*****.**>"
import os
# I may replace this with more generic logging
from utilities.Log import get_log
log = get_log(log_name='dburi', level='INFO')
# log = get_log(log_name='dburi', level='DEBUG')
class Connection(object):
def parse_uri(self, connection_string):
"""Turn the connection_string into a series of parameters to the connect method
Note that connection_string values will be of the form
username[:password]@hostname[:instance name][/database name][?key=value[&key=value]]
"""
connection_details = {}
if connection_string.find('@') != -1:
# Split into the username (and password) and the rest
username, rest = connection_string.split('@')
if username.find(':') != -1:
username, password = username.split(':')
if argv is None:
argv = sys.argv
# parse command line options
try:
opts, args = getopt.getopt(argv[1:], "dh", ["debug", "help",])
except getopt.error, msg:
print msg
print "for help use --help"
return 2
# process options
skip = False
check = False
log = None
for o, a in opts:
if o in ("-h", "--help"):
print __doc__
return 0
if o in ("-d", "--debug"):
log = get_log(level='DEBUG')
# process arguments
if not log:
log = get_log()
log.debug("connection string %s" % args[0])
log.debug("file name %s" % args[1])
log.debug("table name %s" % args[2])
connection = get_connection(args[0])
dump(connection, args[1], args[2], log=log)
if __name__ == "__main__":
sys.exit(main())
"""
Module Name: db_refresh
Description: Copy the contents of a series of tables from one database to another
"""
__version__ = (0, 0, 1)
__date__ = (2011, 10, 21)
__author__ = "Andy Todd <*****@*****.**>"
import argparse
import os
import sys
from utilities.Log import get_log, set_level
LOGNAME = 'db_refresh'
log = get_log(LOGNAME, level='INFO')
from utilities import dburi
from utilities import csvDump
class Table(object):
"""
Contains links to our table in the source and target environments and
a number of operations to be carried out on one or the other
Optionally also contains a list of columns within the table, in the event
that we are only interested in a subset of columns (or where there are
different columns in different environments)
"""
def __init__(self, table_name, source_conn, target_conn, file_dir=None, columns=None):
"Create Table object"
