Python and SQL for beginners

Python Introduction

Python is a high-level, interpreted programming language that was first released in 1991. It is known for its simplicity, readability, and flexibility, and is widely used in a variety of applications, including web development, scientific computing, data analysis, and artificial intelligence. Python's syntax is designed to be easy to read and write, with a focus on code readability and minimalism. Its extensive standard library and large community of third-party packages make it a popular choice for developers of all skill levels. Python's popularity has also been driven by the rise of data science and machine learning, where it has become one of the primary languages used for these fields. Overall, Python is a versatile language that is well-suited to a wide range of applications and is a great choice for both beginner and experienced programmers. Note: I will be talking about Python3 for this blog. Some syntax may be different between Python2 and 3, and some commands may be different or not even exist!

SQL Introduction

SQL (Structured Query Language) is a programming language designed for managing and manipulating data stored in relational databases. SQL is widely used in many industries, including finance, healthcare, and e-commerce, to store, retrieve, and manage large amounts of data. SQL provides a standard set of commands for creating, modifying, and deleting database structures and data, as well as querying and manipulating data within those structures. SQL syntax is designed to be simple and easy to learn, with a focus on readability and expressiveness. The popularity of SQL has led to the development of many different database management systems, each with its own set of extensions and variations on the SQL language. Despite these variations, the core principles of SQL remain the same, making it a powerful and widely-used tool for managing data.

Why not combine two great things?

Python is knows for simplicity, readability, and amazing flexibility. The language is popular in fields from web development, game dev, AI, to data science and robotics. Python is an easy to grasp language for beginners and a near endless toolbox for experienced programmers. I'm sure, dear reader, that you can imagine the need and usefulness of storing your program's data for future use, even after exiting the program. Thankfully we can do this with databases. While we do have a few options for saving our data such as writing to a txt file or creating a json file, using a database file will prepare us for tackling bigger, potentially professional jobs later on.

Starting with sqlite3

Here I will walk through the installation of sqlite3, a lightweight and embedded relational database management system (RDBMS) that is implemented in the C programming language, and therefore able to be used in python!

A quick note from personal experience: sqlite3 has been included in Python since 2.5.x, but if you've not configured your Python install correctly you might find you can't use it. There are many tutorials on the web about how to uninstall, configure, and reinstall Python to get your packages working.

The first thing we want to do to get started using SQL and sqlite3 is to import the module:

import sqlite3

if this is your first time using this module you may want to run your file immediately to see if the program executes successfully or if you get an error stating sqlite3 can't be found.

at this point we want to create the heart of sqlite3, the connect and cursor.
The connect establishes a link between your .py file and the database. You can specify an existing db file, let connect create one for you, or use ':memory:' to create a temporary database stored in RAM.
cursor is the method you will use throughout your program to create, read, update, or delete tables and their data.

CONN = sqlite3.connect('my_db.db')
CURSOR = CONN.cursor()

From here we can use the cursor to create a table.

CURSOR.execute('''CREATE TABLE example (id INTEGER PRIMARY KEY, data TEXT)''')

To add data to our new table:

CURSOR.execute("INSERT INTO ex (data) VALUES ('Hello World')")

and to query the table's data:

res = CURSOR.execute('SELECT * FROM ex')
print(res.fetchall())
# -> [(1, 'Hello World')]

Sqlite3 uses SQL syntax and commands, so we can do anything we can do in a terminal in python. Create multiple tables, join them, migrate data, etc.

Making our lives easier

As you can see we need a bit of patience to write all those sql commands. while not the worst, it certainly isn't the best at avoiding needless repetition when trying to do nearly any operation.

We can make our lives easier by implementing a program called SQLAlchemy. This program allows us to use SQL by only executing python, and can drastically cut down on repetition.

Starting with SQLAlchemy

Unlike sqlite3, SQLAlchemy is not included in python by default. We will need to use pip to install it.

$ pip install sqlalchemy

now we can import the module into our Python code:

import sqlalchemy

The heart of sqlalchemy is the engine. This is a stop gap between your code and the database that interprets python and generates SQL queries for you. The session is your gateway between your program and the database. To get started we will type:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = sql.create_engine('sqlite:///your_db_here')
Session = sessionmaker(bind=engine)
session = Session()

From here we can do any SQL operation we wish. Some examples are:

Create a table:

base.metadata.create_all(engine)
class Example(Base):
    __tablename__='example_table'
    id = Column(Integer(), primary_key=True)
    data= Column(String())

^^ So just for presentation, I'm pretty sure that when you instantiate an instance of a class sqlalchemy is interpreting that class, grabbing the table name dunder method, your column declarations, and adding a table to the database. Could def be wrong here tho...

Insert into a table:

ex = Example(data="Hello World")
session.add(ex)
session.commit()

Insert multiple using bulk_save_objects(list)

ex1 = Example(data = "Hello")
ex2 = Example(data = "World")
session.bulk_save_objects([ex1, ex2])

We have many more options such as

session.query(Example)
session.query(Example).filter(condition, condition2, condition3, ...)
session.query(Example).filter(condition).first()
session.query(Example).filter(condition).many()
session.query(Example).filter(condition).delete()
session.query(Example).order_by(column)
session.query(Example).order_by(desc(column))
session.query(Example).filter(condition).update({column: new_value})

Overall, SQLAlchemy is a powerful and flexible library that makes it easy for developers to work with relational databases using Python. Its ORM layer and powerful query API make it easy to manipulate data, and its support for transactions and connection pooling make it a good choice for high-concurrency applications. If you're building a Python application that needs to work with a relational database, SQLAlchemy is definitely worth considering.

Thanks for reading, and as always, I can't fit an entire library's doc into a blog, so I haven't explained or used even a small fraction of the above libraries functionality. I strongly encourage anyone interested to go ahead and read up on the docs, or just play around until you get the hang of the programs.