Cant connect to database pymysql using a .my.cnf file

Question

This function should connect to database because I've used an exception I'll get my own created message, when incorrect input is found. But when I remove try and except I get : "Acces denied for /'user/'@'/'localhost' (using password : NO).

It seems that it doesnt read my password I have no clue why a little help will be appreciated. Without this file connection to the database works fine without any errors.

    try:
        self.conn = pymysql.connect(read_default_file="~/my.cnf")
        self.curr = self.conn.cursor()

    except pymysql.err.OperationalError : 
        sys.exit("Invalid Input: Wrong username/database or password found, please try again")

Information found in the .my.cnf file is :

[client]

host = 'localhost'

port = 3306

user = myusername

password = "mypassword"

Answer 1

First off, can you connect to your database using the following command.

import pymysql
conn = pymysql.connect(host='localhost',
    port=3306,
    user='myusername',
    passwd='mypasswd')

If that doesn't work, then you might have some other problem (for instance, your database may be configured to only connect via socket for local clients)

As for the config file, I think this will work if you remove the quotation marks, like this:

[client]
host = localhost
port = 3306
user = myusername
password = mypassword

I saved your config file as test.cnf and ran the following code

# test.cnf    
[client]
host = 'localhost'
port = 3306
user = myusername
password = "mypassword"

$ python3
>>> import configparser
>>> reader = configparser.RawConfigParser()
>>> reader.read('test.cnf')
>>> reader.get('client', 'host')
"'localhost'"
>>> reader.get('client', 'user')
'myusername'

As you can see, the config parser is treating quotes as part of the value.

Update: Workaround

The OP mentioned the provided solution (i.e. removal of quotes did not solve the problem he was facing). Here's a workaround that achieves separation of configuration / connection properties from program logic.

Save the configuration in a python file called dbsettings.py.

# dbsettings.py  
connection_properties = {
    'host': 'localhost',
    'port': 3306,
    'user': 'myusername',
    'passwd': 'mypassword'
    }

And then in your main program use the following lines for setting up the connection.

try:
    from dbsettings import connection_properties
    self.conn = pymysql.connect(**connection_properties)
    self.curr = self.conn.cursor()
except pymysql.err.OperationalError : 
    sys.exit("Invalid Input: Wrong username/database or password found, please try again")

If your entire program is written in python, then this allows the same separation of connection / config info from program logic as using the my.cnf method, and it is just as flexible if not mroe so. However, if other non python scripts need to reference the my.cnf file, then you'll have to maintain two separate mysql config files.

Answer 2

You used:

pymysql.connect(read_default_file="~/my.cnf")

You should have used:

pymysql.connect(read_default_file='~/.my.cnf')
-------------------------------------^

Note the missing dot in the filename. You were just loading a different or non existent file.

The ~ is being expanded as shown here:

https://github.com/PyMySQL/PyMySQL/blob/18b62f6e1d6f65b403c9e8b650f4c3bb27b665e7/pymysql/connections.py#L619

Also I can confirm that no quotes or spaces are required in the .my.cnf file:

echo -e "[client]\nuser=root\npassword=defaultrootpwd" > ~/.my.cnf