OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Inserting Data using Connector/Python

Inserting Data using Connector/Python

Last updated on July 27, 2020

In the last lesson, we have created the database called blog along with two tables category and post. In this lesson, we will insert some rows into the tables.

Inserting a single row #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import mysql.connector
from datetime import datetime

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql1 = "insert into category(name) value(%s)"
data1 = ('python',)

sql2 = "insert into post(title, content, date, category_id) value(%s, %s, %s, %s)"

cursor.execute(sql1, data1)

category_id = cursor.lastrowid  # get the id of the last inserted row

data2 =  ('title 1', 'content 1', datetime.now().date() , category_id)

cursor.execute(sql2, data2)

db.commit()   # commit the changes

cursor.close()
db.close()

How it works:

  1. In line 5, we insert a new category into the category table

  2. In line 6, we read the value of last inserted id using the lastrowid attribute of the cursor object.

  3. In line 8, we create data for the new post and in line 10, we execute the insert statement into the post table.

  4. Finally, in line 14, we are calling the commit() method to save the changes to the database. This is necessary if you don't call the commit() method your changes will not be saved into the database.

Bulk Insert #

The following listing inserts rows in bulk into the category and post table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import mysql.connector
from datetime import datetime

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor(buffered=True)

sql1 = "insert into category(name) value(%s)"

data1 = [
    ('css',),
    ('java',),
    ('design',),
    ('ui',),
    ('php',),
]

cursor.executemany(sql1, data1)  # insert categories into the category table

db.commit()  # commit the changes

sql2 = "insert into post(title, content, category_id) value(%s, %s, %s, %s)"

data2 = [
    ('title 2', 'content 2', datetime.now.date(), 1),
    ('title 3', 'content 3', datetime.now.date(), 1),
    ('title 4', 'content 4', datetime.now.date(), 1),
    ('title 5', 'content 5', datetime.now.date(), 1),
    ('title 6', 'content 6', datetime.now.date(), 1),
]

cursor.executemany(sql2, data2)

db.commit()  # commit the changes

cursor.close()
db.close()

MySQL Connector/Python Tutorial

Recent Posts