Python and SQL Setup Made Easy: Guide to Adding Data
Learn how to set up Python and SQL for seamless data management. This beginner-friendly guide covers SQLite, creating databases, and adding data step-by-step.
Starting with SQL databases in Python may seem like a big leap, but it’s actually a simple yet powerful step for anyone looking to handle data more efficiently.
Imagine having all your data neatly organized, ready for analysis, and accessible with just a few lines of code—that’s the magic of combining Python and SQL.
In this series, I’ll break down everything from setting up your environment to running commands like a pro. But with that said this is just my weekly free article with a slither of what we are actually covering in the full SQL in Python Series.
Each week, I dive deep into Python and beyond, breaking it down into bite-sized pieces. While everyone else gets just a taste, my premium readers get the whole feast! Don't miss out on the full experience – join us today!
If you haven’t subscribed to my premium content yet, I highly encourage you to do so. You gain full access to all of these articles and all the code that comes with them, so you can follow along!
Plus, you’ll get access to so much more, like monthly Python projects, in-depth weekly articles, the '3 Randoms' series, and my complete archive!
This is the second article in our SQL in Python series. Check out the new roadmap for SQL in Python and join premium to access mini challenges, learning resources, and more to help you really build a strong foundation!
Also I have just released this months edition of Project X. This project is based on SQL so you can begin using the skills and tools you are learning in this series while creating a real world project, this is exclusive for my premium readers, come join in!
I spend a lot of my week on these articles, so if you find it valuable, consider joining premium. It really helps me keep going and lets me know you’re getting something out of my work!
If you’re already a premium reader, thank you from the bottom of my heart! You can leave feedback and recommend topics and projects at the bottom of all my articles.
👉 If you get value from this article, please help me out and leave it a ❤️. This helps more people discover this newsletter on Substack! Thank you so much!
🎯 This Months Project X is here!
Now building in my ongoing series for SQL in Python this is a piece of the long form article where I break it all down and include even more action for my awesome premium readers!
In the full article I cover Inserting, Deleting and Updating data in your SQL database. This is all done with SQL using INSERT INTO, DELETE and UPDATE. As a bonus I also include transactions and how you keep your data safe with Rollbacks.
Check out this weeks long form article to take your understanding and your skills to the next level, I’ll see you there!
Now onto this week’s free article which is a bite out of the article above!
Setting Up the Environment: Python and SQL
To get started, you'll need an SQL database, like SQLite or MySQL, and the right libraries installed in Python. For the first few articles in this series, we’ll use SQLite because it's easy to set up and comes built-in with Python.
If you have missed the last few articles, please head back to them for reference as we already got started with SQL and installing it on your system. We also covered some SQL syntax and queries at this stage too. I’ll link the series article here, inside this article you can shuffle between the already released ones.
Here, we're using staff.db as our SQLite database file. If this file doesn’t exist yet, SQLite will create it automatically, making SQLite great for quick testing and working locally.
It’s common you see an object called cursor, you can think of this a the middle man, the one who has the ability to communicate with the database, so anytime we want to execute and queries we will use our cursor object.
Inserting Data into SQL Databases
Adding data to a table is a core part of managing data, as it lets you grow your dataset by introducing new entries. Now remember our database is like a spreadsheet and a spreadsheet can have multiple tables, each table with its own data.
So the great thing is that SQL’s INSERT INTO command is built for this, built to easily add data for us and in Python, you can use it in a way that’s both secure and flexible with parameterized queries.
👉 If you get value from this article, please help me out and leave it a ❤️. This helps more people discover this newsletter on Substack! Thank you so much!
Basic Syntax of INSERT INTO
I always like to kick things off with the SQL syntax because even when you start working with SQL outside of the Python world, this is handy. You will already know how to write your own queries when you get using MySQL, PostgreSQL, etc.
INSERT INTO Employees (name, department, salary)
VALUES ('Chris P. Bacon', 'Engineering', 85000);Here, you’re telling SQL to add a new row in the Employees table within your database, listing the columns (name, department, salary) along with the values that go into each column.
You must do this in order or they will be mismatched, so name is equal to ‘Chris P. Bacon’ etc. It’s important to note this because it will also be helpful and take effect next in Python.
Adding a New Entry with Python
In Python, we can add data to our database with parameterized queries, which let you insert values safely. Using parameterization helps keep your database secure by keeping SQL code and data separate, which helps prevent SQL injection attacks.
Now you can check out my code below, I made a variable sql which is holding a string, this is really my SQL commands written in Python. You do not need to make a variable like this but I find it helpful to break things up so it’s easier to digest and see what’s happening.
In my code, the question marks (?) act as placeholders for the values we want to add here. This is standard practice and currently these ‘?’ do not have any values but the soon will.
When I take my cursor to execute the query and insert data, we should use cursor.execute. The first thing we add is the SQL command we want to execute then we add the values to those question marks, for instance ('Chris P. Bacon', 'Engineering', 85000).
Always remember that once you are done with some or really any SQL command and make some changes to your database, don’t forget to commit it. This is basically going to save your data and refresh your database simultaneously.
Conclusion
I’ve touched on the basics of setting up and working with SQL databases in Python. We started with SQLite to make things easy, learned how to add data using the INSERT INTO command, and used Python’s cursor to interact with our database smoothly. Plus, as a bonus I introduced transactions to keep your data safe by ensuring all changes go through only if everything runs as expected.
With these tools, you're ready to start building databases that work seamlessly with your Python projects. In the next articles, we’ll go deeper, exploring more SQL commands and ways to work with your data. Thanks for following along and if you’re getting value from my work drop a comment and a like, that really helps.
👉 If you get value from this article, please help me out and leave it a ❤️. This helps more people discover this newsletter on Substack! Thank you so much!
Have an amazing week Nerd Nook crew! ~ Josh