Python MySQL - Insert Data

Advertisements

Previous Page
Next Page  

You can add new rows to an existing table of MySQL using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names).

Syntax

Following is the syntax of the INSERT INTO statement of MySQL.

INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Example

Following query inserts a record into the table named EMPLOYEE.

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('
   Mac', 'Mohan', 20, 'M', 2000
);

You can verify the records of the table after insert operation using the SELECT statement as −

mysql> select * from Employee;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Mac        | Mohan     | 20   | M    | 2000   | 
+------------+-----------+------+------+--------+
1 row in set (0.00 sec)

It is not mandatory to specify the names of the columns always, if you pass values of a record in the same order of the columns of the table you can execute the SELECT statement without the column names as follows −

INSERT INTO EMPLOYEE VALUES ('Mac', 'Mohan', 20, 'M', 2000);

Inserting data in MySQL table using python

The execute() method (invoked on the cursor object) accepts a query as parameter and executes the given query. To insert data, you need to pass the MySQL INSERT statement as a parameter to it.

cursor.execute("""INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)""")

To insert data into a table in MySQL using python −

  • import mysql.connector package.

  • Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.

  • Create a cursor object by invoking the cursor() method on the connection object created above

  • Then, execute the INSERT statement by passing it as a parameter to the execute() method.

Example

The following example executes SQL INSERT statement to insert a record into the EMPLOYEE table −

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Preparing SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(
   FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Executing the SQL command
   cursor.execute(sql)

   # Commit your changes in the database
   conn.commit()

except:
   # Rolling back in case of error
   conn.rollback()

# Closing the connection
conn.close()

Inserting values dynamically

You can also use “%s” instead of values in the INSERT query of MySQL and pass values to them as lists as shown below −

cursor.execute("""INSERT INTO EMPLOYEE VALUES ('Mac', 'Mohan', 20, 'M', 2000)""", 
   ('Ramya', 'Ramapriya', 25, 'F', 5000))

Example

Following example inserts a record into the Employee table dynamically.

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Preparing SQL query to INSERT a record into the database.
insert_stmt = (
   "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)"
   "VALUES (%s, %s, %s, %s, %s)"
)
data = ('Ramya', 'Ramapriya', 25, 'F', 5000)

try:
   # Executing the SQL command
   cursor.execute(insert_stmt, data)
   
   # Commit your changes in the database
   conn.commit()

except:
   # Rolling back in case of error
   conn.rollback()

print("Data inserted")

# Closing the connection
conn.close()

Output

Data inserted
Advertisements