Python and XLSX to SQL Fun (For me)

I have been mulling this over for weeks now and at the weekend I started to get the plan in place. With the usual whiteboard of processes and aims.

I have a spreadsheet that I think really should be a database. It just makes more sense in my head, and it gives me a bit of fun when everyone else watches the tv. Some kind of soccer event somewhere.

The Easy Bit

Create a database, create a table with all of the correct columns and attributes.

Convert the table on the xlsx spreadsheet into a pandas dataframe, iterate over the dataframe to construct an INSERT sql query for each of the? x 1000s of rows. Saving to a text file to do manual random checks that the queries were working as expected.

The CRUD Bit

Okay so the easy bit sorted out create, this would be reusable code for the future additions. Of course the sql query constructor for delete isn’t much more than a subset of the create constructor code, especially as it would only need the primary key of each row that is required to be deleted.

Now the Update constructor,, that is a bit more drawn out. I have set up one of the possible constructors that would generate an update query.? Fourteen of the twenty three columns that have multiple options. This is going to take time, and I need to get every constructor section working before filling it out.

This is similar for Read, there are going to be so many ways to make this, a simple SELECT * for every row will suffice.

The SQL Interface Bit

Thankfully there is a nice library in pyodbc that I found, that keeps things simple. Set up a connector, then a cursor and execute. It all worked great for a single sql query from the choices above.

How to iterate over multiple commands? Sql queries have single quotes, double quotes and commas when set as STR variable, making for an interesting evening of thought when all of? the examples I found were for executing single commands and for some reason the queries were encapsulated in three double quotes.?

After a bit of trial and error I found the solution. Forget about the three double quotes, but the commas put me off list at first and I started to think tuple, but it came to me that parentheses around a query string was all that was needed. Then I could create a list of queries in this manner and it would work.

The Menu Bit

All of the components work individually and the database is populated with records as the code successfully iterates over a list of insert queries. It is time to look over the mechanics of the menu and how a call from each of the menu options generates output to the terminal or a file for a Select query. It’s a job for pandas and it works a treat. A lot simpler than expected and output is as expected.

The Final Bit is the Start Bit

Now I have all of the components working. It is now the time to start fresh. That doesn’t mean delete everything and keep my fingers crossed that I can rebuild this. No.?

I know all of the components for this system work. Now it is time to start from the top down and build the system structure as a whole that is easy to read and understand.