DEV Community

Cover image for Connecting to a relational database using SQLAlchemy and Python
Chris Greening
Chris Greening

Posted on • Updated on

Connecting to a relational database using SQLAlchemy and Python

Welcome!

Perhaps you've just created your first SQLite database.

Or maybe you read my previous blog post on deploying a free tier relational database with Amazon RDS ๐Ÿ˜‰

For whatever reason you've arrived here - welcome!

And now that you've got that fancy database I'm sure you just can't wait to access it from the warm embrace of Python

So let's jump into some code and learn how we can leverage SQLAlchemy's capabilities as "The Database Toolkit for Python" to connect to our database!

Table of Contents

โ— IMPORTANT โ—: This tutorial is strictly for practical learning purposes and NOT an exhaustive guide for setting up a secure production-ready environment.

Be sure to keep an eye out for additional โ— IMPORTANT โ— notes throughout this tutorial for potential security concerns, gotchas, etc.

Chris Greening - Software Developer

Hey! My name's Chris Greening and I'm a software developer from the New York metro area with a diverse range of engineering experience - beam me a message and let's build something great!

favicon christophergreening.com

Understanding the SQLAlchemy Engine

As we all know the engine is the heart of (most) motor vehicles.

It's a complex machine that:

  1. takes gasoline as an input
  2. burns the gasoline
  3. and converts the resulting heat into mechanical work as an output

And just like with motor vehicles, the Engine is the heart of SQLAlchemy.

It's the lowest level object used by SQLAlchemy and it helps drive the conversation between our Python application and database(s).

Image showing the different layers between the database and our connection

Without going into too much detail, the Engine
internally references a:

  • Dialect object that handles communication and a
  • Pool object that handles connection.

These in turn work with the DBAPI behind the scenes to translate information to and from our app and database.

The Engine is a complex piece of software that:

  1. takes input from our Python app
  2. processes the information
  3. and converts it into output that our SQL database can understand

Don't sweat the details when you're first learning! ๐Ÿ˜…

The important part here is just understanding that we have to create that engine for our app to use

Deconstructing the database URL

Animation showing the different parts of a SQLAlchemy connection string

Now back to the analogy of motor vehicles... when we turn on our engine it's often because we have a destination in mind that we want to drive to

To get there we have to know things like:

  1. How are we getting there?
  2. Where are we going?
  3. What additional information do we need?

And in the context of SQLAlchemy, this is where our database URL comes in

A typical database URL might look something (but not exactly) like this:
dialect+driver://username:password@host:port/database

Feeding this to our instance of Engine, we're able to inform SQLAlchemy crucial information such as:

  • dialect+driver: Is our database MySQL, PostgreSQL, etc? what DBAPI should our Engine connect to?
  • username:password: What credentials do we need to connect to our database?
  • host:port: Where is our database?
  • database: What is the name of our database?

So now let's take a look at a couple examples of what an actual database URL could look like:



# Connect to a local SQLite database
DATABASE_URL = "sqlite:///spam.db'


Enter fullscreen mode Exit fullscreen mode


# Connect to a remote MySQL instance on Amazon RDS
DATABASE_URL = "mysql+pymysql://chris:pa$$w0rd@insert-your-database-name.abcdefgh.us-east-1.rds.amazonaws.com:3306/mydatabase"


Enter fullscreen mode Exit fullscreen mode


# Connect to a remote PostgreSQL database and pass additional parameters
DATABASE_URL = "postgresql+pg8000://user:pa$$w0rd@12.34.56.789/mydatabase?charset=utf8mb4


Enter fullscreen mode Exit fullscreen mode

For additional information and usecases regarding the database URL, feel free to check out some of the official SQLAlchemy documentation on the subject!

โ— IMPORTANT โ—: If you're connecting to a remote database, publicly exposing your database to the internet and/or establishing remote unencrypted connections opens up attack vectors that make your database and application vulnerable

Reminder that this tutorial is NOT an exhaustive guide for setting up a secure production environment.


Creating the Engine

So now let's go ahead and actually create the engine!



from sqlalchemy import create_engine
engine = create_engine(DATABASE_URL)


Enter fullscreen mode Exit fullscreen mode

"Wait - that's it?"

Yeah! That's it!

It's worth mentioning that Engine (and more specifically the internal Pool) have a lazy initialization. They don't establish that first DBAPI connection until we explicitly connect or call an operation that uses connect internally such as execute

For additional information and usecases regarding create_engine, feel free to check out some of the official SQLAlchemy documentation on the subject!


Executing a SQL query from Python

And now that we've instantiated our Engine, we're ready to use it to query our database and parse its response into usable data in Python!



from sqlalchemy import text

# Loop through every row in table chris_greenings_blog
with engine.connect() as connection:
    query = text("SELECT * FROM chris_greenings_blog")
    blog_posts = connection.execute(query)
    for post in blog_posts:
        print(post["title"])


Enter fullscreen mode Exit fullscreen mode

In the above code snippet our Engine (and its internal references) have:

  • established a connection to our database
  • translated our Python textual str query into something the DBAPI can understand
  • executed the query
  • returned and translated the DBAPI's response into something Python can understand
  • looped through each row and printed the title of every blog post

Pretty nifty, eh?


Try it yourself!

As an addition to this blog post, I've published a small project on GitHub that uses SQLAlchemy to:

  • create a local SQLite database
  • create a table
  • seed the database with data from a CSV
  • perform simple queries

Sample code showing SQLAlchemy usage

Click here to check it out and be sure to tinker around with the code to get the most out of this tutorial!


Conclusion

In this tutorial we covered one of the most critical aspects of using SQLAlchemy: connecting to our database

And this only scratched the surface of what we can accomplish with SQLAlchemy - I highly recommend digging deeper and learning more about what SQLAlchemy has to offer (such as its capabilities as an Object Relational Mapper)!

Thanks so much for reading and if you liked my content, be sure to check out some of my other work or connect with me on social media or my personal website ๐Ÿ˜„

Chris Greening - Software Developer

Hey! My name's Chris Greening and I'm a software developer from the New York metro area with a diverse range of engineering experience - beam me a message and let's build something great!

favicon christophergreening.com

Cheers!

Back to the top โคด๏ธ


GitHub logo chris-greening / chris-greening-blog

Repo for storing content related to Chris Greening's blog posts and tutorials

Chris Greening's blog

Welcome to my blog's repository!

Screenshot of Chris Greening's profile on DEV.to

The purpose of this repo is to organize data, source files, and additional information relating to my blog at DEV.to.

Check out my website or email me if you want to get in touch!


Posts





Additional resources

Top comments (2)

Collapse
 
pythonscrapers profile image
pythonscrapers

Awesome tutorial, excellent explanation of the engine. Thank you

Collapse
 
chrisgreening profile image
Chris Greening

No problem! So glad I could be of help :D