Python MySQL Insert Into Table
Python Insert Data Into Mysql Table
After creating a database and a table, we can begin adding information.
The following are some rules to follow in terms of syntax :
- In Python the SQL query has to be quoted.
- You need to quotes string values (text columns) inside the SQL query.
- You don't need to quotes for numerical values.
- The name NULL and DEFAULT keywords are cannot be quoted.
Use the INSERT INTO command to add new records into a MySQL table.
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
We established an empty table called "books" with three columns : "id", "name", and "price" in the previous chapter.
Look at the table structure:
| Column Name | Datatype | Extra |
|---|---|---|
| id | int | auto_increment + primary key |
| name | text | |
| price | float |
Note :- If there's AUTO INCREMENT column (like the column ID), the SQL query shouldn't have to specify it; MySQL adds the value automatically.
Insert Into Table
The execute() method is used to run any sql query against mysql database.
The rowcount property is used to return number of affected rows executed by last queryies.
The commit() method is used to save all changes made by sql queries like insert, update, delete records and so on.
Note:- You cannot get any changes (record insert, update, and delete) in the table, If you omit the commit() method.
Example :- Insert a record into "books" table :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
# Insert a record using normal sql query
sql = "INSERT INTO books (name, price) VALUES ('python cookbook', 175.50)"
mycursor.execute(sql)
print(mycursor.rowcount, "record inserted.")
# Insert a record using prepared statement sql query (Recommended)
# It gives more security compared to above one
qry = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = ("python complete reference", "220")
mycursor.execute(qry, val)
print(mycursor.rowcount, "record inserted.")
mydb.commit() Output :-
1 record inserted.
After executing the above program the "books" table look like:
| ID | Name | Price |
|---|---|---|
| 1 | python cookbook | 175.50 |
| 2 | python complete reference | 220.00 |
Important!: Please note: mydb.commit(), Changes must be made, otherwise the table cannot be changed.
Insert Multiple Rows
The executemany() method is used to insert multiple records at once into a table.
The second parameter of the method executemany() is a tuple list that
includes the data that are to be inserted :
Example :- Insert three records into "books" table with data :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = [
('Python Networking', 160),
('Web Programming', 70),
('Web with Python', 120)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record was inserted.")
Output :-
Related Links
Get Last Inserted ID
By inquiring the cursor object you may acquire the id of the row you just inserted.
The lastrowid property is used to return last inserted id from the table.
Note : The id of the latest inserted rows is returned if more than one row is inserted.
Example :- Get last inserted id :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = ("New era of python", 55)
mycursor.execute(sql, val)
mydb.commit()
print("ID: ", mycursor.lastrowid) Output :-
Related Links