Using SQL in Python
Did you know that SQL was developed in the 1970s? (AWS) That means SQL has been around for over 50 years and has maintained its popularity. So, what does SQL stand for? SQL stands for Structured Query Language. In this blog post, we will dive into SQL and explore how it can be used in Python.
What exactly is SQL and what purpose does it serve?
SQL- Explained
As mentioned, SQL stands for Structured Query Language. But this acronym only scratches the surface. Structured Query Language (SQL) is a programming language used for managing and manipulating data within a relational database. In a relational database, data is organized in tabular form. Tables consist of rows and columns, where each row represents a record and each column represents a data attribute, highlighting the various relationships between the data. SQL statements enable you to add, modify, delete, search for, and retrieve data from the database. Additionally, SQL helps in maintaining and optimizing the database’s performance (AWS).
How to use SQL in Python?
First, import or download!
By default, your Python installation contains a Python SQL library named sqlite3, that you can use to interact with an SQLite database (Real Python). To use it, you just have to write “import sqlite3” at the top of the Python files that will be needing it. If you need to download, use “pipenv install sqlite3” in your terminal.
You can also download other SQL libraries using Visual Studio Code (VSCode). For example:
- SQLite Extension: In VSCode, go to Extensions and search for “SQLite.”
- Prettier Extension: Another useful extension is Prettier, which reformats your code to make it cleaner and adheres to SQL formatting rules.
Second, make your table OR establish a connection to the database!
Depending on your needs, you can either create a new table or establish a connection to an existing database.
Creating a table:
Establishing a connection to the database (2 ways):
SQLite: If you are working with an existing database, you can establish a connection to it using the appropriate SQL library. For sqlite3, you simply connect to the database file:
If you are using other databases (like PostgreSQL, MySQL, or SQL Server), the connection process will involve using their respective libraries and connection strings. For instance, in SQLAlchemy, you establish a connection using a database URL:
Third, add the content you would like! SQL is far more useful than just tables!
SQL is much more than just managing tables. It also allows for complex operations and interactions with your data. Here’s an example of a method that manages game records:
In this save method:
- Insert: If
self.idisNone, it inserts a new record into thegamestable. - Update: If
self.idis notNone, it updates the existing record.
This method ensures that you can efficiently manage game records by either adding new entries or updating existing ones based on the presence of an ID.
Get Audrey Chipman’s stories in your inbox
Join Medium for free to get updates from this writer.
With these basics, you’re now equipped to work with SQL databases in Python, whether you’re starting with a new project or interacting with existing databases.
Conclusion
In this blog post, we’ve explored the fundamentals of SQL and its integration with Python, highlighting how these powerful tools can be used together to manage and manipulate data effectively. SQL, with its rich history and robust capabilities, remains a cornerstone in the world of databases, allowing users to perform a wide range of operations from simple queries to complex transactions.
We covered the basics of SQL, including its role in relational databases and how it organizes data into tables, rows, and columns. You also learned how to start using SQL in Python by importing necessary libraries, creating tables, and establishing connections to various types of databases. By leveraging libraries like sqlite3 and SQLAlchemy, Python developers can seamlessly interact with databases, perform CRUD (Create, Read, Update, Delete) operations, and maintain optimal performance.
Furthermore, we examined how to extend SQL’s capabilities beyond just table management, demonstrating how to use Python to efficiently handle tasks such as inserting and updating records with practical examples.
With this knowledge, you are now equipped to integrate SQL into your Python projects, whether you’re building new applications or managing existing databases. SQL’s versatility and Python’s ease of use make a powerful combination for any data-driven development, enabling you to harness the full potential of your data.
Happy coding, and may your data management tasks be both efficient and insightful!
Sources
“Introduction to Python SQL Libraries.” Real Python, Real Python, 25 Mar. 2024, realpython.com/python-sql-libraries/#:~:text=By%20default%2C%20your%20Python%20installation,write%20data%20to%20a%20file.
“What Is SQL? — Structured Query Language (SQL) Explained — AWS.” AWS, aws.amazon.com/what-is/sql/. Accessed 22 Aug. 2024.





