Join our community of software engineering leaders and aspirational developers. Always
stay in-the-know by getting the most important news and exclusive content delivered
fresh to your inbox to learn more about at-scale software development.
It seems that you've previously unsubscribed from our newsletter
in the past. Click the button below to open the re-subscribe form
in a new tab. When you're done, simply close that tab and continue
with this form to complete your subscription.
The New Stack does not sell your information or share it with
unaffiliated third parties. By continuing, you agree to our
Terms of Use
and
Privacy Policy
.
We’re so glad you’re here. You can expect all the best TNS content to arrive
Monday through Friday to keep you on top of the news and at the top of your game.
What’s next?
Check your inbox for a confirmation email where you can adjust your preferences
and even join additional groups.
Follow TNS on your favorite social media networks.
I want to show you something pretty cool that Python can do: Insert data into a MySQL database table.
Imagine being able to inject data into a table without having to log into the MySQL console first — and injecting that data from a Python application can be incredibly flexible and handy. Even better, it’s not all that much of a challenge.
Let me show you how it’s done.
What You Need
I’m going to demonstrate this on an instance of Ubuntu Server 22.04.3. If you’re using a different Linux distribution (or if you’re using either MacOS or Windows), you’ll have to alter the installation commands. With that said, you’ll need an OS that supports Python3 and a user with
sudo
privileges.
And, just for fun, I’m going to show you how to install MySQL and create a user and database.
Groovy. Let’s make some Python/database magic.
Installing MySQL and Python
The first thing we’re going to do is install the MySQL database server. To do that, log into your instance of Ubuntu Server and issue the command:
sudo apt-get install mysql-server -y
We now have a database, a user, and a table ready for action.
The Python Application
This is where the fun starts. We’re going to create a Python application that injects data into the name and email columns of the editorial columns.
Create the script with the command:
nano insert.py
sql = "INSERT INTO editorial (name, email) VALUES (%s, %s)"
val = ("NAME", "EMAIL")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import
mysql
.
connector
mydb
=
mysql
.
connector
.
connect
(
host
=
"localhost"
,
user
=
"jack"
,
password
=
"PASSWORD"
,
database
=
"staff"
)
mycursor
=
mydb
.
cursor
()
sql
=
"INSERT INTO editorial (name, email) VALUES (%s, %s)"
PASSWORD is the password you created for the MySQL user jack, NAME is the name you want to insert into the name column and EMAIL is the address you want to insert into the email column.
Here’s a bit more explanation:
The first line imports the required function that allows Python to connect to MySQL.
The mydb section configures the information for the database.
mydb.cursor() is the function that allows the insertion of data into the database.
The sql line is our first MySQL query.
The val line defines our columns for the database.
The mycursor.execute executes the above operations.
The mydb.commit() confirms the changes made by mycursor.execute.
The print line prints output to indicate success or failure.
Save and close the file with the Ctrl+X key combination.
Running the App
We can now run our new Python app that will inject the data into the table that you specified in the script. The run command for this would be:
python3 insert.py
You should see your first data added to the table.
Accept Input from a User
That’s not a very efficient method of injecting data into a database because you’d have to edit the script every time. Fortunately, we can use variables in Python to accept input that will then be inserted.
To do this, we have to make use of the input() function, which allows the script to accept input from a user. Our new script would look like this:
import mysql.connector
name = input("Type a name: ")
email = input("Type an email: ")
mydb = mysql.connector.connect(
host="localhost",
user="jack",
password="PASSWORD",
database="staff"
sql = "INSERT INTO editorial (name, email) VALUES (%s, %s)"
val = (name, email)
mycursor.execute(sql, val)
mydb.commit()
PASSWORD is the password you set for the MySQL user.
Save and close the file. Run the app in the same way, only this time you’ll be prompted to input a name and then an email.
And that’s how you can use Python to inject data into a MySQL table. Play around with this and see how creative you can get.
TRENDING STORIES
Jack Wallen is what happens when a Gen Xer mind-melds with present-day snark. Jack is a seeker of truth and a writer of words with a quantum mechanical pencil and a disjointed beat of sound and soul. Although he resides...
Read more from Jack Wallen
