I'm trying to connect to a database on a Linux server, everything works fine with PHP using:
$mysqli= new mysqli($_DBHOST, $_DBUSER, $_DBPASS, $_DB);
I can also connect using the command line with:
mysql -u xxx -p
But when I try to connect with Python using pymysql I get the following error:
pymysql.err.InternalError: (1130, "Host 'xxx' is not allowed to connect to
this MariaDB server")
Python code:
import pymysql
conn = pymysql.connect(host="xxx",user='xxx', passwd='xxx', db = 'xxx',
,port=3306, autocommit=True)
How can this even be possible? It cannot be a permission issue since PHP can connect fine, or can it?
Try this
try:
import mysql.connector
mysql_connector = mysql.connector
except:
import pymysql
mysql_connector = pymysql
mysql_connector.connect(user='root', password='root', host='xxx:3306', database='test')
the best you can do is use MySQLdb (case sensitive) it's the best package. and then you can directly import your table in pandas dataframe.
It's easy to do, but hard to remember the correct spelling:
pip install MySQL-python
Note: Some dependencies might have to be in place when running the above command. Some hints on how to install these on various platforms:
sudo apt-get install python-pip python-dev libmysqlclient-dev
sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
Using MySQLdb
import MySQLdb
conn = MySQLdb.connect(host="xxx.xxx.xx.x", user="name", passwd="password", db="database_name")
cursor = db.cursor()
import pandas as pd
df = pd.read_sql("select * from your_table",conn)
Two guesses:
Run mysqladmin variables | grep socket to get where the socket is located, and try setting up a connection like so:
pymysql.connect(db='base', user='root', passwd='pwd', unix_socket="/tmp/mysql.sock")
Run mysqladmin variables | grep port and verify that the port is 3306. If not, you can set the port manually like so:
pymysql.connect(db='base', user='root', passwd='pwd', host='localhost', port=XXXX)