Connecting to the database


Before we start using our database with python we must connect to it. There are four stages of database communication with python:

  1. Create a connection object.
  2. Create a cursor object to read/write.
  3. Interact with the database.
  4. Close the connection.

note:

We will be using world mysql database so first download and import database as follows:

First login to your mysql server

mysql -u root -p

You will be asked for password at this point, enter your password and hit the enter key.

source path/to/world.sql

Connecting to the database #


To connect to the database you need to use connect() method.

Syntax:

1
2
3
4
5
6
MySQLdb.connect(
                 host="127.0.0.1", 
                 user="username", 
                 passwd="password", 
                 db="database" 
               )

On success, connect() method returns a connection object. Otherwise, OperationalError exception will be thrown.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
from __future__ import print_function

import MySQLdb as my

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

print(db)

Notice the import statement in the first line import print_function from __future__, this allows us to use Python 3 version of print() function in Python 2.

Expected Output:

<_mysql.connection open to '127.0.0.1' at 21fe6f0>

Creating cursor object #


Before you start interacting with the database you need to create cursor object.

Syntax : connection_object.cursor()

On success, it returns a Cursor object, otherwise an exception will be thrown.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
from __future__ import print_function

import MySQLdb as my

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

print(db)

cursor = db.cursor()

print(cursor)

Expected Output:

1
2
<_mysql.connection open to '127.0.0.1' at 239e2c0>
<MySQLdb.cursors.Cursor object at 0x02444AD0>

Interacting with the database #


cursor object has execute() method which you can use to execute sql queries.

Syntax: cursor.execute(sql)

On success it returns number of affected rows, otherwise it throws an exception.

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

import MySQLdb as my

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

print(db)

cursor = db.cursor()

number_of_rows = cursor.execute("select * from city");

print(number_of_rows)

Expected Output:

4079

Closing the connection #


After interacting with the database you need to close the database connection to give up resources.

Syntax: connection_object.close()

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

import MySQLdb as my

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

print(db)

cursor = db.cursor()

number_of_rows = cursor.execute("select * from city");

print(number_of_rows)

db.close()

Now you know how to connect with database, execute queries and close the connection. In the next post, we discuss how to fetch rows from the table.


Other Tutorials (Sponsors)