Summary: in this tutorial, you will learn how to insert data into a table using MySQL Connector/Python API.
This tutorial picks up where the Querying Data from a Table in Python tutorial left off.
To insert new rows into a MySQL table, you follow these steps:
- First, connect to the MySQL database server by creating a new
MySQLConnection
object. - Second, create a
MySQLCursor
object from theMySQLConnection
object. - Third, execute the
INSERT
statement to insert data into the table. - Finally, close the database connection.
MySQL Connector/Python provides an API that allows you to insert one or multiple rows into a table at a time. Let’s examine each method in more detail.
Insert one row into a table
The following program defines a function called insert_book()
that inserts a new book into the books
table:
from mysql.connector import MySQLConnection, Error
from config import read_config
def insert_book(title, isbn):
query = "INSERT INTO books(title,isbn) " \
"VALUES(%s,%s)"
args = (title, isbn)
book_id = None
try:
config = read_config()
with MySQLConnection(**config) as conn:
with conn.cursor() as cursor:
cursor.execute(query, args)
book_id = cursor.lastrowid
conn.commit()
return book_id
except Error as error:
print(error)
if __name__ == '__main__':
insert_book('A Sudden Light', '9781439187036')
Code language: Python (python)
How it works.
First, import the necessary modules including the MySQLConnection
and Error
classes from the mysql.connector
module, and read_config
is a function imported from a custom module called config
:
from mysql.connector import MySQLConnection, Error
from config import read_config
Code language: JavaScript (javascript)
Second, define a function insert_book()
that accepts two arguments: title and ISBN and prepare an SQL query to insert a new row into the books table:
def insert_book(title, isbn):
query = "INSERT INTO books(title,isbn) " \
"VALUES(%s,%s)"
args = (title, isbn)
Code language: JavaScript (javascript)
Third, use the with
statement to open a database connection (MySQLConnection
) and create a cursor (cursor
). We use the **config
syntax to pass keyword arguments from the config
dictionary:
try:
config = read_config()
with MySQLConnection(**config) as conn:
with conn.cursor() as cursor:
Code language: JavaScript (javascript)
Fourth, execute the query using the execute() method of the cursor with the provided arguments (args) and return the inserted id of the row:
cursor.execute(query, args)
book_id = cursor.lastrowid
Sixth, commit the changes to the database using the commit()
method:
conn.commit()
Code language: CSS (css)
Seventh, show the error message if it occurs during the execution:
except Error as error:
print(error)
Code language: JavaScript (javascript)
Note that the with
statement automatically takes care of closing the cursor and the database connection, even if an exception occurs.
Finally, call the insert_book()
function to insert a new book into the books
table:
if __name__ == '__main__':
insert_book('A Sudden Light', '9781439187036')
Code language: JavaScript (javascript)
Insert multiple rows into a table
The following INSERT
statement allows you to insert multiple rows into the books
table:
INSERT INTO books(title,isbn)
VALUES('Harry Potter And The Order Of The Phoenix', '9780439358071'),
('Gone with the Wind', '9780446675536'),
('Pride and Prejudice (Modern Library Classics)', '9780679783268');
Code language: SQL (Structured Query Language) (sql)
To insert multiple rows into a table from Python, you use the executemany()
method of the MySQLCursor
object.
from mysql.connector import MySQLConnection, Error
from config import read_config
def insert_books(books):
query = "INSERT INTO books(title,isbn) " \
"VALUES(%s,%s)"
try:
config = read_config()
with MySQLConnection(**config) as conn:
with conn.cursor() as cursor:
cursor.executemany(query, books)
conn.commit()
except Error as error:
print(error)
if __name__ == '__main__':
books = [('Harry Potter And The Order Of The Phoenix', '9780439358071'),
('Gone with the Wind', '9780446675536'),
('Pride and Prejudice (Modern Library Classics)', '9780679783268')]
insert_books(books)
Code language: Python (python)
The logic in this example is similar to the logic in the first example. However, instead of calling the execute()
method, we call executemany()
method.
To insert multiple rows into the books table, we pass a list of tuples, each containing the title and ISBN of the book to the insert_books()
function.
By calling the executemany()
method of the MySQLCursor
object, the MySQL Connector/Python translates the INSERT
statement into the one that contains multiple lists of values.
Summary
- Use the
execute()
method to execute anINSERT
statement that inserts one row into a table. - Use the
executeMany()
method to execute anINSERT
statement that inserts multiple rows into a table.