Python MySQL Insert Table

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()

				
			
python-mysql-insert-table

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()
				
			
python-mysql-insert-table

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)
				
			

As there is no value generated from AUTO_INCREMENT column, therefore it returned 0 as ID number.

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Recent Post

Popular Post

Top Articles

Archives
Categories

Share on