Skip to content

converting_databases

mitt4git edited this page Mar 20, 2017 · 22 revisions

#Converting Database Schema: From Access to Django Audience: Developers looking to recreate this process for new databases or due to bugs discovered down the line necessitating starting over again

What follows is the process used to generate the Django schema (models.py) starting with the supplied Access 2010 database file.

##Install and Configure MySQL

  • #Where to get it# Best way to install is downloading the 32-bit installer. https://dev.mysql.com/downloads/installer/
  • The file downloaded will be "mysql-installer-web-community-5.7.17.0". Double click or right click install. This will open an installer where you would choose custom option and then select all 32 bit (x86) versions of MySQl server, Workbench 6.3 , MySQL connector/ODBC 5.3. Click next and select folder in program Files (x86).
  • #how to run it# Click next again and make sure everything you need is selected. Click execute.
  • This will be followed by a windows which let you setup a server. Let everything set to defualt and give a password when prompted. Keep this password written and safe. (This will be used later to connect to Access / Django). Pres Execute again for final installation.
  • #any possible confusing or custom steps for this process#

Once installed, open workbench.

  • (confusing step 1...)

##Connect to Access Database##

Very good webpage for visual steps http://mysqlworkbench.org/2014/08/how-to-guide-to-database-migration-from-ms-access-using-mysql-workbench/

  • #How to point MySQL at Access DB File# Go to database migration wizard in workbench. Click open ODBC admin at bottom and add a user dsn with MS access .mdb/.accdb link. (This link with only show up if you have same Access version - 32bit on the machine)

  • After establishing ODBC, click on migrate button and bottom left of workbench. Input all connection parameters including .accdb ethno file location, MySQL DSN, username and password.

  • There will be option of Access password as well in properties which can be added.

  • Fill out all necessary paraetrs in Object migration ta(source, migration, create schema, create target. In create schema, one will create a blank schema and name it the same as Access.accdb name (users choice).

  • Click next and okay and data migration will start. It may ask for few permissions and privileges intermittently. Click okay and next in all prompts.

  • If everything is entered correctly, the migration will show success (few errors came because of connections issues at this point but was corrected after ODBC connection was established)

  • One can now see the ethno.db schema at bottom left (or wherever your schema window is in workbench). This db has all tables and schema in it. One can now generate .sql script from this to create sql dump of this database. This .sql script will help in generating this database in future quickly from code window.

  • (List of dependencies and versions that need to be installed to the system (non-python packages, but DOES include python)) ##Install Django and Dependencies##

  • Django can be installed after installing python and all packages through command prompt using pip.

  • First install latetst python (3.6.0 at the time this was written) through this website. https://www.python.org/downloads/ (make sure to click Add "python to path" when installing)

  • pip is a package manage for Python. We can then install python packages from command line using python -m pip install -U pip pip install virtualenv *create virtual environment for your project virtualenv env_foldername *to activate your new virtual environment- env_mysite\scripts\activatewithin your new folder. *Now one can install Django in this environment pip install django==1.10.6 (latest at this time)

  • (List of dependencies and versions that need to be installed to the system (non-python packages, but DOES include python))

##Setting up database## *Open visual studio and open a blank Django project. Save it in a folder you recognize. *There is another way to create a Django project which is through command line. Once in your environment virtualenv env_foldername', type in django-admin startproject DjProject. This will create a Django project and files within DjProject in virtual environment. These are __init__.py , settings.py, urls.py, wsgi.py . *Remember, anytime you run a virtual environment, it first needs to activated, keep in mind the folder where it is located. Then run on command prompt env_folername\scripts\activate` (where foldername is the folder name you created)

  • (List of python packages to be installed to your virtual environment)* The python packages will be installed in this folder (DjProject)

##Generating your Django Schema##

  • (create new Django project) We already did that in previous step. DjProject is our django project with python packages inside it.
  • (Django settings for MySQL backend) Open settings.py in notepad++ or any pther editor. Setup the database command as shown below (strike out the sqlite command which is default). Make sure the database schema name created in MySQL is the same here. --- (#"ethnographic_elements_be"-- you db name in MySQL), (#"your mysql passwordpassword" --you have for mysql root) DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'ethnographic_elements_be', 'USER': 'root', 'PASSWORD': 'your mysql password', 'HOST': 'localhost', 'PORT': '3306', } }

*Change time zone to PST in settings.py (default is UMT)

  • When all parameters are set in 'settings.py' file. Go back to command prompt. Navigate to folder DjProject and then type this command which will connect to mysql and read all tables. (Make sure to deactivate the virtual environment if active) python manage.py inspectdb

*To generate models.py file, save this command line into a models.py python manage.py inpectdb>models.py

This will save "models.py" file in DjProject folder!

Clone this wiki locally