Open in app

Sign in

Write

Sign in

Python Database Connection Tutorial

Deepak Jha
22 min readNov 8, 2020

--

Databases are critical for storing and processing data even if you consider a powerful programming language like Python. Ever wondered where does this whole large set of data is stored in or fetched from? In this article on “Python database connection”, I’ll talk about the same and take you through the following aspects in detail.

  1. What is a Database and why we need to Connect with Python?
  2. What is MySQLdb?
  3. How does Python connect to a database- MySQL?
  4. How does Python connect to a database- MongoDB?
  5. How does Python connect to a database- Other DB?

1) What is a database?

A database is basically a collection of structured data in such a way that it can easily be retrieved, managed and accessed in various ways. One of the simplest forms of databases is a text database. Relational databases are the most popular database system which includes the following:

  • MySQL
  • Oracle Database
  • SQL server
  • Sybase
  • Informix
  • IBM db2
  • NO SQL

Among all these databases, MySQL is one of the easiest databases to work with. Let me walk you through about this in detail.

2) What is MySQLdb?

MySQLdb is an open-source freely available relational database management system that uses Structured Query Language. Now one of the most important question here is “What is SQL?”

SQL (Structured Query Language) is a standard language for relational databases that allow users to do various operations on data like, Manipulating, Creating, Dropping, etc. In a nutshell, SQL allows you to do anything with the data.

Let’s move ahead and dive deep into Python database connection wherein you will learn how to connect with the database.

3) How does Python connect to a database- MySQL?

It is very simple to connect Python with the database. Refer the below image which illustrates a Python connection with the database where how a connection request is sent to MySQL connector Python, gets accepted from the database and cursor is executed with result data.

There are two ways to connect to DB MySQL:

a) Using Installed DB MySQL

b) Using Remotely Connected MySQL

a) Using Installed DB MySQL

There are mostly 5 steps involved for Working with MySQL Database:

  1. Setting up a Sample Database
  2. Create a Connection Object
  3. Create a Cursor Object to read/write
  4. Interact with Database
  5. Close the Connection

Before connecting to the MySQL database, make sure you have MySQL installer installed on your computer. It provides a comprehensive set of tools which helps in installing MySQL with the following components:

  • MySQL server
  • All available connectors
  • MySQL Workbench
  • MySQL Notifier
  • Tools for Excel and Microsoft Visual Studio
  • MySQL Sample Databases
  • MySQL Documentation

1. Setting up a Sample Database

First, download the following python_mysql database, uncompress the file and copy it to a folder such as C:\mysql\python_mysql.sql :

Download Python MySQL Sample Database

Next, log in to MySQL Server using mysql tool:

mysql -u root -p

Enter the password for the root user.

Enter password: ********

Then, create a new database named python_mysql:

mysql>create database python_mysql;

After that, select the database python_mysql:

mysql>use python_mysql;

Finally, use this command to load data from the file:

mysql> source c:\mysql\python_mysql.sql

To double-check the loading, use the SHOW TABLES command to list all the tables from the python_mysql database:

mysql> SHOW TABLES;

The output will be:

+------------------------+
| Tables_in_python_mysql |
+------------------------+
| authors                |
| book_author            |
| books                  |
+------------------------+
3 rows in set (0.01 sec)

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -

MySql Setup

  1. Download and install MySql Community Server — https://dev.mysql.com/downloads/mysql/
  2. During the installation setup, you will be prompted for a “root” password in the server configuration step.
  3. Download and install MySql Workbench — https://dev.mysql.com/downloads/workbench/
  4. Workbench is GUI tool that allows us to manage your MySql database.
  5. Launch workbench, at the home page, setup a new connection profile with the configuration (Connection method: Standard (TCP/IP), Hostname: 127.0.0.1,Port:3306,Username: root, Password: yourpassword) and test your connection.
  6. Double click on your local instance and it should bring you the schemas view where you can see all your databases and tables.

