Where are you using WebAssembly?
Wasm promises to let developers build once and run anywhere. Are you using it yet?
At work, for production apps
At work, but not for production apps
I don’t use WebAssembly but expect to when the technology matures
I have no plans to use WebAssembly
No plans and I get mad whenever I see the buzzword

Insert Data into a MySQL Database via a Python Script

What do you do with the data you have generated in your new Python application? Why not put it into a MySQL database? Here's how.
Nov 26th, 2023 5:21am by
Featued image for: Insert Data into a MySQL Database via a Python Script

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:

When that finishes, you’ll want to enable the service, so it runs at boot. To do that, issue the command:

Before you continue, you must also install the Python3 MySQL Connector, which allows Python to interact with your MySQL databases. This can be accomplished with the command:

If your Ubuntu instance doesn’t already have Python installed, you can do so with the command:

That’s it for the installation.

Creating a Database and User in MySQL

Next, we need to create a database. Log into the MySQL console with the command:

Once there, create a database named “staff” with the command:

Now, we can create a user (we’ll name it “jack”) and give that user permission to use the new database. Create the user with:

Make sure PASSWORD is a strong, unique password.

Give that user access to our staff database with the command:

Create a Table

We now have to create a table. First, change to the staff database with:

Now, we can create a table on our new database. Let’s keep it simple and create a table called “editorial” with two columns (name and email). The command for that would be:

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:

In that file, paste the following content:

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:

You should receive confirmation that the injection was successful. You can verify this with the command:

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:

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.

Group Created with Sketch.
THE NEW STACK UPDATE A newsletter digest of the week’s most important stories & analyses.