Sunday, April 27, 2014

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!

1 comment:



  1. Tay trái lão giả vũng ra, hoàng mang chói mắt oanh kích ở trên người trung niên hán tử, tiếp theo sau trung niên hán tử máu tươi phun ra, cả người ngã xuống cái khe, đồng thời trữ vật giới chỉ hướng về phía lão giả bay tới.

    Cái khe dưới đất biến mất, cùng lúc đó khôi phục yên tĩnh, cửu tinh Đấu Tôn trung niên hán tử cũng bị mai táng ở dưới đất, một cửu tinh Đấu Tôn cứ như thế biến mất khỏi thế gian này.
    dongtam
    mu moi ra hom nay
    tim phong tro
    http://nhatroso.com/
    nhac san cuc manh
    tổng đài tư vấn luật
    http://dichvu.tuvanphapluattructuyen.com/
    văn phòng luật hà nội
    tổng đài tư vấn luật
    thành lập công ty trọn gói
    http://we-cooking.com/
    chém gió
    trung tâm ngoại ngữ
    - Xoẹt.

    Rơi vào trong tay lão giả là một trữ vật giới chỉ, lão giả áo xanh lộ một nụ cười.

    - Giết người đoạt vật, Triệu Tuệ Quang ngươi cũng có hảo thủ đoạn, không biết bảo vật gì, cho ta xem để mở nhãn giới nào.

    Cùng lúc đó một đạo thanh âm âm lãnh truyền ra, sau đó năm đạo thân ảnh bao vêy áo lão giả xanh ở bên trong.

    Những người mới tới là ba lão giả cùng hai trung niên hán tử, khí tức trên thân năm người

    ReplyDelete