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:

  1. 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
  2. cursor() : This method creates a cursor object that is capable for executing sql query on database.
  3. execute() : This method is used for executing sql query on database. It takes a sql query( as string) as an argument.
  4. 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.
  5. close() : This method close the database connection.

Free remote mysql database providers:
1.www.freemysqlhosting.net
2.www.heliohost.org

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


Connected
Today's Date Is  2017-11-14
filter_none

edit
close

play_arrow

link
brightness_4
code

# 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()

chevron_right


Thanks to Shaurya Uppal for useful improvements.




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

3


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.