Python and MySQL are two powerful tools that work exceptionally well together. In this article, we’ll explore some of the ways we can execute INSERT queries in a MySQL table using Python.
We’ll cover various scenarios, including inserting a single row, using Python variables in the query, inserting multiple rows, and inserting DateTime into a MySQL table.
Inserting a Single Row
Let’s begin with inserting a single row into a MySQL table from Python. We’ll start by importing the necessary modules and connecting to our MySQL database.
import mysql.connector
from mysql.connector import Error
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, salary) VALUES (%s, %s, %s)"
#define values to be inserted
values = (1, 'John Smith', 50000)
#execute the query
cursor.execute(sql, values)
#commit the changes
connection.commit()
print("Data inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
In the code above, we first connect to our MySQL database using the MySQL Connector module. We then create a cursor object to execute our SQL queries.
Next, we define our SQL insert query, which will insert a single row into our employees table. We then define the values to be inserted using a tuple.
We pass these values to the execute() method of the cursor object along with our SQL query. Finally, we commit the changes to our database using the commit() method.
Using Python Variables in INSERT Query
Sometimes we’ll want to insert data into our MySQL table using Python variables. To do this, we’ll use a prepared statement or parameterized query.
This allows us to substitute Python variables in our SQL query.
import mysql.connector
from mysql.connector import Error
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, salary) VALUES (%s, %s, %s)"
#define values using Python variables
id = 2
name = 'Jane Doe'
salary = 60000
values = (id, name, salary)
#execute the query
cursor.execute(sql, values)
#commit the changes
connection.commit()
print("Data inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
In the code above, we use a similar approach to the previous example.
However, we first define our variables outside of the SQL query. We then format our SQL query using %s as placeholders for our variables.
We pass our variables as a tuple to the execute() method of the cursor object, which substitutes these values in our SQL query.
Inserting Multiple Rows
If we need to insert multiple rows into our MySQL table, we can use the executemany() function of the cursor object. This function allows us to insert multiple records in a single query.
We’ll pass a list of tuples containing our records to this function.
import mysql.connector
from mysql.connector import Error
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, salary) VALUES (%s, %s, %s)"
#define values for multiple records
records_to_insert = [(3, 'Jamie Lee', 75000),
(4, 'David Miller', 90000),
(5, 'Sarah Khan', 85000)]
#execute the query
cursor.executemany(sql, records_to_insert)
#commit the changes
connection.commit()
print(cursor.rowcount, "Record(s) inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
In the code above, we define our SQL query as usual.
We then define our records to be inserted as a list of tuples. Each tuple represents a record to be inserted.
We pass this list to the executemany() function of the cursor object along with our SQL query. This function inserts all the records in a single query.
Inserting DateTime into MySQL Table
If we need to insert date and time values into our MySQL table, we’ll need to use the datetime module in Python. We’ll first import the module and create a date object.
We can then format the object as a string using the strftime() method.
import mysql.connector
from mysql.connector import Error
from datetime import datetime
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, hire_date) VALUES (%s, %s, %s)"
#define values to be inserted
id = 6
name = 'James Carter'
hire_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
values = (id, name, hire_date)
#execute the query
cursor.execute(sql, values)
#commit the changes
connection.commit()
print("Data inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
In the code above, we first import the datetime module and create a datetime object using the now() method. We then format the object as a string using the strftime() method.
We then define our SQL query and our variables as usual. We pass our variables to the execute() method of the cursor object, which inserts our date and time value into our MySQL table.
Prerequisites
Before we execute INSERT queries in a MySQL table from Python, we’ll need a few things ready. First, we’ll need to have a MySQL server installed on our system, along with the Python MySQL Connector module.
We’ll also need to have a MySQL database created and a table inside it. We should have the necessary privileges to access the database and table, along with the required username and password to connect to our MySQL server.
Conclusion
In this article, we’ve explored various scenarios of executing INSERT queries in a MySQL table from Python. We’ve covered inserting a single row, using Python variables in the query, inserting multiple rows, and inserting DateTime into a MySQL table.
These are some of the most common scenarios we’ll come across while working with Python and MySQL.
3) Example Code
In this section, we’ll provide some example code to further demonstrate the concepts we’ve covered so far. We’ll include comments to explain each step of the code.
Inserting a Single Row with Python Variables:
import mysql.connector
from mysql.connector import Error
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, salary) VALUES (%s, %s, %s)"
#define values using Python variables
id = 2
name = 'Jane Doe'
salary = 60000
values = (id, name, salary)
#execute the query
cursor.execute(sql, values)
#commit the changes
connection.commit()
print("Data inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
Inserting Multiple Rows with executemany():
import mysql.connector
from mysql.connector import Error
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, salary) VALUES (%s, %s, %s)"
#define values for multiple records
records_to_insert = [(3, 'Jamie Lee', 75000),
(4, 'David Miller', 90000),
(5, 'Sarah Khan', 85000)]
#execute the query
cursor.executemany(sql, records_to_insert)
#commit the changes
connection.commit()
print(cursor.rowcount, "Record(s) inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
Inserting DateTime into MySQL Table:
import mysql.connector
from mysql.connector import Error
from datetime import datetime
try:
#connect to MySQL database
connection = mysql.connector.connect(host='localhost',
database='mydb',
user='root',
password='password')
#create cursor object
cursor = connection.cursor()
#define SQL insert query
sql = "INSERT INTO employees (id, name, hire_date) VALUES (%s, %s, %s)"
#define values to be inserted
id = 6
name = 'James Carter'
hire_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
values = (id, name, hire_date)
#execute the query
cursor.execute(sql, values)
#commit the changes
connection.commit()
print("Data inserted successfully!")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
#close the cursor and connection
cursor.close()
connection.close()
As you can see from the code samples above, Python makes it easy to execute INSERT queries in a MySQL table. By using the MySQL Connector module and the appropriate syntax and methods, we can insert data into our MySQL databases effortlessly.
4) Practicing Python Database Operations
Practicing Python Database Operations, such as executing INSERT queries in a MySQL table, is crucial for mastering these concepts. The more we practice, the better our understanding of these concepts becomes.
We can practice by creating sample databases and tables, using different techniques to insert data, and experimenting with modifying and deleting data from MySQL tables, all while using Python. By practicing regularly, we can become proficient in executing various Database operations in Python, including INSERT queries.
We can then use this knowledge to solve real-world problems that involve storing and retrieving data from databases using Python. In conclusion, executing INSERT queries in a MySQL table using Python is a valuable skill for anyone interested in Database operations.
Through importing appropriate modules and utilizing syntax that allows for the use of Python variables, prepared statements, parameterized queries, and datetime data, Python makes it easy to insert data into our MySQL databases. Practicing these concepts can lead to proficiency in executing various Database operations in Python.
With this knowledge, we can solve real-world problems more efficiently and accurately. The key takeaway from this article is that mastering Python Database operations requires consistent practice, but the effort is well worth the rewards in the end.