Unmixing Using Non-Negative Matrix Factorization (NMF)

A three-part series on unmixing using NMF.

Part 1: Introduction and Implementation 

Part 2: Evaluating the Number of End Members 

Part 3: Assessing Accuracy of End Members

Sequentially Writing Data to Zip Files with Python

The write() and writestr() methods of the ZipFile class in Python's zipfile library allow the addition of an entire file or a single string as a member file within a zip file.  However, if a large amount of data will be generated dynamically, these methods do not allow separate data items to be written sequentially to a member file within a zip file.

The StreamableZipfile class in the following code snippet provides this missing capability.

import time
import zipfile

class StreamableZipfile(object):
	def __init__(self, zipfile_name, mode='a'):
		# Compression type and level are only available in Python
		# since versions 3.3 and 3.7, respectively.
		self.zf = zipfile.ZipFile(zipfile_name, mode,
				compression=zipfile.ZIP_BZIP2, compresslevel=9)
	def close(self):
		self.zf.close()
	def member_file(self, member_filename):
		# Creates a ZipInfo object (file) within the zipfile
		# and opens it for writing.
		self.current_zinfo = zipfile.ZipInfo(filename=member_filename,
						date_time=time.localtime(time.time())[:6])
		self.current_zinfo.compress_type = self.zf.compression
		self.current_zinfo._compresslevel = self.zf.compresslevel
		self.current_zinfo.file_size = 0
		self.current_handle = self.zf.open(self.current_zinfo, mode='w')
	def close_member(self):
		self.current_handle.close()
	def write(self, str_data):
		# Writes the given text to the currently open member.
		data = str_data.encode("utf-8")
		with self.zf._lock:
			self.current_zinfo.file_size = self.current_zinfo.file_size + len(data)
			self.current_handle.write(data)

This simple implementation does not include any error checking and always compresses the data with the bzip2 algorithm, using the highest compression level.  A more robust and flexible implementation would eliminate these limitations.  Modifications are also needed for versions of Python prior to 3.7.

Use of the StreamableZipfile class is illustrated by the following code, which creates a zip file containing two separate files, where lines are written sequentially to each of the files.

import os

zfname = "Test.zip"
if os.path.isfile(zfname):
	os.remove(zfname)

# Open the streamable zip file and write several lines to a file within it.
zfile = StreamableZipfile(zfname)
zfile.member_file('file1.txt')
zfile.write("This is file 1, line 1\n")
zfile.write("This is file 1, line 2\n")
zfile.write("This is file 1, line 3\n")
zfile.close_member()
zfile.close()


# Open the same zip file and write lines to another file within it.
zfile = StreamableZipfile(zfname)
zfile.member_file('file2.txt')
zfile.write("This is file 2, line 1\n")
zfile.write("This is file 2, line 2\n")
zfile.write("This is file 2, line 3\n")
zfile.close_member()
zfile.close()

Producer-Consumer and Other Algorithms for Import of a CSV File to a Database, in Python

This post presents a comparison of the performance of several different algorithms for import of data from a CSV file into a database. Timings are presented for six different algorithms, run on both PostgreSQL (10.12) and MariaDB (10.3.23).

Import of data into a database is a process that is amenable to application of a producer-consumer algorithm because the steps of reading data from the CSV file and writing data to the database may run at different speeds. Using separate threads to read from the CSV file and write to the database can therefore potentially improve performance over reading and inserting rows one by one, by not requiring the faster process to wait for the slower. In addition, because the Python database API allows multiple rows to be inserted into a database in a single function call (i.e., using the 'executemany()' function), this capability provides an additional opportunity for performance improvement over row-by-row insertion. The algorithms tested here evaluate the effect of producer-consumer algorithms and multi-row insertions, providing a comparison to the simple row-by-row insertion method. The comparative performance of Postgres' COPY command is evaluated also.

The Algorithms 

The following algorithms are evaluated:

1. Postgres' COPY command. 
2. Row-by-row reading and writing. 
3. Buffered reading and writing in a single process. 
4. A producer-consumer algorithm using a single buffer. 
5. A producer-consumer algorithm using two buffers and single-row reading. 
6. A producer-consumer algorithm using two buffers and multi-row reading. 

The row-by-row reading/writing algorithm is the simplest possible method to move the data and requires the least coding. All other algorithms (except Postgres' COPY command) are expected to produce performance that is at least as good as this method.

Buffered reading and writing in a single process reads and writes rows into and from a buffer of specified size. Writing of an entire buffer allows the 'executemany()' function of Python's DB-API to be used, for a possible performance improvement over simple row-by-row reading and writing. Performance of this algorithm is expected to be dependent also on the buffer size used.

The producer-consumer algorithm using a single buffer uses two threads, one of which reads rows from the CSV file and places them into the buffer one by one, and the other of which removes rows from the buffer one by one and inserts them into the database. This is the classic producer-consumer model, of which many examples can be found online.

The first of the double-buffered producer-consumer algorithms uses two buffers. One of the two threads reads rows from the CSV file and places them in a buffer one by one. The other thread writes an entire buffer to the database using the 'executemany()' function. The 'writer' thread controls the use of buffers: whenever it is ready, it locks the buffer used by the 'reader' thread, creates a new empty buffer for the 'reader' thread, releases the lock, and then writes the entire buffer that has just been partially or fully filled by the 'reader' thread.

The second of the double-buffered producer consumer algorithms is similar to the previous one, but it locks and entirely fills the input buffer, rather than filling it row-by-row.

Algorithm Implementation

Python's csv library was used to read the CSV file, for all tests but the first (i.e., the test using Postgres's COPY command). Because the csv library yields empty strings for null inputs, each row read is cleaned to replace empty strings with None before the row is written or placed in the buffer.

Production versions of the producer-consumer algorithms illustrated here should include exception handling in the 'reader' and 'writer' threads. Exception-handling code is omitted from these implementations for brevity and clarity.

Support Classes and Functions

The functions that are used to implement and test the various algorithms have a uniform interface: all of them take arguments identifying the CSV file, the database, and the buffer size to use. (The Postgres COPY command does not use a buffer size specification; although this is configurable, the default is used for these tests.) To accomodate differences in DBMSs, such as the default 'paramstyle' used, and to simplify the dynamic creation of the INSERT statement that is used, custom classes were used to represent the CSV file and database objects.

The CsvFile class automatically opens a file and creates a CSV reader, reads the first row containing column headers, and stores those headers so that they can be used to construct the INSERT statement.

class CsvFile(object):
    def __init__(self, filename):
        self.fn = filename
        self.f = None
        self.open()
        self.rdr = csv.reader(self.f)
        self.headers = next(self.rdr)
    def open(self):
        if self.f is None:
            mode = "rb" if sys.version_info < (3,) else "r"
            self.f = open(self.fn, mode)
    def reader(self):
        return self.rdr
    def close(self):
        self.rdr = None
        self.f.close()
        self.f = None

The Database class and subclasses provide a database connection for each type of DBMS, and a method to construct an INSERT statement for a given CsvFile object, using that DBMS's parameter substitution string.  The conn_info argument is a dictionary containing the host name, user name, and password.

class Database(object):
    def __init__(self, conn_info):
        self.paramstr = '%s'
        self.conn = None
    def insert_sql(self, tablename, csvfile):
        return "insert into %s (%s) values (%s);" % (
                tablename,
                ",".join(csvfile.headers),
                ",".join([self.paramstr] * len(csvfile.headers))
                )

class PgDb(Database):
    def __init__(self, conn_info):
        self.db_type = 'p'
        import psycopg2
        self.paramstr = "%s"
        connstr = "host=%(server)s dbname=%(db)s user=%(user)s password=%(pw)s" % conn_info
        self.conn = psycopg2.connect(connstr)

