CODEWITHSUNDEEP
pythonmysql
Nikhil Gupta
Nikhil Gupta

Reputation: 561

python-mysql cursor.execute failing with access denied error

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?

Upvotes: 1

Views: 1065

Answers (2)

Nikhil Gupta
Nikhil Gupta

Reputation: 561

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

  1. 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';
  2. change LOAD DATA INFILE '{}' in sritest.sql to LOAD DATA LOCAL INFILE '{}'
  3. 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.

Upvotes: 0

eaydin
eaydin

Reputation: 3262

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

Upvotes: 1

Related Questions