Connect your database in Python code — works almost everywhere

Why need to Connect?

In a real world scenarios, when you’re working with data or some kind of huge chunks of it, using database to store and process your data is a de facto method of doing so. It is more preferable to use cloud solutions to store, manage and process your data using cloud based solutions such as AWS which have an API of their own, in this story we sill be discussing about connecting core Python code base to a basically default database solution available.

source: edureka

Use of NoSQL database seems to be rising these days, check MongoDB if it suits your purpose over SQL database solutions. Here, we’ll connect MySQL database to our python code.

Python’s database interfacing

The python standard for database interfaces in python DB-API. Most python databases interfaces adhere to this standard. You can choose the right database for your application. Python Database API supports a wide range of database servers as:

1. MySQL/MariaDB
2. PostgreSQL
3. Microsoft SQL Server
4. Oracle
5. SQLite
6. IBM DB2

You must download a separate DB API module for each databases you need to access. For example, if you need to access an Oracle database as well as MySQL database, you need to download both the Oracle and MySQL database modules.

The DB API provides a minimal standard for working with databases using Python, the structures and syntax wherever possible. The API includes the following:

• Importing the API module
• Acquiring the connection with the database
• Issuing SQL statements and stored procedures
• Closing the connection

Python has built-in support for SQLite. We use ‘PyMySQL’ module to work with database using Python.

Here in this story, we’re gonna be using MySQL database. Implementation with other stack is similar of the kind.

About PyMySQL

PyMySQL is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and contains a pure-python MySQL client library. The goal of PyMySQL is to a drop-in replacement for MySQLdb.

Installing PyMySQL

Before proceeding further, you make sure you have PyMySQL installed on your machine. Just type the following in you Python script and execute it.

#usr/bin/python3
import PyMySQL

When this module found to be not installed in your system, you can get the latest stable version of this module using pip.

Alternatively, if pip is not available, a tarball can be downloaded from GitHub and installed with Setuptools (setup.py).

Now, the database connection

Before connecting to MySQL database, make sure of following points:

• A database called TESTDB should be created in the database (prefer using GUI client like phpmyadmin for the purpose).
• A table should be created inside database.
• The table should have suitable fields.
• Set credentials (username and password) to access the TESTDB database.
• Python module PyMySQL is installed properly on the machine.
• Learn well about MySQL. (Refer this tutorial)

Working with database server

First, create a connection.

creating a mysql database connection using Python 3

Then, prepare a cursor for working with database (to execute SQL queries), it is done using cursor() method:

creating database cursor

Finally, write a SQL query and execute it. This is a sample query execution from my experimental web crawler I’ve developed for testing purposes:

executing SQL query using database connection with exception handling

Also, you can print a fancy success message as below:

happy review (just for fun)

Here’s how the stuff works (the output):

Testing on windows machine (from second image above)

Conclusion

The query must be on good string format. Few practices will let you learn the data structure during the transaction (especially important to get the data from database -> which comes kinda array of tuples format).

Happy coding!