class MariaDb(Database):
    def __init__(self, conn_info):
        self.db_type = 'm'
        import pymysql
        self.paramstr = "%s"
        self.conn = pymysql.connect(host=conn_info["server"], database=conn_info["db"], port=3306, user=conn_info["user"], password=conn_info["pw"])

CSV Import Functions

All of the following import functions take a CsvFile object and a Database object as their first and second arguments, respectively. Functions that buffer input or output take the buffer size, in number of rows, as their third argument. The 'clean_line()' function used in the import functions converts empty strings to nulls (None in Python).

1. Postgres' COPY command

This implementation takes a CsvFile object as its first argument for consistency with the interface of other functions, but it only uses that object to obtain the underlying filename, and then opens that file directly for use with the 'copy_expert()' function of the psycopg2 library.

    def postgres_copy(csvfile, db):
        curs = db.conn.cursor()
        rf = open(csvfile.fn, "rt")
        # Read and discard headers
        hdrs = rf.readline()
        copy_cmd = "copy copy_test from stdin with (format csv)"
        curs.copy_expert(copy_cmd, rf)

2. Row-by-row reading and writing

This algorithm simply iterates over the rows of the CSV file, reading and writing them one by one.

    def simple_copy(csvfile, db):
        ins_sql = db.insert_sql('copy_test', csvfile)
        curs = db.conn.cursor()
        rdr = csvfile.reader()
        for line in rdr:
            curs.execute(ins_sql, clean_line(line))
        db.conn.commit()

3. Buffered reading and writing in a single process

This algorithm successively fills a buffer with a specified number of rows, and then writes all the rows in a single step using the 'executemany()' method.

    def buffer1_copy(csvfile, db, buflines):
        ins_sql = db.insert_sql('copy_test', csvfile)
        curs = db.conn.cursor()
        rdr = csvfile.reader()
        eof = False
        while True:
            b = []
            for j in range(buflines):
                try:
                    line = next(rdr)
                except StopIteration:
                    eof = True
                else:
                    b.append(clean_line(line))
            if len(b) > 0:
                curs.executemany(ins_sql, b)
            if eof:
                break
        db.conn.commit()

4. A producer-consumer algorithm using a single buffer

This is a classic producer-consumer algorithm, using the Queue class from Python's standard library for simplicity.

    def queue_copy(csvfile, db, q_size):
        ins_sql = db.insert_sql('copy_test', csvfile)
        curs = db.conn.cursor()
        rdr = csvfile.reader()
        buffer = queue.Queue(maxsize=q_size)
        # Have all CSV lines been read?
        read_all = threading.Event()
        def write_to_db():
            while not read_all.is_set() or not buffer.empty():
                line = buffer.get()
                curs.execute(ins_sql, line)
                buffer.task_done()
        def get_from_csv():
            for line in rdr:
                buffer.put(clean_line(line))
            read_all.set()
        writer = threading.Thread(target=write_to_db)
        reader = threading.Thread(target=get_from_csv)
        writer.start()
        reader.start()
        read_all.wait()
        reader.join()
        writer.join()
        db.conn.commit()

5. A producer-consumer algorithm using two buffers and single-row reading

The Queue class cannot be used to implement the double-buffer algorithm, so the Condition class is used instead to coordinate locking of the buffer into which rows are read. The reader thread (function 'get_from_csv()') locks the buffer only long enough to append a single line. The writer thread (function 'write_to_db()') can lock the buffer at any time when it is not empty, so the number of rows written to the database at once may be anywhere between 1 and the maximum size of the buffer.

    def double_buffer_copy(csvfile, db, buf_size):
        ins_sql = db.insert_sql('copy_test', csvfile)
        curs = db.conn.cursor()
        rdr = csvfile.reader()
        buf_lock = threading.Condition()
        read_all = threading.Event()
        # Define an object with a mutable list as a buffer.
        class BufObj(object):
            def __init__(self, buffer):
                self.buffer = buffer
        b = BufObj([])
        def write_to_db():
            while not read_all.is_set() or len(b.buffer) > 0:
                buf_lock.acquire()
                b2 = b.buffer
                b.buffer = []
                buf_lock.notify()
                buf_lock.release()
                curs.executemany(ins_sql, b2)
        def get_from_csv():
            for line in rdr:
                line = clean_line(line)
                buf_lock.acquire()
                while len(b.buffer) == buf_size:
                    buf_lock.wait()
                b.buffer.append(line)
                buf_lock.release()
            read_all.set()
        writer = threading.Thread(target=write_to_db)
        reader = threading.Thread(target=get_from_csv)
        writer.start()
        reader.start()
        read_all.wait()
        reader.join()
        writer.join()
        db.conn.commit()

6. A producer-consumer algorithm using two buffers and multi-row reading

This algorithm is similar to the previous one, except that the reader thread locks the buffer until the buffer is filled or there are no more lines in the CSV file.

    def double_buffer_bulk_copy(csvfile, db, buf_size):
        ins_sql = db.insert_sql('copy_test', csvfile)
        curs = db.conn.cursor()
        rdr = csvfile.reader()
        buf_lock = threading.Condition()
        read_all = threading.Event()
        class BufObj(object):
            def __init__(self, buffer):
                self.buffer = buffer
        b = BufObj([])
        def write_to_db():
            while not read_all.is_set() or len(b.buffer) > 0:
                buf_lock.acquire()
                b2 = b.buffer
                b.buffer = []
                buf_lock.notify()
                buf_lock.release()
                curs.executemany(ins_sql, b2)
        def get_from_csv():
            while not read_all.is_set():
                buf_lock.acquire()
                while len(b.buffer) > 0:
                    buf_lock.wait()
                for j in range(buf_size):
                    try:
                        line = next(rdr)
                    except StopIteration:
                        read_all.set()
                    else:
                        b.buffer.append(clean_line(line))
                buf_lock.release()
        writer = threading.Thread(target=write_to_db)
        reader = threading.Thread(target=get_from_csv)
        writer.start()
        reader.start()
        read_all.wait()
        reader.join()
        writer.join()
        db.conn.commit()

Testing

Tests were conducted using CSV files containing 1,000, 10,000, and 50,000 rows. The target table (and the CSV files) contain columns with character, varchar, date, datetime, time, float, double, boolean, and numeric data types. Some values in the CSV file were null. Text was minimally quoted, and some text values contained embedded double quotes. The average line length was approximately 140 characters. Disk buffers, the memory cache, and the swap file were all cleared before each test. The target database table was dropped and re-created before each test. Both databases used for testing were running on the local machine, to eliminate effects of network transmission time.  Each test was run five times; the average time is reported here.

Buffer Size

Performance of the methods that use buffers for reading or writing (algorithms 3-6) can be expected to depend on the size of the buffer used. The optimum buffer size may further depend on block or buffer sizes used by hard disks, the operating system, and Python itself. The effect of buffer size on performance was evaluated for two algorithms:

• 3. Buffered reading and writing in a single process.
• 5. A producer-consumer algorithm using two buffers and single-row reading. 

The following figure shows the times to import a CSV file with 50,000 rows into Postgres for these two algorithms. Both algorithms reach their maximum speed (minimum time) with a buffer size of 1,500 rows.

The following figure shows the times required by algorithm 3 (buffered reading and writing in a single process) to import data files of different sizes with buffers of different sizes into Postgres.

For this algorithm, minimum import times are achieved at buffer sizes of 1,000 to 1,500 rows.  The size of the buffer becomes less important as the file size decreases.

Performance

Performance tests were carried out using buffer sizes of both 500 and 1,500 rows on both DMBSs. The times required to load a CSV file of 50,000 rows are shown in the following table.

		Average time / 5 runs (seconds)
