Python MySQL INSERT - CodersLegacy

Description

The Python MySQL INSERT command is used to insert a new row in a table. Keep in mind that for this command to work you need to have a pre-existing table. See below for the syntax of this MySQL command.

INSERT INTO table_name (column1, column2, column3)
                VALUES (value1, value2, value3)

The above code can be written in one line. But it’s more readable this way. Value1 is inserted into column1, value2 into column2 and value3 into column3.

The code above is the pure MySQL form of the insert statement. In Python however, a slight modification is required to get it running due to syntax. See the example below.

Example:

The following example inserts a new row into the table student. From the columns we can see that the student table contains at least three pieces of information (It is not compulsory to fill out all columns). Name, Age and Gender.

The %s characters you see down there are simple placeholders. They have no meaning of there own. They are ultimately replaced by their corresponding values passed into the execute() function.

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user =  "user_name",
  password = "password",
  database = "testdb"  )

cursor = db.cursor()

sql = "INSERT INTO student (Name, Age, Gender) VALUES (%s, %s, %s)"
values = ("Bob","16","Male")
cursor.execute(sql, values)

db.commit()

Remember: Your code will not run if the table does not exist. Even if there is a column mismatch, your code will still produce an error. Everything must be aligned properly for the Python MySQL INSERT command to work.

Furthermore, you have to add a commit() function every time you make changes to a table. The commit() function is sort of like the send button on a email. Until you press the send button you’re email will remain as a draft. Same concept applies here.

Inserting Multiple rows

To insert multiple rows at once, all you have to do is add extra values as shown below. As long as each value follows the proper format, there is no limit to the number of values you can add.

List of changes required:

Change the function execute() to executemany().
The values variable needs to be changed from a single tuple, to a list containing several tuples. Remember those square brackets.

cursor = db.cursor()

sql = "INSERT INTO student (Name, Age, Gender) VALUES (%s, %s, %s)"
values = [
         ("Bob", "16", "Male"),
         ("Alice", "15", "Female"),
         ("David", "17", "Male"),
         ("George", "12", "Male")
         [

cursor.executemany(sql, values)
db.commit()

This marks the end of our Python MySQL INSERT Article. Let us know if you have any suggestions or corrections to make.

Use this link to head back to main Python MySQL page: link