MySql-Connector-Python Setup

  1. This is the python driver for connecting to MySql server.
  2. Using terminal and conda to download
conda install -c anaconda mysql-connector-python

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -

Before proceeding you should make sure you have MySQL db installed on your computer. Refer the below commands for installing MySQL in command prompt and pycharm:

Using Pip:

Command:

pip install mysql-connector

Using Pycharm

Command:

1

import mysql.connector

Output:

as expected..

Process finished with exit code 0

2. Create a Connection Object

Arguments required to connect MySQL from Python

You need to know the following detail of the MySQL server to perform the connection from Python.

  • Username — i.e., the username that you use to work with MySQL Server. The default username for the MySQL database is a root
  • Password — Password is given by the user at the time of installing the MySQL database. If you are using root then you won’t need the password.
  • Host Name — is the server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost, or it’s IP, i.e. 127.0.0.0
  • Database Name — Database name to which you want to connect. Here we are using Database named ‘Electronics‘ because we have already created this for our example.

Note: We are using the MySQL Connector Python module to communicate with MySQL Server.

There are 2 Way to Connect:

  • Connection Using connect() function
  • Connection using MySQLConnection Object

Connection Using connect() function:

First, download the following python_mysql database, uncompress the file and copy it to a folder such as C:\mysql\python_mysql.sql :

Download Python MySQL Sample Database

Next, log in to MySQL Server using mysql tool:

mysql -u root -p

Enter the password for the root user.

Enter password: ********

Then, create a new database named python_mysql:

mysql>create database python_mysql;

After that, select the database python_mysql:

mysql>use python_mysql;

Finally, use this command to load data from the file:

mysql> source c:\mysql\python_mysql.sql

To double-check the loading, use the SHOW TABLES command to list all the tables from the python_mysql database:

mysql> SHOW TABLES;

The output will be:

+------------------------+
| Tables_in_python_mysql |
+------------------------+
| authors                |
| book_author            |
| books                  |
+------------------------+
3 rows in set (0.01 sec)

Connecting to MySQL database using connect() function

Let’s take a look at the following Python module ( connect.py ).

import mysql.connector
from mysql.connector import Error
def connect():
    """ Connect to MySQL database """
    conn = None
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='python_mysql',
                                       user='root',
                                       password='SecurePass1!')
        if conn.is_connected():
            print('Connected to MySQL database')    except Error as e:
        print(e)    finally:
        if conn is not None and conn.is_connected():
            conn.close()
if __name__ == '__main__':
    connect()

Let’s examine this module in detail:

  • First, import the mysql.connector and Error objects from the MySQL Connector/Python package.
  • Second, use the connect() function to connect to the MySQL Server. The connect() function accepts four parameters: host, database, user and password. The connect() function establishes a connection to the python_mysql database and returns a MySQLConnection object.
  • Third, check if the connection to the MySQL database has been established successfully by using is_connected() method. In case an exception occurs such as MySQL server is not available, the database does not exist or invalid user name or password, Python will raise an exception. The try except block handles the exception and displays the error.
  • Fourth, close the database connection using the close() method of the MySQLConnection object.

To test the connect.py module, you use the following command:

>python connect.py
Connected to MySQL database

If the username or password is not valid you will get the following error:

1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

In case the MySQL Server is not available, you will get the following error:

2003: Can't connect to MySQL server on 'localhost:3306' (10061 No connection could be made because the target machine actively refused it)

Note that, in this example, we hard-coded the database configuration such as localhost , python_mysql , root , within the code, It is not a good practice so let’s fix the code by using a database configuration file.

Connecting to MySQL Database using MySQLConnection object

Steps to connect MySQL database in Python using MySQL Connector Python

  1. Install MySQL Connector Python using pip.
  2. Use the mysql.connector.connect() method of MySQL Connector Python with required parameters to connect MySQL.
  3. Use the connection object returned by a connect() method to create a cursor object to perform Database Operations.
  4. The cursor.execute() to execute SQL queries from Python.
  5. Close the Cursor object using a cursor.close() and MySQL database connection using connection.close() after your work completes.
  6. Catch Exception if any that may occur during this process.