Algorithm	Buffer size (rows)	Postgres	MariaDB
1. Postgres copy		0.41
2. Simple row-by-row copy		11.95	18.86
3. Buffered read/write in one thread	500	10.34	8.41
4. Producer-consumer, one buffer	500	13.38	19.87
5. Producer-consumer, two buffers, single-row reading	500	10.57	8.78
6. Producer-consumer, two buffers, multi-row reading	500	10.57	9.83
3. Buffered read/write in one thread	1,500	10.17	7.66
4. Producer-consumer, one buffer	1,500	13.26	21.25
5. Producer-consumer, two buffers, single-row reading	1,500	10.27	7.89
6. Producer-consumer, two buffers, multi-row reading	1,500	10.33	7.29

Discussion

Although the reading and writing processes of CSV import would seem to be a suitable application for a producer-consumer algorithm, the producer-consumer algorithm using a single queue (algorithm 4) is the slowest of all of the methods tested--even slower than the row-by-row copying method.  The other producer-consumer algorithms perform better, but buffered reading and writing in a single process (algorithm 3) was generally the fastest method for both DBMSs.  Compared to the simple row-by-row copying method, buffered reading and writing can produce reductions in import time of 10% for Postgres and 60% for MariaDB.  (In production environments, where network transmittal time is also a factor, the fractional reductions will be much less in practice.)

The one case in which buffered reading and writing (algorithm 3) is not the fastest is when using a buffer size of 1,500 rows with MariaDB; in this case the producer-consumer algorithm using two buffers and multi-row reading (algorithm 6) was slightly faster.  Additional testing not shown here indicates that using larger buffer sizes with this algorithm does not result in further increases in performance.

The relatively poor performance of the producer-consumer algorithm using a single buffer (queue) is most likely due to the large disparity in speed between the reading and writing process.  Reading from the CSV file is far faster than writing to the database (tests not shown).  As a result, the single buffer is quickly filled by the reader, and thereafter the writing and reading threads alternate, each handling a single row at a time.  Thus this process reduces to a set of operations similar to row-by-row copying, with additional overhead for managing the multiple threads.

These results show that the psycopg2 and pymysql libraries differ in the improvements to be gained by use of the 'executemany()' function.  With psycopg2, the 'executemany()' provides little performance improvement relative to multiple calls to 'execute()'.  With pymysql, however, 'executemany()' provides a notable performance improvement relative to multiple calls to 'execute()'.  This can be seen in the contrasting performance improvements between algorithms 2 and 3, where 'execute()' is used in the former, and 'executemany()' is used in the latter.

Python DB-API 'type_code' Values

The Python DB-API specifies seven cursor attributes, the second of which is a type_code that should describe the data type of the column.  The DB-API specifies a set of type objects that should be used for the type_code value.  However, different DBMSs report various types of values for the type codes.  The type of information provided as the type_code by several different DBMSs, for several common data types, is shown in the following table.

Data Type	Postgres	MariaDB	SQL Server	Firebird	MS-Access	SQLite
Timestamp with time zone	1184
Timestamp	1184	7		type 'datetime.datetime'		None
Datetime		12	class 'datetime.datetime'		class 'datetime.datetime'	None
Date	1082	10	class 'datetime.date'	type 'datetime.date'	class 'datetime.datetime'	None
Time	1083	11	class 'datetime.time'	type 'datetime.time'	class 'datetime.datetime'	None
Boolean	16	16	class 'bool'		class 'bool'
Small integer	21	1	class 'int'		class 'int'
Integer	23	2	class 'int'	type 'int'	class 'int'	None
Long integer	20	3	class 'int'	type 'long'		None
Single	701	4	class 'float'	type 'float'	class 'float'	None
Double precision	701	5	class 'float'	type 'float'	class 'float'	None
Decimal	1700	0	class 'decimal.Decimal'	class 'decimal.Decimal'		None
Currency	790			class 'decimal.Decimal'	class 'decimal.Decimal'
Character	1042		class 'str'	type 'str'	class 'str'	None
Character varying	1043	15	class 'str'	type 'str'	class 'str'	None
Text	25		class 'str'	type 'str'	class 'str'	None
Binary / BLOB	17	249,250,251,252	class 'bytearray'	type 'str'	type 'bytearray'	None

The Python data types are for Python 2; for Python 3, the 'long' data type will be 'int' instead.

The following libraries were used to connect to the DBMSs:

• Postgres: psycopg2
• MariaDB: pymysql
• SQL Server: pyodbc
• Firebird: fdb
• MS-Access: pyodbc
• SQLite: sqlite3

The integer type_code values used by Postgres are the same as those in the pg_types system table, except for single-precision floating point and character data types, for which the pg_types values are 700 and 8, respectively.

UnboundLocalError in Python 3 When Using an Exception Alias

While modifying execsql to run under Python 3 as well as Python 2, I encountered a version compatibility issue that I did not find described in any readily available online documentation for 2-to-3 conversion. It is:

In Python 3, when an exception is aliased to the same name as an existing variable, that variable is destroyed, and subsequent references to the variable will result in an UnboundLocalError exception.

Here is a little code to illustrate that effect:

class SomeError(Exception):
 def __init__(self, msg):
  self.message = msg

def generate_exception():
 return 'five' / 'two'

def test_local():
 er = None
 try:
  generate_exception()
 except TypeError as er:
  pass
 except:
  er = SomeError("Unexpected exception.")
 if er:
  print("An exception occurred.")

test_local()

This runs successfully in Python 2, but an UnboundLocalError is generated in Python 3.6.5 at the line "if er:".

This is documented in the Python bug tracker as issue 26174 for Python 3.4, but as of that version the exception generated was a NameError rather than an UnboundLocalError.

Building Tkinter Interfaces with Reusable Components from the TkPane Library

Building graphical user interfaces (GUIs) with Python's Tkinter library usually requires a bit of fiddling not only to get the visual layout correct, but also to link the various Tkinter widgets so that they interact properly. Common interactions among widgets include enabling or disabling one based on the state of another, and accessing the data in one widget from another widget. The network of interactions between widgets in a user interface can require painstaking effort to create and modify, and limits the resuability of those user interface components.

Whereas the TkLayout package simplifies creation of the visual layout of a Tkinter GUI, the TkPane package simplifies the creation, use, and re-use of Tkinter widgets and groups of Tkinter widgets, particularly those that are used to collect or manage data, and that need to interact with other components of the GUI.

The TkPane package provides a TkPane class that can be subclassed to create custom 'pane' objects. Several general-purpose panes are provided in the tkpane.lib module. Pane objects are completely stand-alone, with no inherent dependencies on any other components of the GUI. Pane objects have a standard set of methods, however, by which other panes (or other application code) can enable or disable those panes, or clear their displays. Pane objects also have internal lists of callback functions that are automatically executed when their data are changed to become valid, when their data are changed to become invalid, or when the user leaves the pane (via a keyboard focus change or a mouse movement). Dependencies between panes are easily established with the requires() method. For example, the statement

run_button_pane.requires(input_file_pane)

ensures that the run_button_pane pane will be disabled when the input_file_pane pane contains invalid data, and will be enabled when the input_file_pane pane contains valid data. When one pane enables another, the first pane passes its own data to the second pane.

The type of inter-pane interaction that is enabled by the requires() method may be all that is needed in many cases. If more complex interactions are required, however, callback lists can be modified directly to enable other types of inter-pane activation and data sharing.

When a pane object is instantiated, the constructor method (__init()__) must be passed the Tkinter widget within which the frame will be embedded. Typically that parent widget is a frame. This required argument of pane constructors is identical to the argument that must be passed to the 'build' functions of TkLayout objects. Consequently, TkLayout and TkPane objects work together very well, and the combination of the two practically eliminates all of the fiddling with visual and functional aspects of a GUI when using Tkinter directly.

The following example shows how these two packages can be used together to easily build a Tkinter GUI interface by populating layout elements with panes. This example uses pane classes from tkpane.lib.

try:
    import Tkinter as tk
except:
    import tkinter as tk
import tkpane.lib
import tklayout
import time


# Add a method to the AppLayout class to get a pane: the first child of
# a frame's widgets.
def layout_pane(self, pane_name):
    return self.frame_widgets(pane_name)[0]

