If you need to insert data into a MySQL table using Python, then look no further.
If you need to first learn about the mysql.connector and how to get this up and running, first take a look at the How to Install MySQL Driver in Python
post before continuing.
How do Insert into a MySQL Table in Python
import mysql.connector
mydb = mysql.connector.connect(
host = "localhost",
user = "username",
password = "YoUrPaSsWoRd",
database = "your_database"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Andrew", "Somewhere nice")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record successfully inserted")
This will insert a single row into the MySQL table.
Note the importance of the .commit() once we have executed our SQL statement. This is to persist it to the database.
If you need to insert multiple rows at the same time, then we have a better option for you.
How to Insert Multiple rows into MysQL using Python
import mysql.connector
mydb = mysql.connector.connect(
host = "localhost",
user = "username",
password = "YoUrPaSsWoRd",
database = "your_database"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
('Jack', 'New York'),
('Mary', 'Vancouver'),
('Peter', 'Cairo'),
('Sarah', 'Faro'),
('Stuart', 'London'),
('Hayley', 'Dubai')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was successfully inserted")
Using this technique, we can insert multiple rows in the same query. This reduces the amount of connections to the database and speeds up commit time.
Speaking of commit, note that we always call the .commit() once we are done.
How to get the Last Inserted ID from MySQL in Python
Often you will want to get the last row ID, also known as the row that you just inserted’s ID.
This is typically done by creating an id column and assigning an auto_increment to the column.
This way incremental id numerals will be assigned to each row on row creation by default.
import mysql.connector
mydb = mysql.connector.connect(
host = "localhost",
user = "username",
password = "YoUrPaSsWoRd",
database = "your_database"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Brad", "Los Angeles")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
Once again, we close off by using the .commit() and then call the mycursor.lastrowid which contains the value of the last inserted row’s id.
