Python MySQL Insert into Table (original) (raw)

Last Updated : 25 Apr, 2025

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

Python3

import mysql.connector

mydb = mysql.connector.connect(

`` host = "localhost",

`` user = "username",

`` password = "password",

`` database = "database_name"

)

mycursor = mydb.cursor()

Now, the Insert into Query can be written as follows: Example: Let’s suppose the record looks like this –

Python3

sql = "INSERT INTO Student (Name, Roll_no) VALUES ( % s, % s)"

val = ("Ram", " 85 ")

mycursor.execute(sql, val)

mydb.commit()

print (mycursor.rowcount, "details inserted")

mydb.close()

Output:

1 details inserted

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:

Python3

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

mydb.close()

Output:

7 details inserted

Note:

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

Python program that inserts a row into a MySQL table using the mysql-connector library:

Python

import mysql.connector

db = mysql.connector.connect(

`` host = "localhost" ,

`` user = "yourusername" ,

`` password = "yourpassword" ,

`` database = "yourdatabase"

)

cursor = db.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"

values = ( "John Smith" , "123 Main St" )

cursor.execute(sql, values)

db.commit()

print (cursor.rowcount, "record inserted." )

This program connects to a MySQL server and inserts a row into a table named customers with the name and address values provided. The mysql-connector library is used to interact with the MySQL server.

The time complexity of this program depends on the efficiency of the MySQL server and the size of the table being inserted into. The execute() function is generally fast, with a time complexity of O(1), but the actual time it takes to execute the query will depend on the size and complexity of the table.

The space complexity of this program is also O(1), as it doesn’t create any significant additional data structures beyond the values variable.

When run, this program should output a message indicating the number of rows affected by the query, which in this case should be 1.