- Python Data Access Tutorial
- Python Data Access - Home
- Python MySQL
- Python MySQL - Introduction
- Python MySQL - Database Connection
- Python MySQL - Create Database
- Python MySQL - Create Table
- Python MySQL - Insert Data
- Python MySQL - Select Data
- Python MySQL - Where Clause
- Python MySQL - Order By
- Python MySQL - Update Table
- Python MySQL - Delete Data
- Python MySQL - Drop Table
- Python MySQL - Limit
- Python MySQL - Join
- Python MySQL - Cursor Object
- Python PostgreSQL
- Python PostgreSQL - Introduction
- Python PostgreSQL - Database Connection
- Python PostgreSQL - Create Database
- Python PostgreSQL - Create Table
- Python PostgreSQL - Insert Data
- Python PostgreSQL - Select Data
- Python PostgreSQL - Where Clause
- Python PostgreSQL - Order By
- Python PostgreSQL - Update Table
- Python PostgreSQL - Delete Data
- Python PostgreSQL - Drop Table
- Python PostgreSQL - Limit
- Python PostgreSQL - Join
- Python PostgreSQL - Cursor Object
- Python SQLite
- Python SQLite - Introduction
- Python SQLite - Establishing Connection
- Python SQLite - Create Table
- Python SQLite - Insert Data
- Python SQLite - Select Data
- Python SQLite - Where Clause
- Python SQLite - Order By
- Python SQLite - Update Table
- Python SQLite - Delete Data
- Python SQLite - Drop Table
- Python SQLite - Limit
- Python SQLite - Join
- Python SQLite - Cursor Object
- Python MongoDB
- Python MongoDB - Introduction
- Python MongoDB - Create Database
- Python MongoDB - Create Collection
- Python MongoDB - Insert Document
- Python MongoDB - Find
- Python MongoDB - Query
- Python MongoDB - Sort
- Python MongoDB - Delete Document
- Python MongoDB - Drop Collection
- Python MongoDB - Update
- Python MongoDB - Limit
- Python Data Access Resources
- Python Data Access - Quick Guide
- Python Data Access - Useful Resources
- Python Data Access - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Python MySQL - Database Connection
To connect with MySQL, (one way is to) open the MySQL command prompt in your system as shown below −
It asks for password here; you need to type the password you have set to the default user (root) at the time of installation.
Then a connection is established with MySQL displaying the following message −
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You can disconnect from the MySQL database any time using the exit command at mysql> prompt.
mysql> exit Bye
Establishing connection with MySQL using python
Before establishing connection to MySQL database using python, assume −
That we have created a database with name mydb.
We have created a table EMPLOYEE with columns FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
The credentials we are using to connect with MySQL are username: root, password: password.
You can establish a connection using the connect() constructor. This accepts username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class.
Example
Following is the example of connecting with MySQL database "mydb".
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Executing an MYSQL function using the execute() method
cursor.execute("SELECT DATABASE()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection established to: ",data)
#Closing the connection
conn.close()
Output
On executing, this script produces the following output −
D:\Python_MySQL>python EstablishCon.py
Connection established to: ('mydb',)
You can also establish connection to MySQL by passing credentials (user name, password, hostname, and database name) to connection.MySQLConnection() as shown below −
from mysql.connector import (connection) #establishing the connection conn = connection.MySQLConnection(user='root', password='password', host='127.0.0.1', database='mydb') #Closing the connection conn.close()