Related Articles

Related Articles

Python MySQL – Insert into Table
  • Last Updated : 06 Mar, 2020

MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify the same command.

Note: Before we insert data into our database, we need to create a table. In order to do so, refer to Python: MySQL Create Table.

Inserting data

You can insert one row or multiple rows at once. The connector code is required to connect the commands to the particular database.

Connector query

filter_none

edit
close

play_arrow

link
brightness_4
code

# Enter the server name in host
# followed by your user and
# password along with the database 
# name provided by you.
  
import mysql.connector
  
  
mydb = mysql.connector.connect(
  host = "localhost",
  user = "username",
  password = "password",
  database = "database_name"
  
mycursor = mydb.cursor()

chevron_right


Now, the Insert into Query can be written as follows:



Example: Let’s suppose the record looks like this –

python-mysql-insert

filter_none

edit
close

play_arrow

link
brightness_4
code

sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"
val = ("Ram", "85")
  
mycursor.execute(sql, val)
mydb.commit()
  
print(mycursor.rowcount, "details inserted")
  
# disconnecting from server
mydb.close()

chevron_right


Output:

1 details inserted

python-mysql-insert-2

To insert multiple values at once, executemany() method is used. This method iterates through the sequence of parameters, passing the current parameter to the execute method.

Example:

filter_none

edit
close

play_arrow

link
brightness_4
code

sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"
val = [("Akash", "98"),
       ("Neel", "23"),
       ("Rohan", "43"),
       ("Amit", "87"),
       ("Anil", "45"), 
       ("Megha", "55"), 
       ("Sita", "95")]
  
mycursor.executemany(sql, val)
mydb.commit()
  
print(mycursor.rowcount, "details inserted")
  
# disconnecting from server
mydb.close()

chevron_right


Output:

7 details inserted

python-mysql-insert-3

Note:

  • The cursor() is used in order to iterate through the rows.
  • Without the command mydb.commit() the changes will not be saved.

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.




My Personal Notes arrow_drop_up
Recommended Articles
Page :