MySQL/CUPS PDF printer for STAR TSP100 ECO

This is my very first attempt at Python and whilst the script works perfectly, I'd like to see if I'm missing anything obvious or doing anything particularly stupid. I think this runs on Python 2.7, but I'm not 100% sure.

Goal:
The goal here is to grab a task and its elements (records contain BLOB PDF data) and write them all to CUPS as a single print job for a receipt printer, starting with a plaintext header, followed by a list of PDF files and followed by a plaintext header.

Finally, the code should mark the task as printed in the database and remove all blob-data associated with the task as to prevent my database from turning into a massive waste of space. It should likewise delete the folder containing the 'downloaded' PDFs and header/footer files.

```
import os
import shutil
import MySQLdb
import cups

printername = "STARTTSP100"
dbAddress = "nope"
dbUser = "nope"
dbPasswd = "nope"
dbDatabase = "printer"

# Connect to the Printer DB
db = MySQLdb.connect(
host=dbAddress,
user=dbUser,
passwd=dbPasswd,
db=dbDatabase
)

curOne = db.cursor()
curOne.execute("SELECT task.id, task.header, task.footer FROM task WHERE task.printDate IS NULL ORDER BY task.regDate ASC LIMIT 5")
for row in curOne:

printList = []

if not os.path.exists(row[0]):
os.makedirs(row[0])

header = open(row[0]+"/"+row[0]+"_header.txt","w")
header.write(row[1])
header.close()
printList.append(row[0]+"/"+row[0]+"_header.txt")

curTwo = db.cursor()
curTwo.execute("SELECT elements.id, elements.count, elements.data FROM elements WHERE task = '"+row[0]+"' ORDER BY count ASC")

for subrow in curTwo:
tempPdf = open(row[0]+"/"+subrow[0]+".pdf","wb")
tempPdf.write(subrow[2])
tempPdf.close()
for x in range(0,subrow[1]):
printList.append(row[0]+"/"+subrow[0]+".pdf")

footer = open(row[0]+"/"+row[0]+"_footer.txt","w")
footer.write(row[2])
footer.close()
printList.append(row[0]+"/"+row[

I would put the configuration of user, password, host, etc into a dictionary. This allows you to load this dictionary from a file, if you don't want to have the password in the code. It makes it also easy to pass it to a db connect function:

# Hard-coded here, but might as well be retrieved from a config file
CONF = {'host': "nope",
        'user': "nope",
        'passwd': "nope",
        'db': "printer"}

def connect(**conf):
    """# Connect to the Printer DB"""
    return MySQLdb.connect(**conf)

db = connect(**conf)

The connect function is even transparent, so you can also pass additional keyword-arguments:

db = connect(raise_on_warnings=True, **conf)

I would split up the main part of the code into separate functions. One to create the files to print, which returns the list of files to print. And a second function for printing that task and cleaning up the task.

The first function is to_print. It yields (instead of returning the list) the files to print (as a list) for every task. Note that I re-used the cursor, since there is no need to create a new one every time.

I also defined a partial function using functools, which makes creating a file_name following the format task_id/file_name a bit easier. I also used str.format to avoid potentially costly string additions (I don't know how large your task_ids are.

In addition I used tuple unpacking to give row[0], row[1] and row[2] meaningful names.

And finally, you should use with..as to automatically close a file after you are finished with it.

def to_print(db):
    sub_query = "SELECT elements.id, elements.count, elements.data FROM elements WHERE task = '%s' ORDER BY count ASC"

    cursor = db.cursor()
    cursor.execute(
        "SELECT task.id, task.header, task.footer FROM task WHERE task.printDate IS NULL ORDER BY task.regDate ASC LIMIT 5")
    for task_id, header, footer in cursor:
        path_join = functools.partial(os.path.join, task_id)
        print_list = []
        if not os.path.exists(task_id):
            os.makedirs(task_id)

        header_file_name = "{}_header.txt".format(path_join(task_id))
        with open(header_file_name, "w") as header_file:
            header_file.write(header)
        print_list.append(header_file_name)

        cursor2 = db.cursor()
        cursor2.execute(sub_query, (task_id,))
        for file_name, elements_count, data in cursor2:
            pdf_file_name = "{}.pdf".format(path_join(file_name))
            with open(pdf_file_name, "wb") as pdf_file:
                pdf_file.write(data)
            print_list.extend([pdf_file_name for _ in range(elements_count)])
        cursor2.close()

        footer_file_name = "{}_footer.txt".format(path_join(task_id))
        with open(footer_file_name, "w") as footer_file:
            footer_file.write(footer)
        print_list.append(footer_file_name)

        yield task_id, print_list
    cursor.close()

The second function marks a task as finished:

def mark_finished(db, task_id):
    cursor = db.cursor()
    cursor.execute(
        "UPDATE task SET printDate = NOW() WHERE id = '%s'", (task_id,))
    cursor.execute(
        "UPDATE elements SET data = null WHERE task = '%s'", (task_id,))
    cursor.close()
    shutil.rmtree(task_id)

It is basically the same as before, only now I re-use the cursor and use the functionality of cursor.execute that you can give it data in the sprintf-like format "name: %s" % ("Graipher",).

Finally, I would put the code calling all of this in a if __name__ == "__main__" guard to allow importing you module from another script.

Here is also the usage of the generator returned by to_print, we just iterate over the tasks. The actual printing is inlined here, you could also stick it into a function.

if __name__ == "__main__":
    db = connect(**CONF)
    for task_id, print_list in to_print(db):
        cups.Connection().printFiles(PRINTER, print_list, "expedition-" + task_id, {})
        mark_finished(db, task_id)
    db.commit()
    db.close()

    print("done")

This can be broken down into even smaller tasks (the writing of the files is still some-what redundant for example), which I did in my final code:

```
import os
import shutil
import MySQLdb
import cups
import functools
from contextlib import closing

PRINTER = "STARTTSP100"

# Hard-coded here, but might as well be retrieved from a config file
CONF = {'host': "nope",
'user': "nope",
'passwd': "nope",
'db': "printer"}

def connect(**conf):
"""# Connect to the Printer DB"""
return MySQLdb.connect(**conf)

def create_file(task_id, extension, data):
file_name = "{}{}".format(os.path.join(task_id, task_id), extension)
with open(file_name, "w") as write_file:
write_file.write(data)
return file_name

def create_files(db, task_id, query):
cursor = db.cursor()
cursor.execute(query, task_id)
for file_name, elements_count, data in cursor:

StackExchange Code Review Q#145079, answer score: 3