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 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 8 Server version: 8.0.43 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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 - Connecting to MySQL Database
Following is the example of connecting with MySQL database "mydb".
main.py
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 −
(myenv) D:\Projects\python\myenv>py main.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()