As expected Python has great connectivity to SQL and its variants; Python is made for queries and data analysis afterall. So, I decided to tinker with it a bit (I’m not a programmer per se, see my About page; so I share these simpler, more approachable codes for inspiring interest and education for beginners and kids.)
I’ll use open-source SQLite engine version 3. The commands I use in this example are available in virtually all SQL variants (Microsoft SQL Server, MySQL, OpenSQL, etc.) so use what you like. I use Thonny as my Python IDE although there may be better ones, but it’s working so far for my needs.
Objective: In this exercise, I want to connect to a database (we’ll call it ‘test.db’) and check the version of the SQL library installed on my system. If there’s none installed, you’ll need to get that resolved…it’s a prerequisite. Once it’s verified, we create a new database and create a table, add records to it, modify it, and do a basic query of the database…all using the handy SQL library.
The Code:
First I import the necessary libraries (sqlite3 is the library I used for SQL connectivity), then connect to a demo database called ‘test.db’…it does NOT exist on my PC yet. And it has NO records in it. However, I still need to create it to get the SQL connection and verify the version.
lite is the sql object instantiated; con is the handle for the connection established; cur is the object instantiated for executing SQL commands, it’s a SQL cursor object; data is a string of data returned by the query. Block below…
sqlite3.connect() allows the creation and connection very easily (lite is the local object for sqlite3 class). cursor.execute() allows us to send SQL commands as text for the connected database.
The next block shows where I create another database called ‘user.db’ and start playing with it using Python. First, I create a table specifying 2 fields/columns, and insert some records/rows into it. The order of the values I supply need to match with the order of the columns I created in that table.
But before that, you notice a “DROP TABLE…” SQL command. I added that after I realized that user.db persisted and if SQL returns an error if CREATE is called on an existing table with data (it’s the safe thing to do), so to let me continue with the rest of the code without adding complexity, I just delete the table if it’s there, and do the exercise over every time I run it. The delete-only-if-it-exists equivalent is DROP TABLE IF EXISTS <tableName>
We continue…I specify the Table name (“Users” in this case) and all its fields that I’d like: StudentID, StudentName. Also note that data types specified next to each field name: INT and TEXT:
Once the records are “inserted” into the database, we just do the simplest query “SELECT *” and load them into a local variable row using the cursor object using cursor.fetchall(). And we print out to the console.
Next, I modify the table Users, by “altering” the table to add a new field called Dob and of type date. Now that we have this field, I can populate it by INSERT command. Notice that I only added a value for this new field called Dob for ONE record.
After the modification, I load the latest dataset into memory and print out all the rows in the database:
Full source and the example outputs below…
Full Source Code:
Output:
First line is from the version query of the SQL library. Then you see what’s in the database I just created with sample data. The yellow-highlighted portion is what the records are in the user.db after alteration/modification.
Challenge: Go create your own databases 🙂 Create multiple tables and JOIN them together; then query a merged result. Of course, look up the SQL references and Python syntaxes online for help. (I never remember pesky syntax in ALL the languages and their flavors)
