1. Home
  2. Inserting rows

Inserting rows

Updated on Jan 07, 2020

Insert statement is used to insert records in mysql.

Syntax: INSERT INTO <some table> (<some column names>) VALUES("<some values>");

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
from __future__ import print_function

import MySQLdb as my

db = my.connect(host="127.0.0.1",
user="root",
passwd="",
db="world"
)

cursor = db.cursor()

sql = "insert into city VALUES(null, 'Mars City', 'MAC', 'MARC', 1233)"

number_of_rows = cursor.execute(sql)
db.commit()   # you need to call commit() method to save 
              # your changes to the database


db.close()

This programs inserts a new city in the city table, notice the use to db.commit(), this method saves your changes to the database .

Example 2:

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

import MySQLdb as my

db = my.connect(host="127.0.0.1",
user="root",
passwd="",
db="world"
)

cursor = db.cursor()

name = "Some new city"
country_code = 'PSE'
district = 'Someyork'
population = 10008

sql = "insert into city VALUES(null, '%s', '%s', '%s', %d)" % \
 (name, country_code , district, population)

number_of_rows = cursor.execute(sql)
db.commit()

db.close()

Notice the use of backslash (\) character in line 18. The \ character is used to split python statements to multiple lines.

Inserting multiple rows #

To insert multiple rows in the table use executemany() method of cursor object.

Syntax: cursor_object.executemany(statement, arguments)

statement: string containing the query to execute.

arguments: a sequence containing values to use within insert statement.

Let's take an example.

 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
from __future__ import print_function

import MySQLdb as my

db = my.connect(host="127.0.0.1",
user="root",
passwd="",
db="world"
)

cursor = db.cursor()
name = "Some new city"

country_code = 'SNC'

district = 'Someyork'

population = 10008

data = [
('city 1', 'MAC', 'distrct 1', 16822),
('city 2', 'PSE', 'distrct 2', 15642),
('city 3', 'ZWE', 'distrct 3', 11642),
('city 4', 'USA', 'distrct 4', 14612),
('city 5', 'USA', 'distrct 5', 17672),
]

sql = "insert into city(name, countrycode, district, population) 
VALUES(%s, %s, %s, %s)"

number_of_rows = cursor.executemany(sql, data)
db.commit()

db.close()

In the next post, we discuss how to handle errors.

Other Tutorials (Sponsors)

Recent Posts