Pyodbc meta-data methods you should be using – interactions with MySQL.

In my day job, I have recently begun to leverage the Python pyodbc package for MS Access related database tasks. Working with any database, it goes without saying that understanding the schema is paramount. What tables are present? What are their columns and types? How are they related? Among the many methods pyodbc provides, to answer these types of questions, it provides 3 ‘meta-data’ methods you simply cannot live without. Want to know which ones they are? Keep reading…

[Keep reading for more SQL database and Python-centric content >>>]
Advertisements

Pandas and the python os module use case – appending source file information from CSV’s.

In my day job, I use Python to automate several redundant tasks. Between the Pandas library and the CSV module, there is always something available for me to reach for. I typically process several different CSV’s each day with a planned final destination in a SQL database. While contemplating the schema design, I determined it would be best to store the actual source file information from which the data is derived, using the source files’ name and appending it to the end of each row. How did Python help me accomplish this? Continue reading to find out…

[Keep reading for more SQL database and Python-centric content >>>]

Using the REFERENTIAL_CONSTRAINTS table in MySQL – Foreign Key Awareness

Using FOREIGN KEY‘s in database schema design assist in storing consistent, normalized, and sound data. Oftentimes, many tables wind up with many FOREIGN KEY constraints. However, keeping up with this (potential) maze of relationships doesn’t have to be a nightmare. Want to learn more? Keep reading…

[Keep reading for more SQL database and Python-centric content >>>]

Column ordering in MySQL using ALTER TABLE – with examples.

MySQL allows table columns to be placed in a specific location among the other present columns. You likely don’t care one iota about column ordering. But, if you do – and your table is already established – you can still have your cake and eat it too by using simple ALTER TABLE commands in conjunction with certain keywords and place those columns in a position to your liking. Continue reading to see example queries…

[Keep reading for more SQL database and Python-centric content >>>]

SQL CRUD Basics Part 4 – Delete.

In this final part of the SQL CRUD Basics series, we visit the all-mighty and powerful DELETE command. Does that word frighten you? It should, as DELETE will completely remove rows of that oh-so-important data from your database table. Without warning or question, it will be gone. Perhaps your goal is to remove all rows. Great, no problem. However, removing a specific row or set of rows – instead of them all – requires filtering with a WHERE clause predicate, just the same as you would in SELECT and UPDATE statements. Continue reading to see DELETE command examples for better understanding…

[Keep reading for more SQL database and Python-centric content >>>]