Python connecting to mysql

Question

I installed Python 2.7 to try to connect to MySQL online. Basically, MySQL and phpMyAdmin is on a server and I can access it via localhost:8888/phpmyadmin via putty on my windows desktop. I cant seem to connect to it even with the putty on. Any idea? I face the same issue with Python 3.3 using CyMySQL.

import MySQLdb

db = MySQLdb.connect(host="127.0.0.1", # your host, usually 127.0.0.1
                     user="megamonster", # your username
                     passwd="", # your password
                     db="extractor") # name of the data base

# you must create a Cursor object. It will let
#  you execute all the query you need
cur = db.cursor() 

# Use all the SQL you like
cur.execute("SELECT * FROM abc")

# print all the first cell of all the rows
for row in cur.fetchall() :
    print row[0]

Error:

Traceback (most recent call last):
  File "C:\Users\Jonathan\Desktop\testSQL.py", line 6, in <module>
    db="extractor") # name of the data base
  File "C:\Python27\lib\site-packages\MySQLdb\__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)")

Update

i added port(3306) and got this. OperationalError: (2013, "Lost connection to MySQL server at 'waiting for initial communication packet', system error: 0")

Currently looking at MySQL error: 2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 0"

Hmm cant work still...

can you connect with "mysql -umegamonster extractor" on the machines command line? — joeButler, Oct 19 '13 at 16:14
@kevinsa5 tried that already. I'm considering something like restful or some api stuff hmm what do you suggest? — CodeGuru, Oct 19 '13 at 16:17
@joeButler i have no issues in connecting to mysql from putty / browser(phpmyadmin) — CodeGuru, Oct 19 '13 at 16:17
@GamesBrainiac yea i just added it , new error and found a article on it , do you agree with http://stackoverflow.com/questions/3578147/mysql-error-2013-lost-connection-to-mysql-server-at-reading-initial-communic — CodeGuru, Oct 19 '13 at 16:21
@GamesBrainiac I'm using python 2.7 , Windows , trying to connect to a online phpmyadmin/mysql. I'm tunneled via putty. Hmm because i use mysql for my website :) — CodeGuru, Oct 19 '13 at 16:24

Rohan Gala · Accepted Answer · 2015-11-30T17:57:00.207

2

I used sqlplus it worked

sqlplus User_name/password@Host_ip:Port/Service_Name@$SQLFILENAME

Just specify the SQLFILENAME if you want to utilize a file. Otherwise, you can ignore this parameter and can directly run sql statements

edited Nov 30 '15 at 17:57

answered Nov 30 '15 at 17:36

Rohan Gala

641
5
18

Welcome to Stackoverflow. – CodeGuru Dec 01 '15 at 16:18

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

It could be a number of things, but as far as MySQL is concerned, permissions are set independently for localhost and for 127.0.0.1. Make sure you can connect with the exact host and credentials. Possibly related

For example, check this when connected with your PUTTY connection.

mysql> use mysql;
Database changed

mysql> SELECT host,user,select_priv FROM user;

+-------------------------+------+-------------+
| host                    | user | select_priv |
+-------------------------+------+-------------+
| localhost               | root | Y           |
| 127.0.0.1               | root | Y           |
+-------------------------+------+-------------+

Also check who you are connected as (on PUTTY) and use that same info in the script:

mysql> SELECT USER(),CURRENT_USER();

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

edited May 23 '17 at 12:09

Community

1
1

answered Oct 19 '13 at 16:31

beroe

11,784
5
34
79

I checked, Yes , Localhost and 127.0.0.1 is same as above. for current_user , same as your result as above. – CodeGuru Oct 19 '13 at 16:38
So you can ssh to the machine and then connect at the command line as `mysql -u root`? I don't understand the phpmyadmin "via Putty" reference... Nevermind -- I just saw the edit to your post that you have a new error! – beroe Oct 19 '13 at 16:41
yes. Thanks for helping me debug this , been saerching for hours. I appreciate your help >.< Still trying hard :x Hmmm – CodeGuru Oct 19 '13 at 16:44
I just dont understand why if i can talk to mysql via putty & browser http://localhost:8888/phpmyadmin why i cant do it on python :\ – CodeGuru Oct 19 '13 at 16:45
I'm so desperate , if this cant work , would u suggest something else? eg restful / api stuff ? i'm new to those anyway :\ – CodeGuru Oct 19 '13 at 16:45
In your script, try port 3306 instead of 8888? (Assuming you were using 8888 from your phpmyadmin statement). I think this question is basically finished because you have solved the problem originally asked, so we can't go off and start to debug another error in the comments. – beroe Oct 19 '13 at 16:56

Python connecting to mysql

2 Answers2