(Sponsors) Get started learning Python with DataCamp's free Intro to Python tutorial. Learn Data Science by completing interactive coding challenges and watching videos by expert instructors. Start Now!
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)
This site generously supported by DataCamp. DataCamp offers online interactive Python Tutorials for Data Science. Join over a million other learners and get started learning Python for data science today!
View Comments