tklayout.AppLayout.pane = layout_pane


# Create simple sets of data to display in the TableDisplayPane.
ds1_headers = ["Title", "Author", "Published"]
ds1_data = [["Kon-Tiki: Across the Pacific in a Raft", "Thor Heyerdahl", 1950],
            ["Mawson's Will", "Lennard Bickel", 2000],
            ["Southern Cross to Pole Star - Tschiffely's Ride", "A. F. Tschiffely", 1933],
            ["The Hitchhiker's Guide to the Galaxy", "Douglas Adams", 1979]]

ds2_headers = ["row_id","row_number","long_text","some_date","some_number"]
ds2_data = [
           ["Row 4",4,"Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.","1951-03-19",61.9917173461],
           ["Row 8",8,"Nullam dictum felis eu pede mollis pretium. Integer tincidunt. Cras dapibus.","1977-07-21",34.5729855806],
           ["Row 12",12,"DJs flock by when MTV ax quiz prog.","1983-10-12",2.3773967111]
           ]

def build_message_pane(parent):
    tkpane.lib.MessagePane(parent, "Enter user credentials and an output directory.")

def build_table_pane(parent):
    tkpane.lib.TableDisplayPane(parent, "This is an example message to accompany the data table that is long enough that it should wrap when the window is resized to a relatively small size.", ds1_headers, ds1_data)

def build_button_pane(parent):
    def no_action():
        pass
    tkpane.lib.OkCancelPane(parent, no_action, no_action)

# Lay out the panes
lo = tklayout.AppLayout()
inp_panes = lo.row_elements(["user_pane", "output_pane"], row_weight=0)
app = lo.column_elements(["message_pane", inp_panes, "table_pane", 
                          "button_pane", "status_pane"], 
                          row_weights=[0,0,1,0,0])

root = tk.Tk()
root.title("Demo of the TkPane Package")

# Use an extra frame within the root element with padding to add extra space
# around the outermost app widgets.
appframe = tk.Frame(root, padx=11, pady=11)
appframe.pack(expand=True, fill=tk.BOTH)

lo.create_layout(appframe, app)

lo.build_elements({"message_pane": build_message_pane,
                   "user_pane": tkpane.lib.UserPane,
                   "output_pane": tkpane.lib.OutputDirPane,
                   "table_pane": build_table_pane,
                   "button_pane": build_button_pane,
                   "status_pane": tkpane.lib.StatusProgressPane
                   })

# Get the pane objects for customization.
user_pane = lo.pane("user_pane")
output_pane = lo.pane("output_pane")
button_pane = lo.pane("button_pane")
status_pane = lo.pane("status_pane")
table_pane = lo.pane("table_pane")

# Require a user name and output directory to be entered for the 'OK' button
# to be enabled.
button_pane.requires(user_pane)
button_pane.requires(output_pane)

# Start the demo application with the 'OK' button disabled.
tkpane.en_or_dis_able_all([button_pane])

# Make the user and output directory panes report their status.
user_pane.status_reporter = status_pane
output_pane.status_reporter = status_pane

# Make the buttons change the data and the status bar.
def ok_click(*args):
    status_pane.set_status("OK button clicked.")
    time.sleep(0.5)
    status_pane.set_status("Working...")
    for p in range(10, 110, 10):
        status_pane.set_value(p)
        root.update_idletasks()
        time.sleep(0.5)
    status_pane.set_status("Done.")
    table_pane.display_data(ds2_headers, ds2_data)
button_pane.set_ok_action(ok_click)

def cancel_click(*args):
    status_pane.clear([])
button_pane.set_cancel_action(cancel_click)

# Bind  and  to the buttons.
root.bind("", ok_click)
root.bind("", cancel_click)

# Run the application
root.mainloop()

This code produces a GUI with the initial appearance shown below.  The background of the output directory entry is automatically colored to indicate that it is required but does not contain valid data.

As this example illustrates, the re-usability of pane classes allows Tkinter GUIs to be created with almost no Tkinter code.

The TkPane package can be downloaded from the Python Package Index (PyPI), or installed with pip.

pip install tkpane

The documentation for TkPane is available on ReadTheDocs.

Building Tkinter Interfaces from the Inside Out

Graphical user interfaces (GUIs) ordinarily consist of a number of different elements, such as those illustrated in Figure 1. Some GUI development toolkits, specifically including Python's Tkinter module, require these elements to be assembled from the outside in. That is, the outermost frame must be defined first, and that first frame then populated with any container elements (e.g., additional frames), and then those second-level frames populated next, and so forth.

Figure 1. Sketch of example GUI.
Such an interface may be easier to describe from the inside out, though, rather than from the outside in. For example, the GUI shown in Figure 1 could be described as:

group_1 ← column_of (topic_selection, date_selection)
group_2 ← row_of (group_1, format_options)
group_3 ← column_of (header, group_2, output_selection, buttons)

The group_3 element then describes the entire GUI, including the nested hierarchy of other elements.

The TkLayout Python package allows GUIs to be built by describing them in just this way. The key to this process is separation of the steps of describing the interface and creating the interface.

To describe the interface, the GUI implementer must first assign a name to each element of the GUI. For the example shown in Figure 1, reasonable names would be "topic_selection", "date_selection", etc. The interface is then described using methods of an AppLayout object as follows:

group_1 = column_elements("topic_selection", "date_selection")
group_2 = row_elements(group_1, "format_options")
group_3 = column_elements("header", group_2, "output_selection", "buttons")

The row_elements and column_elements methods synthesize and return new element names that identify the element groupings that are created—i.e., group_1, group_2, and group_3. After the structure of the GUI has been described, the frames that implement that structure are created with another method of the AppLayout object, as follows:

create_layout(root, group_3)

where the root argument is a Tkinter top level object or other container that will contain the GUI.

The create_layout method creates a series of nested frames, as shown in Figure 2 for the GUI illustrated in Figure 1.

Figure 2. Frame hierarchy for the GUI in Figure 1.

After this set of frames is created, the enclosing frame for each element can be obtained from the AppLayout object using that element's name. The GUI implementer can then populate each of these frames with other GUI widgets as appropriate. The AppLayout class will also take a dictionary of element names and element-construction functions, and run those functions to populate each element's enclosing frame.  The functions that create the elements for each frame require only the enclosing frame object, and do not need to know anything about how the elements are laid out in relation to one another.

Separation of structure description and implementation in this way allows the GUI construction process to be greatly simplified.  Exploration of alternate interface layouts is also simplified: each alternate layout can be described in just a few lines of code, without any changes required to code to populate or arrange the interface elements.

The TkLayout package can be downloaded from the Python Package Index (PyPI) or installed with the command

pip install tklayout

The documentation is available on ReadTheDocs.

Ordering Database Tables by Foreign Key Dependencies

Some database operations that affect multiple tables must touch the affected tables in an order corresponding to their dependencies. For example, data must be loaded into parent tables before data can be loaded into child (dependent) tables. Some deletion and update operations may also need to be carried out in dependency order. A list of all tables in dependency order can be useful to automate such operations. Several methods are shown here for generating a list of tables in dependency order; these methods are:

• Python code
• A CTE
• An execsql script.

All of these methods take, as input, a table of dependencies in which each row contains a pair of table names corresponding to a parent:child relationship. The Python code produces a Python list containing tables listed in dependency order, and the other two methods produce a table consisting of one column containing table names and another column containing integers that specify the dependency order. The first items in these lists are the tables that have no parents, and the last items in these lists are the tables with the longest chain of dependencies.

Getting the Dependencies

When using a DBMS that supports INFORMATION_SCHEMA tables, the following SQL can be used to obtain a table containing all direct parent:child pairs

create table dependencies as
select 
        tc.table_name as child,
        tu.table_name as parent
