1. Home
  2. Connecting to the database

Connecting to the database

Updated on Jan 07, 2020

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)

Recent Posts