Unit1 – Database Connectivity with Python
Few connectors of MySQL with Python
Pymysql
Mysql.connector
MySQLdb
Python can be used in database applications. One of the most popular databases is MySQL. Pthon Database API
supports a wide range of database servers such as –
GadFly General Purpose Database Systems Data Warehouse Database
mSQL IBM DB2 Systems
MySQL Firebird (and Interbase) Teradata
IBM Netezza
PostgreSQL Informix
Microsoft SQL Server 2000 Ingres
Informix MySQL
Interbase Oracle
Oracle PostgreSQL
Sybase SAP DB (also known as "MaxDB")
SQLite Microsoft SQL Server
Microsoft Access
Sybase
What is 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 be a drop-in
replacement for MySQLdb.
pip install pymysql
What is MySQL-Connector ?
MySQL Connector/Python is an API implemented using pure Python. It means that you don't need to install
anyMySQL client library or any Python modules except the standard library.
python –m pip install mysql-connector
What is MySQLdb ?
MySQLdb is an interface to the popular MySQL database server for Python. The design goals are:
Compliance with Python database API
Thread-safety
Thread-friendliness (threads will not block each other)
MySQL-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported. Python-3.0 will be supported in a
future release. PyPy is supported.
pip install MySQL-python
Database Connection
Before connecting to a MySQL database, make sure of the following points −
You have created a database fm (in my computer).
You have created a table EMPLOYEE in fm.
This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
User ID "root" and password "123456" are set to access fm.
Python module PyMySQL is installed properly on my machine.
Practical 1:- To Establish a Connection b/w MySQL and Python.
Output –
Practical 2:- Following is an example of connecting with MySQL database "fm".
Practical 3:- Program to create tables or records into the database tables using execute method of the
created cursor.
Practical 4:- Program to create a new Database “Python”.
Cursor: - class cursor allows Python code to execute MySQL command in a database session. Cursors are
created by the connection.cursor() method. They are bound to the connection for the entire lifetime and
all the commands are executed in the context of the database session wrapped by the connection.
Program5:- Write Command to Show Databases.
Program6:- Write Command to Show Table in a Databases.
Program7:- Write Command to create primary key when creating the table.
Program8:- Write Command to create Primary Key on an existing table:
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
Program9:- Write Command to Insert a record in the "customers" table:
Program10:- Write Command to insert multiple rows into a table, use the executemany() method.
Program11:- Write Command to Select all records from the "customers" table, and display the result:
Program12:- Write Command to Select only the name and address columns:
Program13:- Write Command to select only Fetch only one row:
The fetchone() method will return the first row of the result:
Program14:- Write Command to delete any record.
Program15:- Write Command to drop a table.
sql = "DROP TABLE customers"
mycursor.execute(sql)