Understand the Python MySQL Database connection program

import mysql.connector

  • This line imports the MySQL Connector Python module in your program so you can use this module’s API to connect MySQL.

from mysql.connector import Error

  • mysql connector Error object is used to show us an error when we failed to connect Databases or if any other database error occurred while working with the database. Example ACCESS DENIED ERROR when username or password is wrong.

mysql.connector.connect()

  • Using this method we can connect the MySQL Database, this method accepts four required parameters: Host, Database, User and Password that we already discussed.
  • connect() method established a connection to the MySQL database from Python application and returned a MySQLConnection object. Then we can use MySQLConnection object to perform various operations on the MySQL Database.
  • The Connect() method can throw an exception, i.e. Database error if one of the required parameters is wrong. For example, if you provide a database name that is not present in MySQL, then Python application throws an exception. So check the arguments that you are passing to this method.

connection.is_connected()

  • is_connected() is the method of the MySQLConnection class through which we can verify is our python application connected to MySQL.

connection.cursor()

  • This method returns a cursor object. Using a cursor object, we can execute SQL queries.
  • The MySQLCursor class instantiates objects that can execute operations such as SQL statements.
  • Cursor objects interact with the MySQL server using a MySQLConnection object.

cursor.close()

  • Using the cursor’s close method we can close the cursor object. Once we close the cursor object, we can not execute any SQL statement.

connection.close()

  • At last, we are closing the MySQL database connection using a close() method of MySQLConnection class.

Now you know how to connect to MySQL server from python let’s proceed with creating a table from Python.

Python MySQL Connection arguments list

As you already know we can connect to MySQL Server from python Using a mysql.connector.connect() method or the mysql.connector.MySQLConnection() class. We already discussed the four mandatory arguments required to connect the MySQL Server.

Let see what the other connection arguments we can use to communicate with MySQL server from Python are. Following is the list of all other connection arguments and their example.

  • port: The TCP/IP port of the MySQL server. This value must be an integer. We can specify the different port number if MySQL server is listening to the different port. The default value for this port argument is 3306.
  • use_unicode: Whether to use Unicode. The default value is True.
  • charset: MySQL character set to use, character set variables relate to a client’s interaction with the server. There are almost 30 to 40 charset MySQL server supports. The default value of the charset argument is “utf8″.
  • auto-commit: Whether to auto-commit transactions. If you want to manage transactions in MySQL from Python, you need to set this value to true or false. The default value is False, i.e. the changes are not committed to the database. You need to explicitly call a commit method to persist your changes in the database.
  • get_warnings: To fetch warning, this is helpful to know the connection is established but with warnings. The default value is False.
  • raise_on_warnings: Whether to raise an exception on warnings. The Default value is False.
  • connection_timeout (connect_timeout*) : Timeout for the TCP and Unix socket connections. The connection terminates after this timeout expired.
  • buffered: Whether cursor objects fetch the results immediately after executing queries. The default value is False.
  • raw: Whether MySQL results are returned as-is, rather than converted to Python types. The default value is False. You can set it to true if you want a query result in python type.
  • force_ipv6: When setting to True, uses IPv6 when an address resolves to both IPv4 and IPv6. By default, IPv4 used in such cases. The default value for this argument is false.
  • pool_name: It is the Connection pool name that you are creating or using.
  • pool_size: Connection pool size that you want to create. the default value is 5.
  • pool_reset_session: Whether to reset session variables when the connection returned to the pool. the default is True.
  • use_pure: Whether to use pure Python or C Extension. If use_pure=False then pure python module is used otherwise it connects MySQL using C extension. Moreover, if C Extension is not available, then My SQL Connector Python automatically fall back to the pure Python implementation.
  • unix_socket: The location of the Unix socket file. These enable communication between two processes.
  • auth_plugin: Authentication plugin to use. Added in 1.2.1.
  • collation: MySQL collation to use. you can use the collation that you set while installing MySQL Server. The default value is utf8_generalW_chiich.
  • sql_mode: Set the sql_mode session variable at connection time.

First, create a database configuration file named config.ini and define a section with four parameters as follows:

[mysql]
host = localhost
database = python_mysql
user = root
password =SecurePass1!

Second, create a new module named python_mysql_dbconfig.py that reads the database configuration from the config.ini file and returns a dictionary object:

from configparser import ConfigParser
def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))    return db

Notice that we used ConfigureParser package to read the configuration file.

Let’s test this module in the REPL:

>>> from python_mysql_dbconfig import read_db_config
>>> read_db_config()
{'host': 'localhost', 'database': 'python_mysql', 'user': 'root', 'password': 'SecurePass1!'}

It works as expected.

Third, create a new module connect2.py that uses the MySQLConnection object to connect to the python_mysql database.

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
def connect():
    """ Connect to MySQL database """    db_config = read_db_config()
    conn = None
    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)        if conn.is_connected():
            print('Connection established.')
        else:
            print('Connection failed.')    except Error as error:
        print(error)    finally:
        if conn is not None and conn.is_connected():
            conn.close()
            print('Connection closed.')
if __name__ == '__main__':
    connect()

Let’s examine the module in greater detail:

  • First, import necessary objects including MySQLConnection , Error from MySQL Connector/Python package and read_db_config from python_mysql_dbconfig module.
  • Second, read the database configuration and pass it to create a new instance of MySQLConnection object in the connect() function. The rest of the code works similar to the first example.

Here is the test of the connect2 module:

>python connect2.py
Connecting to MySQL database...
connection established.
Connection closed.

3. Creating a cursor object to read/write:

The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have multiple separate working environments through the same connection to the database. We can create the cursor object by calling the ‘cursor’ function of the connection object. The cursor object is an important aspect of executing queries to the databases.

The syntax to create the cursor object is given below.

  1. <my_cur> = conn.cursor()

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”, database = “mydb”)
  4. #printing the connection object
  5. print(myconn)
  6. #creating the cursor object
  7. cur = myconn.cursor()
  8. print(cur)

Output:

<mysql.connector.connection.MySQLConnection object at 0x7faa17a15748> 
MySQLCursor: (Nothing executed yet)

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_functionimport MySQLdb as mydb = 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>

4. Interact with the Database

Creating new databases

In this section of the tutorial, we will create the new database PythonDB.

Getting the list of existing databases

We can get the list of all the databases by using the following MySQL query.

  1. > show databases;

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. dbs = cur.execute(“show databases”)
  8. except:
  9. myconn.rollback()
  10. for x in cur:
  11. print(x)
  12. myconn.close()

Output:

