Python: MySQLdb: Insert records into table

Following program insert records into table employee.

In brief,

Step 1: Get connection object.

db = MySQLdb.connect(host, userName, password, database)

Step 2: Get cursor instance.

cursor = db.cursor()

Step 3: Insert records into table.

record1="""INSERT INTO employee values(1, "Krishna", "Ananda", 100000.00, "krishna@krishna.com")"""
record2="""INSERT INTO employee values(2, "Arjun", "Dhanunjay", 50000.00, "arjun@arjun.com")"""
record3="""INSERT INTO employee values(3, "Ptr", "Ptr", 25000.00, "ptr@ptr.com")"""

cursor.execute(record1)
cursor.execute(record2)
cursor.execute(record3)

Following is the complete working application.

#!/usr/bin/python

# Import MySQLdb module
import MySQLdb

# Print data from given table
def printData(cursor,tableName):
    print('***********************************')
    print("getting all the records from ", tableName)
    sql = "SELECT * FROM "+tableName
    # Execute the SQL command
    cursor.execute(sql)

    # Fetch all the rows
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        firstName = row[1]
        lastName = row[2]
        salary = row[3]
        mailId = row[4]

        print(id, firstName, lastName, salary, mailId)

# Drop and create table
def dropAndCreateTable(cursor,tableName):
    sql="DROP TABLE IF EXISTS "+tableName
    cursor.execute(sql)

    sql = """CREATE TABLE employee
            (
                id int,
                firstName varchar(30),
                lastName varchar(30),
                salary decimal,
                mailId varchar(30),
                PRIMARY KEY(id)
            )"""
    cursor.execute(sql)

# Connection related variables
host="localhost"
userName="root"
password="tiger"
database="sample"

# Open connection to a database
db = MySQLdb.connect(host, userName, password, database)

# Get cursor object
cursor = db.cursor()

# Prepare SQL query to create table
record1="""INSERT INTO employee values(1, "Krishna", "Ananda", 100000.00, "krishna@krishna.com")"""
record2="""INSERT INTO employee values(2, "Arjun", "Dhanunjay", 50000.00, "arjun@arjun.com")"""
record3="""INSERT INTO employee values(3, "Ptr", "Ptr", 25000.00, "ptr@ptr.com")"""

try:
    print("Dropping table employee")
    dropAndCreateTable(cursor,"employee")

    printData(cursor,"employee")

    print("Inserting records")
    cursor.execute(record1)
    cursor.execute(record2)
    cursor.execute(record3)

    print("Commiting data to database")

    db.commit()

    printData(cursor,"employee")
except Exception as e:
    print("Error while processing query", e)
    db.rollback()

finally:
    # Close the connection to database
    db.close()

Output

Dropping table employee
***********************************
('getting all the records from ', 'employee')
Inserting records
Commiting data to database
***********************************
('getting all the records from ', 'employee')
(1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com')
(2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com')
(3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')

Previous                                                 Next                                                 Home