coderz.py

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

Using the INSERT INTO statement, you can add new rows to an existing MySQL table. In this section, you must specify the table’s name, column names, and values (in the same order as column names).

Syntax:

INSERT INTO table_name (column_names) VALUES(data)

Example:

The following query inserts a record into the table named EMPLOYEE.

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('
   Mac', 'Mohan', 20, 'M', 2000
);

Inserting data in MySQL table using python:

The execute() method (called on the cursor object) takes a query as a parameter and executes it. You must pass the MySQL INSERT statement as a parameter to insert data. The table’s name, column names, and values (in the same order as column names).

The following example executes SQL INSERT statement to insert a record into the EMPLOYEE table −

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Preparing SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(
   FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Executing the SQL command
   cursor.execute(sql)

   # Commit your changes in the database
   conn.commit()

except:
   # Rolling back in case of error
   conn.rollback()

# Closing the connection
conn.close()

We can insert data in a table in two ways:

• Inserting a single row at a time
• Inserting multiple rows at a time

Inserting a single row at a time:

import mysql.connector

mydb = mysql.connector.connect(
    host="hostname",
    user="username",
    password="password"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(name, branch, address) VALUES (%s, %s,%s)"
## There is no need to insert the value of rollno 
## because in our table rollno is autoincremented #started from 1
## storing values in a variable
values = ("Rabecca", "CSE", "220 Baker Street, London")

## executing the query with values
cursor.execute(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "record inserted")

In the above code, we have inserted the data in the table as a single row at a time.

Inserting multiple rows at a time:

The executemany() method is utilized to add numerous rows to the table. The query is the first argument, and the second parameter is a list of tuples holding the data.

import mysql.connector

mydb = mysql.connector.connect(
    host="hostname",
    user="username",
    password="password"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(Name, Branch,Address) VALUES (%s, %s, %s)"

## storing values in a variable
values = [
    ("Peter", "ME","Noida"),
    ("Amy", "CE","New Delhi"),
    ("Michael", "CSE","London")
]

## executing the query with values
cursor.executemany(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "records inserted")

If all three rows were entered successfully, the output of the above code will be:

3 records inserted

Note: also read about Create and List Table-Python

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py