('EmployeeDB',)
('Test',)
('TestDB',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mysql',)
('performance_schema',)
('testDB',)

Creating the new database

The new database can be created by using the following SQL query.

  1. > create database <database-name>

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #creating a new database
  8. cur.execute(“create database PythonDB2”)
  9. #getting the list of all the databases which will now include the new database PythonDB
  10. dbs = cur.execute(“show databases”)
  11. except:
  12. myconn.rollback()
  13. for x in cur:
  14. print(x)
  15. myconn.close()

Output:

('EmployeeDB',)
('PythonDB',)
('Test',)
('TestDB',)
('anshika',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)

Creating the table

In this section of the tutorial, we will create the new table Employee. We have to mention the database name while establishing the connection object.

We can create the new table by using the CREATE TABLE statement of SQL. In our database PythonDB, the table Employee will have the four columns, i.e., name, id, salary, and department_id initially.

The following query is used to create the new table Employee.

  1. > create table Employee (name varchar(20) not null, id int primary key, salary float not null, Dept_Id int not null)

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Creating a table with name Employee having four columns i.e., name, id, salary, and department id
  8. dbs = cur.execute(“create table Employee(name varchar(20) not null, id int(20) not null primary key, salary float not null, Dept_id int not null)”)
  9. except:
  10. myconn.rollback()
  11. myconn.close()

Now, we may check that the table Employee is present in the database.

Alter Table

Sometimes, we may forget to create some columns, or we may need to update the table schema. The alter statement used to alter the table schema if required. Here, we will add the column branch_name to the table Employee. The following SQL query is used for this purpose.

  1. alter table Employee add branch_name varchar(20) not null

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #adding a column branch name to the table Employee
  8. cur.execute(“alter table Employee add branch_name varchar(20) not null”)
  9. except:
  10. myconn.rollback()
  11. myconn.close()

Insert Operation

Adding a record to the table

The INSERT INTO statement is used to add a record to the table. In python, we can mention the format specifier (%s) in place of values.

We provide the actual values in the form of tuple in the execute() method of the cursor.

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. sql = “insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)”
  7. #The row values are provided in the form of tuple
  8. val = (“John”, 110, 25000.00, 201, “Newyork”)
  9. try:
  10. #inserting the values into the table
  11. cur.execute(sql,val)
  12. #commit the transaction
  13. myconn.commit()
  14. except:
  15. myconn.rollback()
  16. print(cur.rowcount,”record inserted!”)
  17. myconn.close()

Output:

1 record inserted!

Insert multiple rows

We can also insert multiple rows at once using the python script. The multiple rows are mentioned as the list of various tuples.

Each element of the list is treated as one particular row, whereas each element of the tuple is treated as one particular column value (attribute).

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. sql = “insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)”
  7. val = [(“John”, 102, 25000.00, 201, “Newyork”),(“David”,103,25000.00,202,”Port of spain”),(“Nick”,104,90000.00,201,”Newyork”)]
  8. try:
  9. #inserting the values into the table
  10. cur.executemany(sql,val)
  11. #commit the transaction
  12. myconn.commit()
  13. print(cur.rowcount,”records inserted!”)
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

3 records inserted!

Row ID

In SQL, a particular row is represented by an insertion id which is known as row id. We can get the last inserted row id by using the attribute lastrowid of the cursor object.

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. sql = “insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)”
  7. val = (“Mike”,105,28000,202,”Guyana”)
  8. try:
  9. #inserting the values into the table
  10. cur.execute(sql,val)
  11. #commit the transaction
  12. myconn.commit()
  13. #getting rowid
  14. print(cur.rowcount,”record inserted! id:”,cur.lastrowid)
  15. except:
  16. myconn.rollback()
  17. myconn.close()

Output:

1 record inserted! Id: 0

Read Operation

The SELECT statement is used to read the values from the databases. We can restrict the output of a select query by using various clause in SQL like where, limit, etc.

Python provides the fetchall() method returns the data stored inside the table in the form of rows. We can iterate the result to get the individual rows.

In this section of the tutorial, we will extract the data from the database by using the python script. We will also format the output to print it on the console.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select * from Employee”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. #printing the result
  12. for x in result:
  13. print(x);
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

('John', 101, 25000.0, 201, 'Newyork')
('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')

Reading specific columns

We can read the specific columns by mentioning their names instead of using star (*).

In the following example, we will read the name, id, and salary from the Employee table and print it on the console.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. #printing the result
  12. for x in result:
  13. print(x);
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

('John', 101, 25000.0)
('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)

The fetchone() method

The fetchone() method is used to fetch only one row from the table. The fetchone() method returns the next row of the result-set.

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee”)
  9. #fetching the first row from the cursor object
  10. result = cur.fetchone()
  11. #printing the result
  12. print(result)
  13. except:
  14. myconn.rollback()
  15. myconn.close()

Output:

