Python MySQL INSERT Query: Step by Step Guide in Python

0
182

Python MySQL INSERT Query: Step by Step Guide in Python

In this article, you will learn how to use Python MySQL INSERT query to add data to your database. If you have a background in SQL, this tutorial will improve your knowledge in handling your database in Python.

Prerequisite

First things first – You need to set up your project. If in case you do not know how to do it, click the link to go to our step by step guide. Also, if you have any trouble in using the PIP command, just click the link to go to our easy step by step fix. Make sure you have a database to work with.

How to use INSERT query in Python

  1. Import mysql.connector

    This will connect your project/program to Python’s MySQL module so that your work can communicate to your database.

  2. Use the connect() method

    Using mysql.connector.connect() will establish a connection to your database. You can set the host, user, password, and database name as parameters of this method.

  3. Preparing the SQL

    Next is to prepare your query to insert values into your database. The values inserted to your table should be in the same order as the columns. If you want, you can enumerate the columns on the SQL but the values should be in the same order. Create an appropriately named variable to store your SQL. We will use this later as a parameter.

  4. The cursor() method

    This will create a cursor object that will interact with database.

  5. Execute the SQL with execute()

    Use your SQL variable as parameter for the execute method.

  6. Commit

    Using the commit() method will send the data to the MySQL server. This will permanently change the database. This step is important because Python does not autocommit. So after every execution, you must use the commit() method.

  7. Closing the connection

    Lastly, close the connection of your database.

Sample Code

Let us take a look at the example code. The database used here is “python_db” with a “students” table.

Output

Query Variation

You can also make this as a parameterized query. For this example, we have a “insertStudentRecord” function with “id”,”fn”,”mn” and “ln” as parameters.

You will get the same output as before.

MySQL INSERT Multiple Row Insertion

To insert multiple rows, you will use the executemany() function. With this, you can use an array containing your multiple records to pass to this function. Take a look at the example below.

You will get an output like this:

Conclusion

That’s how you use Python MySQL INSERT query in your projects. You can always expand and try different ways in implementing the INSERT statement in your Python projects. Check out the different Python – related projects below:

Inquiries

If you have any questions or suggestions about the python mysql insert query, please feel free to leave a comment below.

Looking for more source code? Type your keyword here here!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.