Connect to a database

If you are connecting to a remote database, create the database with your remote host first.  Go to the cpanel or whatever you have, and add a new PostgreSQL database (In this example, I will be using PostgreSQL).  Every hosting account is probably different, so I'm not going to bother with the specifics.

Added the database “rebates”.

I like to also use a database management tool locally. So, at this stage, I like to add my IP address so that I can connect locally to my remote database server. (This is fairly standard for authentication with most hosting sites, I believe). Generally, you will need a specific username, password, and database at this point for authentication purposes.

Added:  rebates with user admin from XX.XXX.XXX.0/24

Now, locally I open up my database management tool (I'm using SQL Manager Lite for PostgreSQL) and register my newly created database.

Then, simply connect to the newly registered database.  Success!

Great-- but that has nothing to do with our django app. Let's configure the database in the settings.py, which exists in the project directory file. Our project is called "rebates". Inside the "rebates" directory, there is a project specific directory (automatically created by django when you start the project),which also has the same name of "rebates".   Inside this directory, you will find your settings.py file. I realize this is a little confusing.

The tree looks like this, where:

rebates is the project and
rebate is the app

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> tree /f
Folder PATH listing for volume OS
Volume serial number is BA99-C83E
C:.
¦   manage.py
¦
+---rebate
¦       admin.py
¦       models.py
¦       tests.py
¦       views.py
¦       __init__.py
¦
+---rebates
        settings.py
        settings.pyc
        urls.py
        wsgi.py
        __init__.py
        __init__.pyc

I like to use notepad++ as my default editor.  I'm going to open my settings.py file with notepad++

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> start notepad++ rebates\settings.py

Inside settings.py, there is a default area for DATABASE which looks like this.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'mydatabase',
    }
}

You can find out more about these configurations for each type of database inside the django documentation:

https://docs.djangoproject.com/en/1.6/ref/settings/#databases

Let's configure our database with the remote PostgreSQL database

DATABASES = {
    'default': {
        'ENGINE':'django.db.backends.postgresql_psycopg2',
        'NAME': 'jaytarle_rebates',
        'USER': 'jaytarle_admin',
        'PASSWORD': 'XXXXXXXXXXXX',
        'HOST': 'jaytarlecki.com',
        'PORT': '5432',
    }
}

That should do the trick for the database, but before we close the settings.py file let's add rebate to our list of INSTALLED_APPS

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'rebate',
)

Now that we have linked up the rebate app to our django project, let's go into the rebate directory and launch the models.py file:

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> start notepad++ rebate\models.py

Time to create our database model! So, I already have an idea of how I want to structure my first test table, which is called zipcodes.  As you may be able to guess, its a table of zipcodes and other associated info.  I already have the data in another database.  For the sake of my own sanity, I'm just going to copy the CREATE statement here, so I can translate it into the django database model language, as needed.

In PostgreSQL, our zipcodes table:

CREATE TABLE zipcodes (
  id SERIAL,
  zipcode VARCHAR(5) NOT NULL,
  state CHAR(2) NOT NULL,
  city VARCHAR(28) NOT NULL,
  type CHAR(1) NOT NULL,
  countyfips CHAR(5) NOT NULL,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  areacode CHAR(3) NOT NULL,
  financecode CHAR(6) NOT NULL,
  lastline CHAR(4),
  fac CHAR(1),
  msa CHAR(4),
  pmsa CHAR(4),
  modifieduser VARCHAR(128) NOT NULL,
  modifieddate TIMESTAMP WITHOUT TIME ZONE NOT NULL
) 

So, we can skip the id field, as django's backend will handle that for us.

from django.db import models

# Create your models here.
class Zipcodes(models.Model):
 zipcode = models.CharField(max_length=5)
 state = models.CharField(max_length=2)
 city = models.CharField(max_length=100)
 type = models.CharField(max_length=1)
 countyfips = models.CharField(max_length=5)
 latitude = models.FloatField()
 longitude = models.FloatField()
 areacode = models.CharField(max_length=3)
 financecode = models.CharField(max_length=6)
 lastline = models.CharField(max_length=4, null=True)
 fac = models.CharField(max_length=1, null=True)
 msa = models.CharField(max_length=4, null=True)
 pmsa = models.CharField(max_length=4, null=True)
 modifieduser = models.CharField(max_length=128, default='admin')
 modifieddate = models.DateField(auto_now=True)

 def __unicode__(self):
  return self.zipcode

Inside a virtual environment, you will need to get the windows binary that allows proper syncing with a postgre database (don't ask, just do it)

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> easy_install http://www.stickpeople.com/projects/python/win-psycopg/psycopg2-2.4.win32-pyx.x-pg9.0.3-release.exe

Now, create the database by syncing it in powershell

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> python manage.py syncdb

Create a superuser if prompted, and viola! You have just synced the database. Django will create a whole slew of tables which are very useful, but I won't explain in this post.

What we care about right now is the zipcodes table. Refresh your local rebates database inside your SQL Manager and lets see how we did.  Let's script out a CREATE TABLE statement and view how our model in Django created the table in PostgreSQL

CREATE TABLE public.rebate_zipcodes (
  id SERIAL,
  zipcode VARCHAR(5) NOT NULL,
  state VARCHAR(2) NOT NULL,
  city VARCHAR(100) NOT NULL,
  type VARCHAR(1) NOT NULL,
  countyfips VARCHAR(5) NOT NULL,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  areacode VARCHAR(3) NOT NULL,
  financecode VARCHAR(6) NOT NULL,
  lastline VARCHAR(4),
  fac VARCHAR(1),
  msa VARCHAR(4),
  pmsa VARCHAR(4),
  modifieduser VARCHAR(128) NOT NULL,
  modifieddate DATE NOT NULL,
  CONSTRAINT rebate_zipcodes_pkey PRIMARY KEY(id)
) 
WITH (oids = false);

Not too shabby... Looks just like the table we used as our template. Its time to start building out that model!