('John', 101, 25000.0)

Formatting the result

We can format the result by iterating over the result produced by the fetchall() or fetchone() method of cursor object since the result exists as the tuple object which is not readable.

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. print(“Name id Salary”);
  12. for row in result:
  13. print(“%s %d %d”%(row[0],row[1],row[2]))
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

Name    id    Salary
John    101    25000
John    102    25000
David    103    25000
Nick    104    90000
Mike    105    28000

Using where clause

We can restrict the result produced by the select statement by using the where clause. This will extract only those columns which satisfy the where condition.

Consider the following example.

Example: printing the names that start with j

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee where name like ‘J%’”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. print(“Name id Salary”);
  12. for row in result:
  13. print(“%s %d %d”%(row[0],row[1],row[2]))
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

Name    id    Salary
John    101    25000
John    102    25000

Example: printing the names with id = 101, 102, and 103

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee where id in (101,102,103)”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. print(“Name id Salary”);
  12. for row in result:
  13. print(“%s %d %d”%(row[0],row[1],row[2]))
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

Name    id    Salary
John    101    25000
John    102    25000
David    103    2500

Ordering the result

The ORDER BY clause is used to order the result. Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee order by name”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. print(“Name id Salary”);
  12. for row in result:
  13. print(“%s %d %d”%(row[0],row[1],row[2]))
  14. except:
  15. myconn.rollback()
  16. myconn.close()

Output:

Name    id    Salary
David   103    25000
John    101    25000
John    102    25000
Mike    105    28000
Nick    104    90000

Order by DESC

This orders the result in the decreasing order of a particular column.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Reading the Employee data
  8. cur.execute(“select name, id, salary from Employee order by name desc”)
  9. #fetching the rows from the cursor object
  10. result = cur.fetchall()
  11. #printing the result
  12. print(“Name id Salary”);
  13. for row in result:
  14. print(“%s %d %d”%(row[0],row[1],row[2]))
  15. except:
  16. myconn.rollback()
  17. myconn.close()

Output:

Name    id    Salary
Nick    104    90000
Mike    105    28000
John    101    25000
John    102    25000
David    103    25000

Update Operation

The UPDATE-SET statement is used to update any column inside the table. The following SQL query is used to update a column.

  1. > update Employee set name = ‘alex’ where id = 110

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #updating the name of the employee whose id is 110
  8. cur.execute(“update Employee set name = ‘alex’ where id = 110”)
  9. myconn.commit()
  10. except:
  11. myconn.rollback()
  12. myconn.close()

Delete Operation

The DELETE FROM statement is used to delete a specific record from the table. Here, we must impose a condition using WHERE clause otherwise all the records from the table will be removed.

The following SQL query is used to delete the employee detail whose id is 110 from the table.

  1. > delete from Employee where id = 110

Consider the following example.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. #Deleting the employee details whose id is 110
  8. cur.execute(“delete from Employee where id = 110”)
  9. myconn.commit()
  10. except:
  11. myconn.rollback()
  12. myconn.close()

Performing Transactions

Transactions ensure the data consistency of the database. We have to make sure that more than one applications must not modify the records while performing the database operations. The transactions have the following properties.

  1. Atomicity
  2. Either the transaction completes, or nothing happens. If a transaction contains 4 queries then all these queries must be executed, or none of them must be executed.
  3. Consistency
  4. The database must be consistent before the transaction starts and the database must also be consistent after the transaction is completed.
  5. Isolation
  6. Intermediate results of a transaction are not visible outside the current transaction.
  7. Durability
  8. Once a transaction was committed, the effects are persistent, even after a system failure.

Python commit() method

Python provides the commit() method which ensures the changes made to

the database consistently take place.

The syntax to use the commit() method is given below.

  1. conn.commit() #conn is the connection object

All the operations that modify the records of the database do not take place until the commit() is called.

Python rollback() method

