Connect to a remote mysql database using Python and MySQLdb

remote_db_connector.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

############################################################
#                                                          #
# Simple script to connect to a remote mysql database      #
#                                                          #
#                                                          #
# Install MySQLdb package by running:                      #
#                                                          #
#                       pip install MySQL-python           #
#                                                          #
############################################################


import MySQLdb as db

HOST = "remote host"
PORT = 3306
USER = "username of remote mysql instance"
PASSWORD = "password of remote mysql instance"
DB = "database name"

try:
    connection = db.Connection(host=HOST, port=PORT,
                               user=USER, passwd=PASSWORD, db=DB)

    dbhandler = connection.cursor()
    dbhandler.execute("SELECT * from your_table")
    result = dbhandler.fetchall()
    for item in result:
        print item

except Exception as e:
    print e

finally:
    connection.close()

Hi Kiran,
I'm trying to connect to a host 191.247.228.16 and I'm receiving the error below. Do you have any idea what is my mistake?

_mysql_exceptions.OperationalError: (1130, "Host '191.247.228.16' is not allowed to connect to this MariaDB server")

I suppose to have remote access to the database. I have my user allowed in the phpmyadmin with all permissions.

Thanks in advanced.

@criskubiaki By default db server can only be accessed through localhost. Search for 'bind-address = 127.0.0.1' in /etc/mysql/my.cnf file. Either comment out the line or delete it or make it 'bind-address = 0.0.0.0'. Retry Connecting...

I'm getting a error 2003 Cannot connect to the mysql server with ip xxx.xx.x.x (110) , I have made the bind address as 0.0.0.0, But still I'm not able to access the remote mysql server. when i'm trying to access with in a local host i'm able to do successfully, Thanks in advance

Thanks man, works perfectly

I am working with python 3 and I am not able to install mysqldb. Later I found MySQldb doesn't support Python 3.

Now I am using MySQL connector Python to connect MySQL.

I found following article helpful.

https://pynative.com/python-mysql-database-connection/

Thanks @ault2910, that link did the trick for me.