Python MySQL - Introduction
Python MySQL - Database Connection
Python MySQL - Create Database
Python MySQL - Create Table
Python MySQL - Insert Data
Python MySQL - Select Data
Python MySQL - Where Clause
Python MySQL - Order By
Python MySQL - Update Table
Python MySQL - Delete Data
Python MySQL - Drop Table
Python MySQL - Limit
Python MySQL - Join
Python MySQL - Cursor Object
Python PostgreSQL
Python PostgreSQL - Introduction
Python PostgreSQL - Database Connection
Python PostgreSQL - Create Database
Python PostgreSQL - Create Table
Python PostgreSQL - Insert Data
Python PostgreSQL - Select Data
Python PostgreSQL - Where Clause
Python PostgreSQL - Order By
Python PostgreSQL - Update Table
Python PostgreSQL - Delete Data
Python PostgreSQL - Drop Table
Python PostgreSQL - Limit
Python PostgreSQL - Join
Python PostgreSQL - Cursor Object
Python SQLite
Python SQLite - Introduction
Python SQLite - Establishing Connection
Python SQLite - Create Table
Python SQLite - Insert Data
Python SQLite - Select Data
Python SQLite - Where Clause
Python SQLite - Order By
Python SQLite - Update Table
Python SQLite - Delete Data
Python SQLite - Drop Table
Python SQLite - Limit
Python SQLite - Join
Python SQLite - Cursor Object
Python MongoDB
Python MongoDB - Introduction
Python MongoDB - Create Database
Python MongoDB - Create Collection
Python MongoDB - Insert Document
Python MongoDB - Find
Python MongoDB - Query
Python MongoDB - Sort
Python MongoDB - Delete Document
Python MongoDB - Drop Collection
Python MongoDB - Update
Python MongoDB - Limit
Python Data Access Resources
Python Data Access - Quick Guide
Python Data Access - Useful Resources
Python Data Access - Discussion
Selected Reading
UPSC IAS Exams Notes
Developer's Best Practices
Questions and Answers
Effective Resume Writing
HR Interview Questions
Computer Glossary
Who is Who
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)"""
# 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)
# 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
Print Page
Tutorials Point is a leading Ed Tech company striving to provide the best learning material on technical and non-technical subjects.
About us