The rollback() method is used to revert the changes that are done to the database. This method is useful in the sense that, if some error occurs during the database operations, we can rollback that transaction to maintain the database consistency.

The syntax to use the rollback() is given below.

  1. Conn.rollback()

Closing the connection

We need to close the database connection once we have done all the operations regarding the database. Python provides the close() method. The syntax to use the close() method is given below.

  1. conn.close()

In the following example, we are deleting all the employees who are working for the CS department.

Example

  1. import mysql.connector
  2. #Create the connection object
  3. myconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “google”,database = “PythonDB”)
  4. #creating the cursor object
  5. cur = myconn.cursor()
  6. try:
  7. cur.execute(“delete from Employee where Dept_id = 201”)
  8. myconn.commit()
  9. print(“Deleted !”)
  10. except:
  11. print(“Can’t delete !”)
  12. myconn.rollback()
  13. myconn.close()

Output:

Deleted !

5. Closing the Connection:

Closing the connection

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

Syntax: connection_object.close()

from __future__ import print_functionimport MySQLdb as mydb = 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()

b) Using Remotely Connected MySQL

What is MYSQLdb?

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.

Packages to Install

mysql-connector-python
mysql-python

If using anaconda

conda install -c anaconda mysql-python
conda install -c anaconda mysql-connector-python

else

pip install MySQL-python
pip install MySQL-python-connector

Import Package

import MYSQLdb

How to connect to remote mysql database using python ?

Before we start you should know basics of SQL. Now let us discuss methods used in this code:

  1. connect() : This method is used for creating a connection to our database it have four arguments:
  2. Server Name
  3. Database User Name
  4. Database Provider
  5. Database Name
  6. cursor() : This method creates a cursor object that is capable for executing sql query on database.
  7. execute() : This method is used for executing sql query on database. It takes a sql query( as string) as an argument.
  8. fetchone() : This method retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available.
  9. close() : This method close the database connection.

Free remote mysql database providers:

1.www.freemysqlhosting.net

2.www.heliohost.org

filter_nonebrightness_4

‘’’This code would not be run on geeksforgeeks IDE

because required module

are not installed on IDE. Also this code requires

a remote MySQL databaseconnection with valid

Hostname, Dbusername Password and Dbname’’’

# Module For Connecting To MySQL database

import MySQLdb

# Function for connecting to MySQL database

def mysqlconnect():

#Trying to connect

try:

db_connection= MySQLdb.connect

(“Hostname”,”dbusername”,”password”,”dbname”)

# If connection is not successful

except:

print(“Can’t connect to database”)

return 0

# If Connection Is Successful

print(“Connected”)

# Making Cursor Object For Query Execution

cursor=db_connection.cursor()

# Executing Query

cursor.execute(“SELECT CURDATE();”)

# Above Query Gives Us The Current Date

# Fetching Data

m = cursor.fetchone()

# Printing Result Of Above

print(“Today’s Date Is “,m[0])

# Closing Database Connection

db_connection.close()

# Function Call For Connecting To Our Database

mysqlconnect()

Connected
Today's Date Is  2017-11-14

filter_none

edit

play_arrow

brightness_4

# Python code to illustrate and create a

# table in database

import MySQLdb

# Open database connection

db = MySQLdb.connect(“localhost”,”testuser”,”testpassword”,”gfgdb” )

cursor = db.cursor()

# Drop table if it already exist using execute()

cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”)

# Create table as per requirement

sql = CREATE TABLE EMPLOYEE (

FNAME CHAR(20) NOT NULL,

LNAME CHAR(20),

AGE INT )

cursor.execute(sql) #table created

# disconnect from server

db.close()

This is my first article. Please read and share if you find it useful.

My Name is Deepak Jha.

--

--

Deepak Jha
Deepak Jha

Written by Deepak Jha

11 Followers
33 Following

Author in progress, News Junkie, Technophile-Neophile, Compulsive Overthinker, Full Stack QA

No responses yet

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams