In This Article, You Will Know About Python MySQL insert Table.
Python MySQL Insert Table – Before moving ahead, let’s know a bit about Python MySQL Create Table.
Table of Contents
1. INSERT INTO – Single Row Value
To fill the values in a column, use ‘INSERT INTO’ statement.
Syntax: INSERT INTO TABLE_NAME (COLUMN_NAME_1, COLUMN_NAME_2, ….) VALUES(VALUE1, VALUE2, ….)
Example: Insert values for first row in the “stu_information” table.
import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
username="your_MySQL_username",
password="Your_MySQL_password",
database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()
database_for_mysql.execute(
"INSERT INTO Stu_information (name, class, roll_no, section) VALUES ('Akshay', '9TH', 5202, 'A')")
my_user_details.commit()
As a result, it returned a table containing data in first row.
Syntax: SELECT * FROM TABLE_NAME
In above statement, * (asterick) means, select all columns from specified table.
Note: The commit() statement is used to change the table; if it is not used, then no changes will be made.
2. INSERT INTO – Multiple Rows Value
Syntax: INSERT INTO TABLE_NAME (COLUMN_NAME_1, COLUMN_NAME_2, ….) VALUES(VALUE1, VALUE2, ….), (VALUE1, VALUE2, ….), (VALUE1, VALUE2, ….)
Every time (group_of_values) will be separated by a comma to give multiple values.
Example: Insert multiple values for rows in the “stu_information” table.
import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
username="your_MySQL_username",
password="Your_MySQL_password",
database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()
database_for_mysql.execute(
"INSERT INTO Stu_information (name, class, roll_no, section) VALUES('Aman', '10TH', 5203, 'B'),('Akash', '11TH', 5204, 'C'), ('Ajay', '12TH', 5205, 'D')")
my_user_details.commit()
As a result, it returned a table containing data in four rows, including the first row’s data given in the above step.
3. Get Inserted ID
lastrowid – The lastrowid property returns the ID number from the AUTO_INCREMENT column.
Note: If you insert the single row value, it returns the ID number of the last inserted row.
Note: If you insert multiple row values, it returns the ID number of the first most inserted row.
Example: Insert the row, and return the ID.
import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
username="your_MySQL_username",
password="Your_MySQL_password",
database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()
database_for_mysql.execute(
"INSERT INTO Stu_information (name, class, roll_no, section) VALUES('Aman', '10TH', 5203, 'B'),('Akash', '11TH', 5204, 'C'), ('Ajay', '12TH', 5205, 'D')")
my_user_details.commit()
print("Return the ID for inserted record:", mydatabaseformysql.lastrowid)
