Forums

Settings mySQL database in a Django app

Hi! I just wanted to know how you configure the DATABASE part of settings.py to work with the free mySQL database that we are provided. Any help would be appreciated in this matter. So far, I've been able to do with sqlite, but I want to learn how to work with MySQL, so the a short tutorial on how to handle the configurations would be awesome.

This is my current configuration:

import os

DEBUG = True
TEMPLATE_DEBUG = DEBUG


def replace(path):
    assert isinstance(path, str)
    return path.replace('\\', os.sep)


def here(*args):
    return replace(os.path.abspath(os.path.join(os.path.dirname(__file__), *args)))


BASE_DIR = here('..')


def root(*args):
    return replace(os.path.abspath(os.path.join(BASE_DIR, *args)))

ADMINS = (
    # ('Your Name', 'your_email@example.com'),
)

MANAGERS = ADMINS

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',  # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': root('myblog.db'),                      # Or path to database file if using sqlite3.
        # The following settings are not used with sqlite3:
        'USER': '',
        'PASSWORD': '',
        'HOST': '',                      # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
        'PORT': '',                      # Set to empty string for default.
    }
}

I'm no Django expert, so hopefully someone more familiar than I can comment, but in the interests of getting you a response quickly it looks like all you need is some appropriate configuration in the DATABASES section.

I'm guessing you change ENGINE to django.db.backends.mysql and NAME to the database name that you can find on your Dashboard -> Databases page (probably gamesbrainiac$default at a guess). Set USER to whatever is shown on the same page for "Username" (probably your standard username, but I'm not sure if there are cases where it differs) and PASSWORD to whatever you've set as your database password on that page (I would definitely recommend setting a password if you haven't already).

HOST should be set to mysql.server and PORT can probably be left empty as the default should be fine.

Let us know if that works for you - otherwise we'll have to wait until a Django expert responds. (^_^)

[edit by admin: see jgmdavies' comment about the hostname further on in this thread]

what Cartroo said!

Thanks to the both of you guys! :)

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': '<your_username>$<your_database_name>',
'USER': '<your_username>',
'PASSWORD': '<your_mysql_password>',
'HOST': '<your_mysql_hostname>',
}
}

And these days the Host should be username.mysql.pythonanywhere-services.com, where 'username' is your PA username. The old mysql.server is deprecated.

I'm sorry, but I really am a newbie to mysql. When I try to migrate I get the following error:

django.db.utils.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

could someone please help me?

Ok I figured it out my config.cnf file wasn't working I put the settings directly in settings.py and now it works!

Excellent, glad you worked it out!

I have one row in one table: mysql> select * from cw_operator; +------+---------------+-------------+ | id | name | slug | +------+---------------+-------------+ | 9052 | BP ENERGY INC | bpenergyinc | +------+---------------+-------------+ 1 row in set (0.01 sec)

mysql> \s

mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper Connection id: 459471460 Current database: magula$mydb2 Current user: magula@10.0.0.220 SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.40-log Source distribution Protocol version: 10 Connection: magula.mysql.pythonanywhere-services.com via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 +------+---------------+-------------+ Uptime: 257 days 2 hours 52 min 54 sec

Threads: 82 Questions: 12367507308 Slow queries: 342586 Opens: 112711838 Flush tables: 1 Open tables: 992 Queries per second avg: 556.714


my Django settings: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'magula$mydb2', #database name on PA Databases page 'USER': 'magula', #username on PA Databases page 'HOST': 'magula.mysql.pythonanywhere-services.com', 'PORT': '3306', } }


but the query in Django pukes: (virtualenv) $ ./manage.py shell In [6]: from cw.models import Operator In [7]: what=Operator.objects.all() In [8]: what OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (111)")

Those Django settings are not the ones that are being used. They look correct but your manage.py shell is not using them. In particular, the HOST from the DATABASES entry is correct, but Django is not using it. Django is using a DATABASES setting where HOST is either not set or where it's set to 127.0.0.1.

Hello, please can someone help me level up when I try to migrate. Here is the error: django.db.utils.OperationalError: (1045, "Access denied for user 'halltech'@'10.0.0.32' (using password: YES)")

Is your db name correct? It should be something like halltech$whatever

Yes the name of my database is correct: halltech $ App_shop

halltech$App_shop

If the database name is correct, make sure that the password you're using matches the one in ~/.my.cnf

How to check please?

Look in your .my.cnf file in the home directory of your account.

OK thank you, it works

Great, thanks for letting us know.

Hi, I'm encountering the below error -

cannot import name 'Connection' from 'MySQLdb.connections' (/home/VijayKumarABP/.virtualenvs/summarizevenv/lib/python3.9/site-packages/MySQLdb/connections.py)

Please let me know a solution for the same,

That looks like MySQLdb may not be properly installed. Try re-installing it.

Could you let me know the exact package name?

It's https://pypi.org/project/mysqlclient/

See https://help.pythonanywhere.com/pages/UsingMySQL/