Python mysql connector insert

Inserting or updating data is also done using the handler structure known as a cursor. When you use a transactional storage engine such as InnoDB (the default in MySQL 5.5 and higher), you must commit the data after a sequence of INSERT , DELETE , and UPDATE statements.

This example shows how to insert new data. The second INSERT depends on the value of the newly created primary key of the first. The example also demonstrates how to use extended formats. The task is to add a new employee starting to work tomorrow with a salary set to 50000.

The following example uses tables created in the example Section 5.2, “Creating Tables Using Connector/Python”. The AUTO_INCREMENT column option for the primary key of the employees table is important to ensure reliable, easily searchable data.

from __future__ import print_function from datetime import date, datetime, timedelta import mysql.connector cnx = mysql.connector.connect(user='scott', database='employees') cursor = cnx.cursor() tomorrow = datetime.now().date() + timedelta(days=1) add_employee = ("INSERT INTO employees " "(first_name, last_name, hire_date, gender, birth_date) " "VALUES (%s, %s, %s, %s, %s)") add_salary = ("INSERT INTO salaries " "(emp_no, salary, from_date, to_date) " "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)") data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14)) # Insert new employee cursor.execute(add_employee, data_employee) emp_no = cursor.lastrowid # Insert salary information data_salary = < 'emp_no': emp_no, 'salary': 50000, 'from_date': tomorrow, 'to_date': date(9999, 1, 1), >cursor.execute(add_salary, data_salary) # Make sure data is committed to the database cnx.commit() cursor.close() cnx.close()

We first open a connection to the MySQL server and store the connection object in the variable cnx . We then create a new cursor, by default a MySQLCursor object, using the connection’s cursor() method.

We could calculate tomorrow by calling a database function, but for clarity we do it in Python using the datetime module.

Both INSERT statements are stored in the variables called add_employee and add_salary . Note that the second INSERT statement uses extended Python format codes.

The information of the new employee is stored in the tuple data_employee . The query to insert the new employee is executed and we retrieve the newly inserted value for the emp_no column (an AUTO_INCREMENT column) using the lastrowid property of the cursor object.

Next, we insert the new salary for the new employee, using the emp_no variable in the dictionary holding the data. This dictionary is passed to the execute() method of the cursor object if an error occurred.

Since by default Connector/Python turns autocommit off, and MySQL 5.5 and higher uses transactional InnoDB tables by default, it is necessary to commit your changes using the connection’s commit() method. You could also roll back using the rollback() method.

Источник

Python Insert Into MySQL Table

This article demonstrates how to execute INSERT Query from Python to add a new row into the MySQL table.

In this lesson, you’ll learn the following Python MySQL insert operations using a ‘MySQL Connector’ module.

Insert single and multiple rows into the database table.
Use a parameterized query to insert a Python variable value (Integer, string, float, double, and DateTime) into a database table.

Further Reading:

Prerequisite

Before moving further, Please make sure you have the following in place: –

Username and password to connect MySQL
MySQL table name in which you want to insert data.

I have created a table ‘Laptop’ in the MySQL server to insert records in it. See its column structure in the image.

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your INSERT operations.

Insert a Single Row into MySQL table from Python

How to Insert Into MySQL table from Python

Connect to MySQL from Python Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module
Define a SQL Insert query Next, prepare a SQL INSERT query to insert a row into a table. in the insert query, we mention column names and their values to insert in a table.
For example, INSERT INTO mysql_table (column1, column2, …) VALUES (value1, value2, …);
Get Cursor Object from Connection Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.
Execute the insert query using execute() method Execute the insert query using the cursor.execute() method. This method executes the operation stored in the Insert query.
Commit your changes After the successful execution of a query make changes persistent into a database using the commit() of a connection class.
Get the number of rows affected After a successful insert operation, use a cursor.rowcount method to get the number of rows affected. The count depends on how many rows you are Inserting.
Verify result using the SQL SELECT queryExecute a MySQL select query from Python to see the new changes.
Close the cursor object and database connection object use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Let’ s see the program now

