Best post : Database connectivity in Python (Part 1)

Database connectivity in Python

In this blog we are going to establish MySQL database connectivity with python. Database connectivity plays important role in developing dynamic applications.

What is Data?

Data is collection of different set unprocessed information from multiple heterogeneous sources.
Data can be in different forms like number, text, video, audio etc.
Data can be organized or unorganized depending on its collection point.

What is database?

A database is an organized collection of data, generally stored and accessed electronically from a computer system.
A database is collection of data in grid like structures i.e. row and columns.
The database is to operate a large amount of information by storing, retrieving, and managing data.
There are many databases available like MySQL, Sybase, Oracle, MongoDB, PostgreSQL, SQL Server, etc.
Structured Query Language is used to operate on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.

How to interact with the database using python?

To interact with the database using python it is mandatory to establish database connectivity with python using any database in backend.

To interact with database using python:
We can write script in python to operate a large amount of information by storing, retrieving, and managing data.
We can write commands in Python interpreter

In this blog we will be using connectivity between MySQL and Python using mysql.connector module.

How to install mysql.connector?

To install mysql.connector, first download .exe file of mysql connector for required version depending on python version.
Prerequisite for Python Installation and MySQL Connectivity in Windows are:

  • Python 2.7.13 Windows Installer Package.
  • Python 3.4.3 Windows Installer Package.
  • MySQL Essential 5.1.36 Windows Installer Package
  • MySQL-Connector-Python-2.1.6-py3.4-win64
  • MySQL Python 1.2.4b4.win64-py2.7 Application File

Install Python 2.7.13

  • Click on python 2.7.13 .exe file to launch set up window.
  • Click on install for all users and click on next
  • Select Destination Directory and click on next
  • Customize Python 2.7.13 window appears, in that scroll down and click on Add python.exe to path.Select Entire feature will be installed on local hard drive and click on next.
  • Python 2.7.13 will be installed successfully

Install Python 3.4.3

  • Click on python 3.4.3 .exe file to launch set up window.
  • Click on install for all users and click on next
  • Select Destination Directory and click on next
  • Customize Python 3.4.3 window appears, in that scroll down and click on Add python.exe to path.Select Entire feature will be installed on local hard drive and click on next.
  • Python 3.4.3 will be installed successfully

MySQL Essential 5.1.36 Windows Installer Package

  • Click on setup file of MySQL 5.1.36 to launch set up window and click on Next
  • In setup type window select COMPLETE , click on Next and Install
  • MySQL Enterprise appears, Click on Next
  • Wizard completed appears ,Click on Next.
  • Welcome to MySQL Server Instance Configuration wizard 1.0.15.0 appears, Click on Next.
  • In Configuration type window select Detailed Configuration, Click on Next.
  • In server type window select Developer Machine, Click on Next.
  • In database window select Multifunctional database , Click on Next.
  • In InnoDB wizard keep everything as it is and click on Next.
  • In concurrent connection wizard select Decision Support (DSS/OLAP), Click on Next.
  • Keep network options as it is an click on Next.
  • In Default character set , Select Standard Character set and click on Next.
  • In security option give new password and confirm password for root and check Enable root access from remote machine and click on Next.
  • Click on Execute to proceed installation.
  • MySQL Essential 5.1.36 will be installed successfully

Install MySQL-Connector-Python-2.1.6-py3.4-win64

  • Copy MySQL-Connector-Python-2.1.6-py3.4-win64 setup file in Python 34 folder.
  • Click on MySQL-Connector-Python-2.1.6-py3.4-win64 file to launch set up window.
  • MySQL-Connector-Python-2.1.6-py3.4-win64 will be installed successfully

Install MySQL Python 1.2.4b4.win64-py2.7 Application File

  • Copy MySQL Python 1.2.4b4.win64-py2.7 Application File setup file in Python 27 folder.
  • Click on MySQL Python 1.2.4b4.win64-py2.7 Application File to launch set up window.
  • No changes are required just keep on clicking next.
  • MySQL Python 1.2.4b4.win64-py2.7 Application File will be installed successfully

Basic MySQL commands required

To perform MySQL operations open MySQL Command Line Client and enter the password given during installation
Create query
Insert query
Select query
Update query
Drop Query
Delete query
Apart from this:
desc tablename, show databases, show tables, use database etc will be required.

Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)

Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)

Basic SQL queries (itvoyagers.in)
Basic SQL queries (itvoyagers.in)

You can also check out other posts related to python file handling, GUI, and basic python.

Leave a Comment