MYSQLdb Connection in Python
Last Updated :
23 Nov, 2021
In this article, I have discussed how to connect to MySQL database remotely using python. For any application, it is very important to store the database on a server for easy data access. It is quite complicated to connect to the database remotely because every service provider doesn't provide remote access to the MySQL database. Here I am using python's MySQLdb module for connecting to our database which is at any server that provides remote access.
What is MYSQLdb?
MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.
Packages to Install
mysql-connector-python
mysql-python
If using anaconda
conda install -c anaconda mysql-python
conda install -c anaconda mysql-connector-python
else
pip install MySQL-python
pip install MySQL-python-connector
Import-Package
import MYSQLdb
How to connect to a remote MySQL database using python?
Before we start you should know the basics of SQL. Now let us discuss the methods used in this code:
- connect(): This method is used for creating a connection to our database it has four arguments:
- Server Name
- Database User Name
- Database Password
- Database Name
- cursor(): This method creates a cursor object that is capable of executing SQL queries on the database.
- execute(): This method is used for executing SQL queries on the database. It takes a sql query( as string) as an argument.
- fetchone(): This method retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available.
- close() : This method close the database connection.
Free remote mysql database providers:
1.www.freemysqlhosting.net
2.www.heliohost.org
Python3
'''This code would not be run on geeksforgeeks IDE
because required module
are not installed on IDE. Also this code requires
a remote MySQL databaseconnection with valid
Hostname, Dbusername Password and Dbname'''
# Module For Connecting To MySQL database
import MySQLdb
# Function for connecting to MySQL database
def mysqlconnect():
#Trying to connect
try:
db_connection= MySQLdb.connect
("Hostname","dbusername","password","dbname")
# If connection is not successful
except:
print("Can't connect to database")
return 0
# If Connection Is Successful
print("Connected")
# Making Cursor Object For Query Execution
cursor=db_connection.cursor()
# Executing Query
cursor.execute("SELECT CURDATE();")
# Above Query Gives Us The Current Date
# Fetching Data
m = cursor.fetchone()
# Printing Result Of Above
print("Today's Date Is ",m[0])
# Closing Database Connection
db_connection.close()
# Function Call For Connecting To Our Database
mysqlconnect()
Connected
Today's Date Is 2017-11-14
Python3
# Python code to illustrate and create a
# table in database
import mysql.connector as mysql
# Open database connection
db = mysql.connect(host="localhost",user="root",password="tiger",database="python")
cursor = db.cursor()
# Drop table if it already exist using execute()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Create table as per requirement
sql = "CREATE TABLE EMPLOYEE ( FNAME CHAR(20) NOT NULL, LNAME CHAR(20), AGE INT )"
cursor.execute(sql) #table created
# disconnect from server
db.close()
Output:
Similar Reads
MySQL-Connector-Python module in Python MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify
2 min read
Python MySQL - Join JOIN clause is used to combine rows from two or more tables based on a related column. It allows you to query data from multiple tables and return the result as a single set. There are several types of joins in SQL, each providing different results based on how data is combined.JOINTables used in th
3 min read
Connect to MySQL using PyMySQL in Python In this article, we will discuss how to connect to the MySQL database remotely or locally using Python. In below process, we will use PyMySQL module of Python to connect our database. What is PyMySQL? This package contains a pure-Python MySQL client library, based on PEP 249. Requirements : MySQL Se
2 min read
How to install MySQL Connector Package in Python MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. A connector is employed when we have to use MySQL with other pro
2 min read
Python MySQL - Insert into Table MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify
3 min read