python-mysql cursor.execute failing with access denied error

Question

I have two machines: local_machine, server_machine. I have mysql server on server_machine and sftp server on local_machine. I am trying to send sritest.csv file (UTF-8) from local_machine to server_machine using python. These are the contents of sritest.csv: 1,2,3

I have the sql query saved in sritest.sql and these are the contents of the file:

LOAD DATA INFILE '{}'
INTO TABLE TESTBED_STAGING.test
COLUMNS TERMINATED BY ','
;

This is the python script I have now:

import MySQLdb
import os
import string

# Open database connection
db = MySQLdb.connect (host="1.2.3.4",port=3306,user="app_1",\
                      passwd="passwd",db="TESTBED_STAGING")

cursor=db.cursor()
#Query under testing
sql = open('sritest.sql','r').read()
print sql
l = os.listdir(".")
for file_name in l:
    if file_name.endswith('sritest.csv'):
        print 'the csv file we are reading is: '+file_name
        #try:
        cursor = db.cursor()
        print 'filename is '+sql.format(file_name)
        cursor.execute(sql.format(file_name))
        db.commit()
        '''
        except Exception:
            # Rollback in case there is any error
            db.rollback()
            print 'ERROR - So, rollback :( :( '
        '''
# disconnect from server
db.close()

In the above script, I commented try,except so I can see the error where it breaks. Currently the code is breaking at cursor.execute(sql.format(file_name)) line with this error:

OperationalError: (1045, "Access denied for user 'app_1'@'%' (using password: YES)")

I have been playing around but not able to fix it. Any suggestions/ideas?

eaydin · Accepted Answer · 2015-12-23 18:41:59Z

1

For starters, creating cursor at every loop is not a good idea. You've already created a cursor earlier, so you can remove the cursor declaration in the for loop.

Second, I think your error is due to lack of access on MySQL server at 1.2.3.4 remotely using user app_1. Try this on the server's MySQL console,

GRANT ALL PRIVILEGES ON TESTBED_STAGING.* TO 'app_1'@'%';

Lastly, try and avoid using print "line" notation and start switching to the print("line") notation for compatibility with Python 3.x

answered Dec 23, 2015 at 18:41

eaydin

3,4812 gold badges21 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikhil Gupta Over a year ago

app_1 user already has all the privileges on the MySQL server (I mean I ran the above command already).

Nikhil Gupta · Accepted Answer · 2015-12-23 20:28:53Z

0

I figured out the answer and decided to leave this question open for those who might face the similar problem:

In the MySQL server (server_machine), make sure you do this after you start mysql: mysql>grant all privileges on *.* to 'app_1'@'%' identified by 'passwd';
change LOAD DATA INFILE '{}' in sritest.sql to LOAD DATA LOCAL INFILE '{}'
In the python code, edit the MySQLdb.connect statement as: db = MySQLdb.connect (host="1.2.3.4",port=3306,user="app_1",\ passwd="passwd",db="TESTBED_STAGING", local_infile=1)

All errors are eliminated and data is transferred.

answered Dec 23, 2015 at 20:28

Nikhil Gupta

5611 gold badge10 silver badges30 bronze badges

Collectives™ on Stack Overflow

python-mysql cursor.execute failing with access denied error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related