from 
        information_schema.table_constraints as tc
        inner join information_schema.constraint_table_usage as tu
             on tu.constraint_name = tc.constraint_name
where 
        tc.constraint_type = 'FOREIGN KEY'
        and tc.table_name <> tu.table_name;

Additional constraints may be used, or needed, in the WHERE clause to limit the set of tables returned--for example, to eliminate system tables, or to select only the tables in a particular schema. If there are cyclic dependency relationships among tables, this SQL will not complete, and so in those cases at least one of the tables in the cycle of mutual dependencies should be omitted using an appropriate specification in the WHERE clause.

The table produced by this SQL will include only those tables that are part of some foreign key relationship. Standalone tables that are neither a parent nor a child will not be included. Standalone tables and tables that are omitted because of cyclic dependency relationships can be added to the output of the dependency-ordering step.

Ordering Tables by Dependency

Converting the set of parent:child dependencies into a list of tables in dependency order requires an iterative or recursive traversal of the tree of relationships that is rooted at the tables that have no parents. The table-ordering routines that follow use different approaches:

• A loop over a list of unprocessed dependencies in Python, where that list is modified within the loop.
• A recursive traversal of the tree using a CTE, terminating when the farthest leaves of the tree have been reached.
• Looping using end recursion in the execsql script to traverse the tree in a manner similar to the CTE.

The algorithm used in the Python code is distinct, whereas the algorithms used with the recursive CTE and the execsql script are very similar. (The algorithm used by the Python code can also be implemented using an execsql script, but the code is considerably longer than the implementation shown below.)

Python

The input for the Python code to generate a dependency-ordered list of tables is a table of dependencies consisting of a list of two-element lists or tuples, each containing a child table name and a parent table name. This list might be generated by querying the database (e.g., using the SQL above) or from static configuration data. Given this table of dependencies, a Python list of all of the tables in dependency order can be generated with the following function

def dependency_order(dep_list):
    rem_tables = list(set([t[0] for t in dep_list] + [t[1] for t in dep_list]))
    rem_dep = copy.copy(dep_list)
    sortkey = 1
    ret_list = []
    while len(rem_dep) > 0:
        tbls = [tbl for tbl in rem_tables if tbl not in [dep[0] for dep in rem_dep] ]
        ret_list.extend([ (tb, sortkey) for tb in tbls ])
        rem_tables = [ tbl for tbl in rem_tables if tbl not in tbls ]
        rem_dep = [ dep for dep in rem_dep if dep[1] not in tbls ]
        sortkey += 1
    if len(rem_tables) > 0:
        ret_list.extend([(tb, sortkey) for tb in rem_tables])
    ret_list.sort(cmp=lambda x,y: cmp(x[1], y[1]))
    return [ item[0] for item in ret_list ]

SQL CTE

For DBMSs that support them, a recursive CTE can be used to convert a table of parent:child dependencies into a list of tables in dependency order. The input for the following code should be a table of such dependencies; that table should be named "dependencies". The output of the recursive CTE contains all parent tables, and the remaining tables (that are not parents to any other table) are added in the SELECT statement that uses the CTE.

with recursive dep_depth as (
 select
  dep.child,
  dep.parent,
  1 as lvl
 from
  dependencies as dep
 union all
 select
  dep.child,
  dep.parent,
  dd.lvl + 1 as lvl
 from
  dep_depth as dd
  inner join dependencies as dep on dep.parent = dd.child
 )
select
 table_name,
 table_order
from (
 select
  dd.parent as table_name,
  max(lvl) as table_order
 from
  dep_depth as dd
 group by
  table_name
 union
 select
  dd.child as table_name,
  max(lvl) + 1 as level
 from
  dep_depth as dd
  left join dependencies as dp on dp.parent = dd.child
 where
  dp.parent is null
 group by
  dd.child
 ) as all_levels;

Execsql Script

For DBMSs that don't support recursive CTEs, and when use of a client language like Python is not desired, the metacommands provided by execsql allow recursive traversal of the tree of dependencies, as shown in the following code. Explanations of the metacommands used in this code can be found in the on-line documentation.

As with the CTE implementation, the input for the following code should be a table of parent:child dependencies that is named "dependencies".

The following code increments a counter to track and assign the successive levels of recursion during traversal from the root to the leaves of the dependency tree. Because automatically-generated sequences and variables are DBMS-specific extensions to SQL, for the sake of generality, this implementation uses execsql counter variables and substitution variables. Thus, but for minor differences in SQL syntax, the following code should run in any DBMS.

-- ====================================================================
--  Initialize the tables used to summarize dependency order.
--  Table created:
--    dep_level: A copy of "dependencies" with an additional column
--               to store the level in the hierarchy.  The dependency
--               level is set by an execsql counter variable for
--               generality.
-- ====================================================================
-- !x! sub current_level !!$counter_530!!
select
    child,
    parent,
    !!current_level!! as lvl
into
    temporary table dep_level
from
    dependencies;


-- ====================================================================
--  Create a view to evaluate whether there are any remaining
--  dependencies to evaluate.
--  View created:
--    unprocessed: The number of parent tables whose children are
--                 not already listed as parents in the 'dep_level' table.
--  Tables used:
--    dep_level
--    dependencies
-- ====================================================================
create temporary view unprocessed as
select count(distinct dep.child) as unproc
from
    dep_level as dl
    inner join dependencies as dep on dl.child = dep.parent
where
    dl.lvl = (select max(lvl) from dep_level);


-- ====================================================================
--  Define an execsql sub-script to increment the dependency level.
-- ====================================================================
-- !x! begin script add_new_level
-- !x! sub last_level !!current_level!!
-- !x! sub current_level !!$counter_530!!
insert into dep_level
    (child, parent, lvl)
select distinct
    dep.child,
    dl.child as parent,
    !!current_level!!
from
    dep_level as dl
    inner join dependencies as dep on dl.child = dep.parent
where
    dl.lvl = !!last_level!!;
-- !x! end script


-- ====================================================================
--  Define and execute an execsql sub-script to increment the dependency
--  level as many times as necessary.
-- ====================================================================
-- !x! begin script add_levels
-- !x! subdata remaining unprocessed
-- !x! execute script add_new_level
-- !x! subdata remaining unprocessed
-- !x! if(is_gt(!!remaining!!, 0)) { execute script add_levels }
-- !x! end script

-- !x! execute script add_levels


-- ====================================================================
--  Convert the dependency levels into a table order.
-- ====================================================================
create temporary table dependency_order as
select
 table_name,
 table_order
from (
 select
  dd.parent as table_name,
  max(lvl) as table_order
 from
  dep_level as dd
 group by
  table_name
 union
 select
  dd.child as table_name,
  max(lvl) + 1 as level
 from
  dep_level as dd
  left join dependencies as dp on dp.parent = dd.child
 where
  dp.parent is null
 group by
  dd.child
 ) as all_levels;

Using a Makefile with a Database

A makefile is a convenient and efficient way to run a multi-step process that creates or modifies files.  However, when one step of that process is to extract data from a server-based database, make has no inherent ability to determine when the data in the database has last been changed, and therefore, whether the data extraction step needs to be re-run.

The following Python program (db_updated.py) addresses this problem. It updates the modification date of a specific flag file (named db_updated.flag by default), creating the file if it does not already exist. This code is written to be used with a PostgreSQL database, but can be easily modified for other databases.

The program requires that the database contain at least one table with a column that contains a revision date (and time), either for that table or for the database as a whole. Command-line options can be used to specify the table(s) to be checked, and the name of the column to be checked. If neither is specified, a default table name of "auditlog" is used, and a default column name of "rev_time" is used.

Command-line options and arguments are:

db_updated.py [-t table] [-c column] [-u user] server database

Multiple -t options may be specified; the latest revision date of any of the tables will be used to set the modification date of the db_updated.flag file. If a user name is specified, the program will prompt for a password.


db_updated.py:

