Database

Pyodbc SQL CRUD – Read: Examples with MySQL

Reading the data in a database table is a fundamental SQL operation. I suppose you could just store the data and be done with it. But, where is the fun in that? To see stored data, you use the SELECT command. In the CRUD acronym, that is the Read aspect. I have written extensively on SQL CRUD operations in the past (see list of posts at the end of this piece). But, how do you read rows of data with pyodbc as the middleware (termed loosely here) between you and the database? Keep reading to see several simple examples…

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

Pyodbc SQL CRUD – Create: Examples with MySQL

In my day job as a Pipeline Survey Data Analyst, I lean heavily on the Python pyodbc package for interactions with an Access Database. To gain more knowledge and better proficiency with pyodbc, I thought to use it with one of my favorite open-source databases, MySQL. Having recently written a 4-part series on SQL CRUD operations using MySQL, this post is a continuation of sorts. However, Create operations are executed with the pyodbc driver instead of native SQL…

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

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 >>>]

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 >>>]

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 >>>]