import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='electronics', user='pynative', password='pynative@#29') mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (15, 'Lenovo ThinkPad P71', 6459, '2019-08-14') """ cursor = connection.cursor() cursor.execute(mySql_insert_query) connection.commit() print(cursor.rowcount, "Record inserted successfully into Laptop table") cursor.close() except mysql.connector.Error as error: print("Failed to insert record into Laptop table <>".format(error)) finally: if connection.is_connected(): connection.close() print("MySQL connection is closed")

Record inserted successfully into Laptop table MySQL connection is closed

Use Python Variables in a MySQL Insert Query

Sometimes you need to insert a Python variable value into a table’s column. For example, in the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

We can insert Python variables into the table using the prepared statement and parameterized query.
Using a parameterized query, we can pass Python variables as a query parameter in which placeholders (%s) used for parameters.

import mysql.connector def insert_varibles_into_table(id, name, price, purchase_date): try: connection = mysql.connector.connect(host='localhost', database='Electronics', user='pynative', password='pynative@#29') cursor = connection.cursor() mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s) """ record = (id, name, price, purchase_date) cursor.execute(mySql_insert_query, record) connection.commit() print("Record inserted successfully into Laptop table") except mysql.connector.Error as error: print("Failed to insert into MySQL table <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") insert_varibles_into_table(2, 'Area 51M', 6999, '2019-04-14') insert_varibles_into_table(3, 'MacBook Pro', 2499, '2019-06-20')

Record inserted successfully into Laptop table MySQL connection is closed Record inserted successfully into Laptop table MySQL connection is closed

Refer to fetch rows from MySQL table in Python to check the data you inserted.

Insert multiple rows into MySQL table using the cursor’s executemany()

In the previous example, we have used execute() method of cursor object to insert a single record.

What if you want to insert multiple rows into a table in a single insert query from the Python application. Use the cursor’s executemany() function to insert multiple records into a table.

Syntax of the executemany() method.

cursor.executemany(operation, seq_of_params)

This method executes Insert operation against all parameter sequences in the sequence seq_of_params argument.

You need to include lists of tuples in the seq_of_params argument along with the insert query.

Each tuple inside the list contains a single row that you want to insert. So you can add as many rows in the list and pass a list to a cursor.executemany() function along with the insert query.

Note: Each tuple is enclosed within parentheses and separated by commas. For example, to insert multiple rows in a laptop table, we can use the following SQL Query:

INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s)

And in seq_of_params we are passing the below List.

records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'), (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'), (6, 'Microsoft Surface', 2330, '2019-07-23')]

Example to INSERT multiple rows into a MySQL table

import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='Electronics', user='pynative', password='pynative@#29') mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s) """ records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'), (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'), (6, 'Microsoft Surface', 2330, '2019-07-23')] cursor = connection.cursor() cursor.executemany(mySql_insert_query, records_to_insert) connection.commit() print(cursor.rowcount, "Record inserted successfully into Laptop table") except mysql.connector.Error as error: print("Failed to insert record into MySQL table <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed")

3 Record inserted successfully into Laptop table MySQL connection is closed

Refer to fetch data from the MySQL table to verify your result.

Using cursor.executemany(sql_insert_query, records_to_insert) we are inserting multiple rows (from a List) into the table.
Using the cursor.rowcount we can find the number of records inserted.

Insert timestamp and DateTime into a MySQL table using Python

For example, you have a date column in a MySQL table. Let’s see how to prepare an insert query to add DateTime into a table from Python

from datetime import datetime import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='Electronics', user='pynative', password='pynative@#29') mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date) VALUES (%s, %s, %s, %s) """ cursor = connection.cursor() current_Date = datetime.now() # convert date in the format you want formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S') insert_tuple = (7, 'Acer Predator Triton', 2435, current_Date) result = cursor.execute(mySql_insert_query, insert_tuple) connection.commit() print("Date Record inserted successfully") except mysql.connector.Error as error: connection.rollback() print("Failed to insert into MySQL table <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed")

You can get output like this after the execution of the above code.

Date Record inserted successfully MySQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

15+ Topic-specific Exercises and Quizzes
Each Exercise contains 10 questions
Each Quiz contains 12-15 MCQ

Источник

Python mysql connector insert