import argparse
import psycopg2
import getpass
import os
import os.path
import time

FLAG_FILE = 'db_updated.flag'

def get_user(server_name, database_name):
 user_name = raw_input("User name for %s database %s: " % (server_name, database_name))
 return user_name

def get_password(server_name, database_name, user_name):
 passwd = getpass.getpass("Password for user %s on %s database %s: " % (user_name, server_name, database_name))
 return passwd

def clparser():
 desc_msg = "Create or update the  database flag file if the given database has been recently modified."
 parser = argparse.ArgumentParser(description=desc_msg)
 parser.add_argument('-u', '--db_user', dest='db_user', action='store')
 parser.add_argument('-t', '--table', dest='table', action='append')
 parser.add_argument('-c', '--column', dest='column', action='store')
 parser.add_argument('server', action='store')
 parser.add_argument('database', action='store')
 return parser

def main():
 parser = clparser()
 arg = parser.parse_args()
 if not os.path.exists(FLAG_FILE):
  open(FLAG_FILE, 'a').close()
 if arg.db_user:
  db_user = arg.db_user
  pw = get_password(arg.server, arg.database, db_user)
  conn = psycopg2.connect(host=arg.server, database=arg.database, port=5432, user=unicode(db_user), password=unicode(pw))
 else:
  conn = psycopg2.connect(host=arg.server, database=arg.database, port=5432)
 curs = conn.cursor()
 column = arg.column or "rev_time"
 if arg.table:
  tables = 0
  sql = ''
  for t in arg.table:
   if tables > 0:
    sql = sql + " union "
   tables += 1
   sql = sql + "select max(%s) as latest from %s" % (column, t)
  if tables > 1:
   sql = "select max(latest) from (%s) as all_t" % sql
  sql = sql + ";"
 else:
  sql = "select max(%s) from auditlog;" % column
 curs.execute(sql)
 revtime = time.mktime(curs.fetchone()[0].timetuple())
 conn.close()
 os.utime(FLAG_FILE, (revtime, revtime))

main()

This can be used in a makefile like the example below. This example extracts data from a database and then runs an R script to further summarize the data.  The input and output file names for the R script are shown on the command line here, though encoding them in the script itself may be more convenient in many cases than parsing the command line in R.

.PHONY: all

all: db_updated.flag data_summary.csv
 db_updated.py -u user_name server_name db_name

data_summary.csv: data.csv summary_stats.R
 Rscript --vanilla summary_stats.R data.csv data_summary.csv

data.csv: db_updated.flag get_data.sql
 execsql.py -tp -u user_name get_data.sql server_name db_name

db_updated.flag:
 db_updated.py -u user_name server_name db_name

In this example, neither a table name nor a column name are included as arguments to db_updated.py. These may be needed in some circumstances, or the program modified so that the defaults are appropriate for the circumstances in which it is used.

A Threaded Tkinter Toplevel Console Window

The code below implements a GUI window for output display, such as might be used as a console to display status messages or other information from a running program. It is implemented using a threaded Tkinter Toplevel widget, and is designed to be used in a non-GUI command-line program. The display that it produces looks like this:

The Python code for this GUI console is:

class ConsoleUIError(Exception):
 def __init__(self, msg):
  self.value = msg
 def __repr__(self):
  return ("ConsoleUIError(%r)" % self.value)

class ConsoleUI(object):
 class TkUI(object):
  def __init__(self, kill_event, stop_update_event, msg_queue, status_queue, title=None):
   self.kill_event = kill_event
   self.stop_update_event = stop_update_event
   self.msg_queue = msg_queue
   self.status_queue = status_queue
   import Tkinter as tk1
   import ttk as ttk1
   self.win = tk1.Toplevel()
   self.status_msg = tk1.StringVar()
   self.status_msg.set('')
   self.win.title(title if title else "execsql console")
   console_frame = ttk1.Frame(master=self.win, padding="2 2 2 2")
   console_frame.grid(column=0, row=0, sticky=tk1.NSEW)
   self.textarea = tk1.Text(console_frame, width=100, height=25, wrap='none')
   # Status bar
   statusframe = ttk1.Frame(master=self.win)
   statusbar = ttk1.Label(statusframe, text='', textvariable=self.status_msg, 
    relief=tk1.RIDGE, anchor=tk1.W)
   statusbar.pack(side=tk1.BOTTOM, fill=tk1.X)
   statusframe.grid(column=0, row=1, sticky=tk1.EW)
   # Scrollbars
   vscroll = tk1.Scrollbar(console_frame, orient="vertical", command=self.textarea.yview)
   hscroll = tk1.Scrollbar(console_frame, orient="horizontal", command=self.textarea.xview)
   self.textarea.configure(yscrollcommand=vscroll.set)
   self.textarea.configure(xscrollcommand=hscroll.set)
   self.textarea.grid(column=0, row=0, sticky=tk1.NSEW)
   vscroll.grid(column=1, row=0, sticky=tk1.NS)
   hscroll.grid(column=0, row=2, sticky=tk1.EW)
   # Allow resizing
   self.win.columnconfigure(0, weight=1)
   self.win.rowconfigure(0, weight=1)
   console_frame.columnconfigure(0, weight=1)
   console_frame.rowconfigure(0, weight=1)
   # Kill on window close
   self.win.protocol("WM_DELETE_WINDOW", self.kill)
   # Display and center the window
   self.win.update_idletasks()
   m = re.match("(\d+)x(\d+)([-+]\d+)([-+]\d+)", self.win.geometry())
   wwd = int(m.group(1))
   wht = int(m.group(2))
   swd = self.win.winfo_screenwidth()
   sht = self.win.winfo_screenheight()
   xpos = (swd/2) - (wwd/2)
   ypos = (sht/2) - (wht/2)
   self.win.geometry("%dx%d+%d+%d" % (wwd, wht, xpos, ypos))
   self.win.grab_set()
   self.win._root().withdraw()
   self.update_id = self.win.after(200, self.update)
   self.win.wait_window(self.win)
  def kill(self):
   if self.update_id:
    self.win.after_cancel(self.update_id)
    self.update_id = None
   self.win.destroy()
   self.win.update_idletasks()
  def update(self):
   self.update_id = None
   while not self.msg_queue.empty():
    msg = self.msg_queue.get(False)
    self.textarea.insert('end', msg)
    self.textarea.see('end')
    self.msg_queue.task_done()
   while not self.status_queue.empty():
    msg = self.status_queue.get(False)
    self.status_msg.set(msg)
   if self.kill_event.is_set():
    self.kill()
   else:
    if not self.stop_update_event.is_set():
     self.update_id = self.win.after(200, self.update)
 def __init__(self, title=None):
  self.title = title
  self.msg_queue = Queue.Queue()
  self.status_queue = Queue.Queue()
  self.kill_event = threading.Event()
  self.stop_update_event = threading.Event()
  self.consolethread = None
  self.update_id = None
  # Start the local event loop in a thread.
  def openconsole():
   self.active = True
   self.ui = self.TkUI(self.kill_event, self.stop_update_event, self.msg_queue, 
    self.status_queue, self.title)
   # Deallocate the Tk object here to avoid the "main thread is not in main loop" error.
   self.ui = None
  self.consolethread = threading.Thread(target=openconsole)
  self.consolethread.start()
 def write(self, msg):
  self.active = self.consolethread and self.consolethread.is_alive()
  if not self.active:
   raise ConsoleUIError(msg)
  self.msg_queue.put(msg)
 def write_status(self, msg):
  self.active = self.consolethread and self.consolethread.is_alive()
  if not self.active:
   raise ConsoleUIError(msg)
  self.status_queue.put(msg)
 def deactivate(self):
  self.kill_event.set()
  if self.consolethread and self.consolethread.is_alive():
   self.consolethread.join()
  self.active = False
 def wait_for_user_quit(self):
  self.stop_update_event.set()
  if self.consolethread and self.consolethread.is_alive():
   self.consolethread.join()
  self.active = False

