[FIXED] How to create a mysql database in Django on the first run?

Issue

I'd like my application to be "plug-and-play", so I need to automatically create the database on the first run. I use docker with docker-compose

My attempt is to connect without specifying the database name and run a custom command before running the server:

command:
  sh -c "python manage.py create_db &&
         python manage.py runserver 0.0.0.0:8000"

And the command itself:

class Command(BaseCommand):
"""Django command to create DB"""

def handle(self, *args, **options):
    con = connections['default']
    db_name = os.environ.get('DB_NAME')
    db_up = False
    while not db_up:
        try:
            cursor = con.cursor()
            cursor.execute(f'CREATE DATABASE IF NOT EXISTS {db_name}')
            cursor.execute(f'USE {db_name}')
            db_up = True
        except Exception as err:
            self.stdout.write('Database unavailable, waiting 1 second...')
            self.stdout.write(str(err))
            time.sleep(1)
    self.stdout.write(self.style.SUCCESS('Database available!'))

If this is the right way, then now I just need to update the connection to use the newly created database, but I don't know how. The line cursor.execute(f'USE {db_name}') of course doesn't work.

Is it the right way to create the database? If so, how to update the connection? If not, how to do it?

Thanks!

EDIT

After hints from Nealium, I created an independent script (not a Django command) which I run before running the server.

import os
import time
from MySQLdb import _mysql
import os


db_host=os.environ.get('DB_HOST')
db_user=os.environ.get('DB_USER')
db_password=os.environ.get('DB_PASS')
db_name = os.environ.get('DB_NAME')
db_up = False
while not db_up:
    try:
        db = _mysql.connect(
            host=db_host,
            user=db_user,
            password=db_password
        )
        db_up = True
        db.query(f'CREATE DATABASE IF NOT EXISTS {db_name}')
        db.close()
    except Exception as err:
        print('Database unavailable, waiting 1 second...')
        time.sleep(1)
print('Database available!')

Solution

This what my management command generally looks like

call_command() basically does python manage.py {command}

Updated dothing command

from django.core.management.base import BaseCommand
from django.core.management import call_command

def create_db():
  import mysql.connector
  try:
    mydb = mysql.connector.connect(
      host=os.environ.get('DB_HOST'),
      user=os.environ.get('DB_USER'),
      password=os.environ.get('DB_PASS')
    )

    mycursor = mydb.cursor()
    mycursor.execute('CREATE DATABASE IF NOT EXISTS {0}'.format(os.environ.get('DB_NAME')))
    return True
  except mysql.connector.Error as err:
    print('Something went wrong: {}'.format(err))
  except Exception as ex:
    message("Exception: {}".format(ex))
  return False

class Command(BaseCommand):
    help = 'does thing'

    def add_arguments(self, parser):
        # Named (optional) arguments
        parser.add_argument(
            '--import',
            action='store_true',
            help='Skips Import',
        )

    def handle(self, *args, **kwargs):
        print("Doing Thing")

        # connect to db + create if it doesn't exist
        status = create_db()

        if status:
          # create migrations
          call_command('makemigrations') # (Django Command)

          # can also pass arguemnts like a specific app
          # call_command('makemigrations', 'app1')

          # This create db **if** it doesn't exist
          #   + checks that migrations are up to date
          call_command('migrate') # (Django Command)

          if kwargs['import']:
              # another management command to handle importing
              #   I've just a csv reader and a loop
              call_command('importDb') # (Custom Command)

          # Collect Static (+ don't ask for confirmation)
          call_command('collectstatic', interactive=False) # (Django Command)

          print('Thing has been Done')
        else:
          print('Thing not Done')

So with that, I just run:
python manage.py dothing (python manage.py dothing --import if I want db to be imported)
and then:
python manage.py runserver and it's good to go!

---

Edit

Just do something like this and pull the options from the settings: https://www.w3schools.com/python/python_mysql_create_db.asp

---

Edit 2

It should work; From my testing Django doesn't actually connect to the db until its told to run a query (filter/get/create/delete)

You can generally test this with a basic management command and bad db settings:

1. Set db name in settings to 'invalid_db'
2. Test command below

from django.core.management.base import BaseCommand
from django.core.management import call_command
import os
class Command(BaseCommand):
    help = 'testing '

    def handle(self, *args, **kwargs):
        print('Command is Running!')
        print('doing the things')

        print('about to migrate / use db / crash')
        print('*'*100)
        call_command('migrate')

So in theory, as long as you aren't using Django commands you should be able to do whatever you want

Answered By - Nealium

Answer Checked By - Mary Flores (PHPFixing Volunteer)