Python can't connect to MySQL

Question

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?

is python running on the same machine as PHP and your CLI-test? — Franz Gleichmann, Nov 25 '16 at 06:27

Januka samaranyake · Answer 1 · 2016-11-25T05:50:37.030

1

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

edited Nov 25 '16 at 05:50

answered Nov 25 '16 at 05:42

Januka samaranyake

2,385
1
28
50

I can't connect on localhost; "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)") – Charles Nov 25 '16 at 05:43
@Charles which means? – Januka samaranyake Nov 25 '16 at 05:46
I have to connect through another host, I named it "xxx" here. but it's different then localhost – Charles Nov 25 '16 at 05:47
then you have to indicate port ('xxx:3306') if port is open in your host. if you are using ssh you need to approach ssh implementation – Januka samaranyake Nov 25 '16 at 05:49
1

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'xxx:3306' ([Errno -2] Name or service not known)") I am using ssh should I add something to my code? – Charles Nov 25 '16 at 05:52
if you are using ssh. refer this http://stackoverflow.com/questions/9070708/ssh-first-with-mysqldb-in-python – Januka samaranyake Nov 25 '16 at 05:54
channel_setup_fwd_listener: cannot listen to port: 9990 – Charles Nov 25 '16 at 05:57

score 0 · Answer 2 · answered Nov 25 '16 at 06:01

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:

Ubuntu 14, Ubuntu 16, Debian 8.6 (jessie)

sudo apt-get install python-pip python-dev libmysqlclient-dev

Fedora 24:

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)

score 0 · Answer 3 · answered Nov 25 '16 at 06:09

0

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)
```

answered Nov 25 '16 at 06:09

Shubham R

7,382
18
53
119

Is there another way to get the socket location, because I don't have permission to run mysqladmin variables – Charles Nov 25 '16 at 06:13
@Charles [check this](http://www.tecmint.com/mysqladmin-commands-for-database-administration-in-linux/) i too have no idea as for now – Shubham R Nov 25 '16 at 06:19
@Charles why don't you use MySQLdb instead, its the best – Shubham R Nov 25 '16 at 06:20
Yeah, I'm trying to download it, but there's plenty of dependencies and I have a very limited access to this server (can barely use pip), but I don't see why it would change something? – Charles Nov 25 '16 at 06:22
@Charles , because, you can directly store your result table from sql in pandas dataframe. and i personally like pandas. Let me know if you're facing problems in installation. And upvote the answer if you find it useful as it is a common practice here. – Shubham R Nov 25 '16 at 06:31

Python can't connect to MySQL

3 Answers3

Ubuntu 14, Ubuntu 16, Debian 8.6 (jessie)

Fedora 24: