MYSQLdb Connection in Python

In this article, I have discussed how to connect to mysql database remotely using python. For any application it is very important to store database on a server for easy data access . It is quite complicated to connect to database remotely because every service provider don’t provide remote access to mysql database. Here I am using python’s MySQLdb module for connecting to our database which is at any server that provide 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 remote mysql database using python ?

Before we start you should know basics of SQL. Now let us discuss methods used in this code:

connect() : This method is used for creating a connection to our database it have four arguments:
1. Server Name
2. Database User Name
3. Database Provider
4. Database Name
cursor() : This method creates a cursor object that is capable for executing sql query on database.
execute() : This method is used for executing sql query on 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

'''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

# Python code to illustrate and create a  
# table in database  
import MySQLdb 
  
# Open database connection 
db = MySQLdb.connect("localhost","testuser","testpassword","gfgdb" ) 
  
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() 

Thanks to Shaurya Uppal for useful improvements.

My Personal Notes

Recommended Posts: