Python MySQL – Insert into Table

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 –

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

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

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.