Python: Dumping Database Data with Peewee

Question

Background

I am looking for a way to dump the results of MySQL queries made with Python & Peewee to an excel file, including database column headers. I'd like the exported content to be laid out in a near-identical order to the columns in the database. Furthermore, I'd like a way for this to work across multiple similar databases that may have slightly differing fields. To clarify, one database may have a user table containing "User, PasswordHash, DOB, [...]", while another has "User, PasswordHash, Name, DOB, [...]".

The Problem

My primary problem is getting the column headers out in an ordered fashion. All attempts thus far have resulted in unordered results, and all of which are less then elegant.

Second, my methodology thus far has resulted in code which I'd (personally) hate to maintain, which I know is a bad sign.

Work so far

At present, I have used Peewee's pwiz.py script to generate the models for each of the preexisting database tables in the target databases, then went and entered all primary and foreign keys. The relations are setup, and some brief tests showed they're associating properly.

Code: I've managed to get the column headers out using something similar to:

    for i, column in enumerate(User._meta.get_field_names()):
        ws.cell(row=0,column=i).value = column

As mentioned, this is unordered. Also, doing it this way forces me to do something along the lines of

    getattr(some_object, title)

to dynamically populate the fields accordingly.

Thoughts and Possible Solutions

• Manually write out the order that I want stuff in an array, and use that for looping through and populating data. The pros of this is very strict/granular control. The cons are that I'd need to specify this for every database.

• Create (whether manually or via a method) a hash of fields with an associated weighted value for all possibly encountered fields, then write a method for sorting "_meta.get_field_names()" according to weight. The cons of this is that the columns may not be 100% in the right order, such as Name coming before DOB in one DB, while after it in another.

Feel free to tell me I'm doing it all wrong or suggest completely different ways of doing this, I'm all ears. I'm very much new to Python and Peewee (ORMs in general, actually). I could switch back to Perl and do the database querying via DBI with little to no hassle. However, it's libraries for excel would cause me as many problems, and I'd like to take this as a time to expand my knowledge.

Osteoid

Answer 1

There is a method on the model meta you can use:

for field in User._meta.get_sorted_fields():
    print field.name

This will print the field names in the order they are declared on the model.

Calbert

I guess this is as close as I'll get to doing what I want. I'll have to revisit my thought process on this, and look at alternative ways of handling this. Thank you for the help! – Osteoid

I guess coleifer of all people would know, but as a note to others, get_field_names now appears to be an alias for grabbing field names from get_sorted_fields (from definition of ModelOptions), so the order should be correct either way. – Ferd

Note that in the latest versions of peewee, you new need to use User._meta.sorted_fields or User._meta.sorted_field_names – Varices