Python MySQL Insert into Table (original) (raw)

Last Updated : 28 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 `

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

Now, the Insert into Query can be written as follows: Example: Let's suppose the record looks like this - python-mysql-insert

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

disconnecting from server

mydb.close()

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:

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

disconnecting from server

mydb.close()

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.

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

Python `

import mysql.connector

Connect to the MySQL server

db = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" )

Create a cursor object

cursor = db.cursor()

Prepare the SQL query

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" values = ("John Smith", "123 Main St")

Execute the query

cursor.execute(sql, values)

Commit the changes

db.commit()

Print the number of rows affected

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.