Because it runs in its own thread, this console can be used to display information produced by the main program or even several other separate processes.

The 'write()' method of the ConsoleUI object will write the given text at the end of the console display.  A status message can also be written, separately from the stream of text that is written in the main part of the window.  The console window can be closed directly from the program with the 'deactivate()' method, or the program can pause until the user closes the window by using the 'wait_for_user_quit()' method.

Other Tkinter widgets should not be activated in other threads while this GUI console window is open.

execsql

execsql is a Python application that reads a text file of SQL commands and executes them against a database. The supported databases are:

• PostgreSQL
• MS-Access
• MS-SQL Server
• SQLite
• MySQL or MariaDB
• Firebird
• ODBC DSN connections.

In addition to executing SQL statements, execsql also implements a number of metacommands that allow:

• Import of data from text files and OpenDocument spreadsheets
• Export of data to delimited text, HTML, JSON, LaTeX tables, and OpenDocument spreadsheets
• Copying of data between databases, even of different DBMS types
• Conditional execution of SQL code and metacommands based on data values or user input.

execsql allows variables to be defined and used to dynamically modify SQL code and metacommands. An INCLUDE metacommand can be used to modularize SQL scripts and facilitate code re-use. Variables can be used to parameterize included scripts. Conditional inclusion of scripts can be used to implement loops. Automatically incrementing counter variables can be used to control loops or generate unique values to be used in input or output.

execsql is fundamentally a command-line application that can be used to automate database operations, either by itself or as part of a toolchain that may include other steps to either pre-process data before loading, or post-process or analyze data that are extracted from a database. However, execsql also has the ability to present data to the user in dialog boxes, request several types of input from the user either on the terminal or in GUI dialogs, and display messages to the user either on the terminal or in a GUI console. These features allow flexible interactive applications to be created.

A guiding principle in the development of execsql is documentation of all data operations. The use of SQL scripts, rather than ad-hoc interactive operations in a GUI, is key to meeting that goal. In addition, execsql logs all usage information, including databases used, script files executed (including script file version information), variable assignments, user input, and errors. Chain-of-custody procedures are used in some disciplines to ensure traceability from collection through (typically) laboratory analysis. However, no formal chain of custody procedures ordinarily are applied to data during or after entry into a database. SQL script files and execsql log files can be used to produce a complete record of data operations so that traceability of data can be extended into the database environment.

execsql and its documentation are available from the Python Package Index (PyPI). Note that the search functionality in PyPI and in pip search is broken, and will return links to obsolete versions of execsql that are no longer available. The direct link will take you to the latest available version.

An alternate Python CSV sniffer

I recently came across a case where the format sniffer in Python's csv module was not satisfactory.  The problem is that the format sniffer always identifies a quote character, even when there is no quote character in the CSV file (e.g., in the case of a tab-delimited file).  By default the format sniffer reports that a double-quote character is used.  This is not a problem if you subsequently use the csv module itself to read the file.  However, I wanted to use the csv module only if the file contained quote characters, and use a different method (the copy_from method of the psycopg module) if the file contained no quote characters.  I could not make this distinction using the csv module's format sniffer, so I wrote the following one to use instead.

import re

class CsvDiagError(Exception):
 def __init__(self, msg):
  self.value = msg
 def __str__(self):
  return self.value

class CsvLine():
 escchar = u"\\"
 def __init__(self, line_text):
  self.text = line_text
  self.delim_counts = {}
  self.item_errors = []  # A list of error messages.
 def __str__(self):
  return u"; ".join([u"Text: <<%s>>" % self.text, \
   u"Delimiter counts: <<%s>>" % ", ".join([u"%s: %d" % (k, self.delim_counts[k]) for k in self.delim_counts.keys()]) ])
 def count_delim(self, delim):
  # If the delimiter is a space, consider multiple spaces to be equivalent
  # to a single delimiter, split on the space(s), and consider the delimiter
  # count to be one fewer than the items returned.
  if delim == u" ":
   self.delim_counts[delim] = max(0, len(re.split(r' +', self.text)) - 1)
  else:
   self.delim_counts[delim] = self.text.count(delim)
 def delim_count(self, delim):
  return self.delim_counts[delim]
 def _well_quoted(self, element, qchar):
  # A well-quoted element has either no quotes, a quote on each end and none
  # in the middle, or quotes on both ends and every internal quote is either
  # doubled or escaped.
  # Returns a tuple of three booleans; the first indicates whether the element is
  # well-quoted, the second indicates whether the quote character is used
  # at all, and the third indicates whether the escape character is used.
  if qchar not in element:
   return (True, False, False)
  if len(element) == 0:
   return (True, False, False)
  if element[0] == qchar and element[-1] == qchar and qchar not in element[1:-1]:
   return (True, True, False)
  # The element has quotes; if it doesn't have one on each end, it is not well-quoted.
  if not (element[0] == qchar and element[-1] == qchar):
   return (False, True, False)
  e = element[1:-1]
  # If there are no quotes left after removing doubled quotes, this is well-quoted.
  if qchar not in e.replace(qchar+qchar, u''):
   return (True, True, False)
  # if there are no quotes left after removing escaped quotes, this is well-quoted.
  if qchar not in e.replace(self.escchar+qchar, u''):
   return (True, True, True)
  return (False, True, False)
 def record_format_error(self, pos_no, errmsg):
  self.item_errors.append(u"%s in position %d." % (errmsg, pos_no))
 def items(self, delim, qchar):
  # Parses the line into a list of items, breaking it at delimiters that are not
  # within quoted stretches.  (This is a almost CSV parser, for valid delim and qchar,
  # except that it does not eliminate quote characters or reduce escaped quotes.)
  self.item_errors = []
  if qchar is None:
   if delim is None:
    return self.text
   else:
    if delim == u" ":
     return re.split(r' +', self.text)
    else:
     return self.text.split(delim)
  elements = []  # The list of items on the line that will be returned.
  eat_multiple_delims = delim == u" "
  # States of the FSM:
  # _IN_QUOTED: An opening quote has been seen, but no closing quote encountered.
  #  Actions / transition:
  #   quote: save char in escape buffer / _ESCAPED
  #   esc_char : save char in escape buffer / _ESCAPED
  #   delimiter: save char in element buffer / _IN_QUOTED
  #   other: save char in element buffer / _IN_QUOTED
  # _ESCAPED: An escape character has been seen while _IN_QUOTED (and is in the escape buffer).
  #  Actions / transitions
  #   quote: save escape buffer in element buffer, empty escape buffer, 
  #    save char in element buffer / _IN_QUOTED
  #   delimiter: save escape buffer in element buffer, empty escape buffer,
  #    save element buffer, empty element buffer / _BETWEEN
  #   other: save escape buffer in element buffer, empty escape buffer,
  #    save char in element buffer / _IN_QUOTED
  # _QUOTE_IN_QUOTED: A quote has been seen while _IN_QUOTED (and is in the escape buffer).
  #  Actions / transitions
  #   quote: save escape buffer in element buffer, empty escape buffer, 
  #    save char in element buffer / _IN_QUOTED
  #   delimiter: save escape buffer in element buffer, empty escape buffer,
  #    save element buffer, empty element buffer / _DELIMITED
  #   other: save escape buffer in element buffer, empty escape buffer,
  #    save char in element buffer / _IN_QUOTED
  #     (An 'other' character in this position represents a bad format:
  #     a quote not followed by another quote or a delimiter.)
  # _IN_UNQUOTED: A non-delimiter, non-quote has been seen.
  #  Actions / transitions
  #   quote: save char in element buffer / _IN_UNQUOTED
  #    (This represents a bad format.)
  #   delimiter: save element buffer, empty element buffer / _DELIMITED
  #   other: save char in element buffer / _IN_UNQUOTED
  # _BETWEEN: Not in an element, and a delimiter not seen.  This is the starting state,
  #   and the state following a closing quote but before a delimiter is seen.
  #  Actions / transition:
  #   quote: save char in element buffer / _IN_QUOTED
  #   delimiter: save element buffer, empty element buffer / _DELIMITED
  #    (The element buffer should be empty, representing a null data item.)
  #   other: save char in element buffer / _IN_UNQUOTED
  # _DELIMITED: A delimiter has been seen while not in a quoted item.
  #  Actions / transition:
  #   quote: save char in element buffer / _IN_QUOTED
  #   delimiter: if eat_multiple: no action / _DELIMITED
  #     if not eat_multiple: save element buffer, empty element buffer / _DELIMITED
  #   other: save char in element buffer / _IN_UNQUOTED
  # At end of line: save escape buffer in element buffer, save element buffer.  For a well-formed
  # line, these should be empty, but they may not be.
  #
  # Define the state constants, which will also be used as indexes into an execution vector.
  _IN_QUOTED, _ESCAPED, _QUOTE_IN_QUOTED, _IN_UNQUOTED, _BETWEEN, _DELIMITED = range(6)
  #
  # Because of Python 2.7's scoping rules:
  # * The escape buffer and current element are defined as mutable objects that will have their
  #  first elements modified, rather than as string variables.  (Python 2.x does not allow
  #  modification of a variable in an enclosing scope that is not the global scope, but
  #  mutable objects like lists can be altered.  Another approach would be to implement this
  #  as a class and use instance variables.)
  # * The action functions return the next state rather than assigning it directly to the 'state' variable.
  esc_buf = [u'']
  current_element = [u'']
  def in_quoted():
   if c == self.escchar:
    esc_buf[0] = c
    return _ESCAPED
   elif c == qchar:
    esc_buf[0] = c
    return _QUOTE_IN_QUOTED
   else:
    current_element[0] += c
    return _IN_QUOTED
  def escaped():
   if c == delim:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    elements.append(current_element[0])
    current_element[0] = u''
    return _BETWEEN
   else:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    return _IN_QUOTED
  def quote_in_quoted():
   if c == qchar:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    self.record_format_error(i+1, "Unexpected character following a closing quote")
    return _IN_QUOTED
  def in_unquoted():
   if c == delim:
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  def between():
   if c == qchar:
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  def delimited():
   if c == qchar:
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    if not eat_multiple_delims:
     elements.append(current_element[0])
     current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  # Functions in the execution vector must be ordered identically to the
  # indexes represented by the state constants.
  exec_vector = [ in_quoted, escaped, quote_in_quoted, in_unquoted, between, delimited ]
  # Set the starting state.
  state = _BETWEEN
  # Process the line of text.
  for i, c in enumerate(self.text):
   state = exec_vector[state]()
  # Process the end-of-line condition.
  if len(esc_buf[0]) > 0:
   current_element[0] += esc_buf[0]
  if len(current_element[0]) > 0:
   elements.append(current_element[0])
  return elements
 def well_quoted_line(self, delim, qchar):
  # Returns a tuple of boolean, int, and boolean, indicating: 1) whether the line is
  # well-quoted, 2) the number of elements for which the quote character is used,
  # and 3) whether the escape character is used.
  wq = [ self._well_quoted(el, qchar) for el in self.items(delim, qchar) ]
  return ( all([b[0] for b in wq]), sum([b[1] for b in wq]), any([b[2] for b in wq]) )


