MySQL Python Connector
MySQL Python connector enables Python programs to access MySQL databases, the connector uses an API which is compliant with the Python Database API Specification. The API is written in pure Python and does not have any dependencies except for the Python Standard Library. The connector provides full support for :
- MySQL functionality
- Connections using TCP/IP sockets and on Unix using Unix sockets.
- Secure TCP/IP connections using SSL.
- Self-contained driver. The connector does not require the MySQL client library or any Python modules outside the standard library.
Python Connector version details :
|1.1 ( current development series.)||5.7, 5.6, 5.5 (5.1, 5.0, 4.1)||3.1 and later, 2.7, 2.6|
|1.0||5.7, 5.6, 5.5 (5.1, 5.0, 4.1)||3.1 and later, 2.7, 2.6 (2.5, 2.4)|
Download Python Connector :
The python connector runs on any platform where Python is installed. Python comes preloaded on almost all Linux distribution or Unix-like systems such as Mac OS X and FreeBSD. You can download the latest version of Python Connector binaries and sources from the following website -
Install Python Connector on Microsoft Windows
Before installing the Connector on Windows :
- To complete the installation operation, root or administrator privileges may be required.
- Before installing the Python distribution on your system, enable python.exe in Windows %PATH% setting ( path environment variable) or manually add it to, if it is not enabled.
Installing the Windows Connector/ODBC Driver :
Select and download the MSI installer packages from http://dev.MySQL.com/downloads/connector/python/ as per your requirement.
Next, we will install the "Windows (Architecture Independent), MSI Installer, Python 3.2 " connector in a preloaded Windows 7, MySQL 5.6 and Python 3.2.
Now follow the following steps :
Step -1 :
Double click the installer (here it is 'MySQL-connector-python-1.2.2-py3.2.msi')
Step -2 :
Click on 'Run' and complete the process.
Connecting to MySQL using Python connector
The following example shows how to connect and handle errors to the MySQL server. The connect() constructor creates a connection to the MySQL server and returns a MySQL Connection object. Within the example code, it is stored in the variable 'db'.
#!/usr/bin/python import MySQL.connector from MySQL.connector import errorcode try: db = MySQL.connector.connect(user='root', password='datasoft123', host='127.0.0.1', database='sakila') except MySQL.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Acess denied/wrong user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exists") else: print(err) else: db.close()
Querying data using Python connector
We want to get the names (first_name, last_name), salary of the employees whose salary greater than the average salary of all department.
Structure of 'hr' database:
Sample table: employees
SELECT b.first_name,b.last_name FROM employees b WHERE NOT EXISTS (SELECT 'X' FROM employees a WHERE a.manager_id = b.employee_id);
Let execute above code in Python 3.2 :
#!/usr/bin/python import MySQL.connector db = MySQL.connector.connect(host="localhost", # Host, usually localhost user="root", # your username password="**********", # your password db="hr") # name of the data base #create a Cursor object. cur = db.cursor() # Write SQL statement here cur.execute("SELECT b.first_name,b.last_name FROM employees b WHERE NOT EXISTS (SELECT 'X' FROM employees a WHERE a.manager_id = b.employee_id);") # print all the first and second cells of all the rows for row in cur.fetchall() : print (row,row)
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join