def diagnose_delim(linestream, possible_delimiters=None, possible_quotechars=None):
 # Returns a tuple consisting of the delimiter, quote character, and escape
 # character for quote characters within elements of a line.  All may be None.
 # If the escape character is not None, it will be u"\".
 # Arguments:
 # * linestream: An iterable file-like object with a 'next()' method that returns lines of text
 #  as bytes or unicode.
 # * possible_delimiters: A list of single characters that might be used to separate items on
 #  a line.  If not specified, the default consists of tab, comma, semicolon, and vertical rule.
 #  If a space character is included, multiple space characters will be treated as a single
 #  delimiter--so it's best if there are no missing values on space-delimited lines, though
 #  that is not necessarily a fatal flaw unless there is a very high fraction of missing values.
 # * possible_quotechars: A list of single characters that might be used to quote items on
 #  a line.  If not specified, the default consists of single and double quotes.
 if not possible_delimiters:
  possible_delimiters = [ u"\t", u",", u";", u"|"]
 if not possible_quotechars:
  possible_quotechars = [ u'"', u"'"]
 lines = []
 for i in range(100):
  try:
   ln = linestream.next()
  except StopIteration:
   break
  except:
   raise
  while len(ln) > 0 and ln[-1] in (u"\n", u"\r"):
   ln = ln[:-1]
  if len(ln) > 0:
   lines.append(CsvLine(ln))
 if len(lines) == 0:
  raise CsvDiagError(u"CSV diagnosis error: no lines read")
 for ln in lines:
  for d in possible_delimiters:
   ln.count_delim(d)
 # For each delimiter, find the minimum number of delimiters found on any line, and the number of lines 
 # with that minimum number
 delim_stats = {}
 for d in possible_delimiters:
  dcounts = [ ln.delim_count(d) for ln in lines ]
  min_count = min(dcounts)
  delim_stats[d] = (min_count, dcounts.count(min_count))
 # Remove delimiters that were never found.
 for k in delim_stats.keys():
  if delim_stats[k][0] == 0:
   del(delim_stats[k])
 def all_well_quoted(delim, qchar):
  # Returns a tuple of boolean, int, and boolean indicating: 1) whether the line is
  # well-quoted, 2) the total number of lines and elements for which the quote character
  # is used, and 3) the escape character used.
  wq = [ l.well_quoted_line(delim, qchar) for l in lines ]
  return ( all([b[0] for b in wq]), sum([b[1] for b in wq]), CsvLine.escchar if any([b[2] for b in wq]) else None )
 def eval_quotes(delim):
  # Returns a tuple of the form to be returned by 'diagnose_delim()'.
  ok_quotes = {}
  for q in possible_quotechars:
   allwq = all_well_quoted(delim, q)
   if allwq[0]:
    ok_quotes[q] = (allwq[1], allwq[2])
  if len(ok_quotes) == 0:
   return (delim, None, None) # No quotes, no escapechar
  else:
   max_use = max([ v[0] for v in ok_quotes.values() ])
   if max_use == 0:
    return (delim, None, None)
   # If multiple quote characters have the same usage, return (arbitrarily) the first one.
   for q in ok_quotes.keys():
    if ok_quotes[q][0] == max_use:
     return (delim, q, ok_quotes[q][1])
 if len(delim_stats) == 0:
  # None of the delimiters were found.  Some other delimiter may apply,
  # or the input may contain a single value on each line.
  # Identify possible quote characters.
  return eval_quotes(None)
 else:
  if len(delim_stats) > 1:
   # If one of them is a space, prefer the non-space
   if u" " in delim_stats.keys():
    del(delim_stats[u" "])
  if len(delim_stats) == 1:
   return eval_quotes(delim_stats.keys()[0])
  # Assign weights to the delimiters.  The weight is the square of the minimum number of delimiters
  # on a line times the number of lines with that delimiter.
  delim_wts = {}
  for d in delim_stats.keys():
   delim_wts[d] = delim_stats[d][0]**2 * delim_stats[d][1]
  # Evaluate quote usage for each delimiter, from most heavily weighted to least.
  # Return the first good pair where the quote character is used.
  delim_order = sorted(delim_wts, key=delim_wts.get, reverse=True)
  for d in delim_order:
   quote_check = eval_quotes(d)
   if quote_check[0] and quote_check[1]:
    return quote_check
  # There are no delimiters for which quotes are OK.
  return (delim_order[0], None, None)
 # Should never get here
 raise CsvDiagError(u"CSV diagnosis error: an untested set of conditions are present")