# Django + PostgreSQL
###### tags: `Python` `PostgreSQL` `Django`
select * from pujblic.auth_user;
List tables in a PostgreSQL schema
### 零、參考文件
[How To Set Up Django with Postgres, Nginx, and Gunicorn on Ubuntu 18.04](https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-18-04)
[How To Use PostgreSQL with your Django Application on Ubuntu 14.04](https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application-on-ubuntu-14-04)
[How To Install the Django Web Framework on Ubuntu 18.04](https://www.digitalocean.com/community/tutorials/how-to-install-the-django-web-framework-on-ubuntu-18-04)
[PostgreSQL installation](https://tutorial-extensions.djangogirls.org/en/optional_postgresql_installation/)
[How to setup a Django Development Environment on Mac from scratch](https://medium.com/riow/how-to-setup-a-django-development-environment-on-mac-968d129bc661)
### 一、安裝PostgreSQL
環境 Python 3.6.8, Ubuntu 18.04
必要套件
```
$ sudo apt update
$ sudo apt install python3-pip python3-dev libpq-dev postgresql postgresql-contrib nginx curl
```
or
pip
```
$ sudo apt install python3-pip
```
PostgreSQL
```
$ sudo apt install postgresql postgresql-contrib
```
curl
```
$ sudo apt install curl
```
nginx (web server)
```
$ sudo apt install nginx
```
others
```
$ sudo apt install python3-dev libpq-dev
```
### 二、安裝 PostgreSQL package for Python
```
(venv) ~/djangogirls$ pip install psycopg2
```
這個目的是為了讓``python``可以連線使用 PostgreSQL
### 三、PostgreSQL 建立 database 與 and user
進入DB模式
```
$ sudo -i -u postgres
```
> under postgres=#
看 Databases:
```
postgres=# \l
```
看 Users:
```
postgres=# \du
```
看 Table
```
postgres=# \dt
```
查看 Table schema:
```
postgres=# select * from <table-name>;
postgres=# select * from public.<table-name>;
```
---
建立 Database:
```
postgres=# CREATE DATABASE project1;
```
建立 Database User:
```
postgres=# CREATE USER projectuser WITH PASSWORD 'password';
```
> We are setting the default encoding to UTF-8, which Django expects. We are also setting the default transaction isolation scheme to "read committed",
which blocks reads from uncommitted transactions. Lastly, we are setting the timezone. By default, our Django projects will be set to use UTC. These are all recommendations from the Django project itself:
```
postgres=# ALTER ROLE projectuser SET client_encoding TO 'utf8';
postgres=# ALTER ROLE projectuser SET default_transaction_isolation TO 'read committed';
postgres=# ALTER ROLE projectuser SET timezone TO 'UTC';
```
> Now, we can give our new user access to administer our new database:
開最大權限給新使用者
```
postgres=# GRANT ALL PRIVILEGES ON DATABASE project1 TO projectuser;
```
離開！
#### 刪除 Database:
[PostgreSQL DROP DATABASE](http://www.postgresqltutorial.com/postgresql-drop-database/)
```
postgres=# DROP DATABASE [IF EXISTS] name;
```
#### 刪除 Table
[PostgreSQL DROP TABLE](http://www.postgresqltutorial.com/postgresql-drop-table/)
```
postgres=# DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
```
In case the table that you want to remove is used in views,
constraints or any other objects,
the ``CASCADE`` allows you to remove those dependent objects
together with the table automatically.
``RESTRICT`` refuses to drop table if there is any object depends on it. PostgreSQL uses ``RESTRICT`` by default.
>``CASCADE``會把相依的Table 都刪除
``RESTRICT``是預設值-當有相依性存在時，會無法刪除指定的 Table
### 四、安裝 Django
```
(venv) ~/djangogirls$ pip install django
```
檢查版本：
```
(venv) ~/djangogirls$ python -m django --version
```
### 五、Django專案設定
#### 1. 閈新專案
假設新專案名稱為mytestsite
```
(venv) ~/djangogirls$ django-admin startproject mytestsite
```
開新 App
```
(venv) ~/djangogirls$ python manage.py startapp app1
```
##### 修改``setting.py``
###### 1. ``ALLOWED_HOSTS``
```
# The simplest case: just add the domain name(s) and IP addresses of your Django server
# ALLOWED_HOSTS = [ 'example.com', '203.0.113.5']
# To respond to 'example.com' and any subdomains, start the domain with a dot
# ALLOWED_HOSTS = ['.example.com', '203.0.113.5']
ALLOWED_HOSTS = ['your_server_domain_or_IP', 'second_domain_or_IP', . . ., 'localhost']
```
####### 2. ``DATABASES``
```
DATABASES = {
'default': {
#'ENGINE': 'django.db.backends.sqlite3',
#'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'myproject',
'USER': 'myprojectuser',
'PASSWORD': 'password',
'HOST': 'localhost',
'PORT': '',
}
}
```
##### 3. 建立 super user
```
(venv) ~/djangogirls$ python3 manage.py createsuperuser
```
會要輸入名稱、email、密碼
##### 4. collect all of the static content into
> We can collect all of the static content into the directory location we configured by typing:
```
(venv) ~/djangogirls$ python3 manage.py collectstatic
```
##### 5.
執行
```
python manage.py migrate
```
遇到：
>InconsistentMigrationHistory. Migration is applied before its dependency on database 'default'.
這是因為 Database 已經經過一連串的 Migrations，且裡頭已經有資料了，
導致後來有些情況會因 Table dependency 而有衝突！
> 解法是 Drop table 或使用新的 Database
-----
#### Testing the website framework
[Tutorial - Django Web Framework (Python)](https://developer.mozilla.org/en-US/docs/Learn/Server-side/Django)
At this point we have a complete skeleton project.
The website doesn't actually do anything yet,
but it's worth running it to make sure that none of our changes have broken anything.
Before we do that, we should first run a database migration.
This updates our database to include any models in our installed applications
(and removes some build warnings).
###### Running database migrations
Django uses an Object-Relational-Mapper (ORM)
to map model definitions in the Django code
to the data structure used by the underlying database.
As we change our model definitions,
Django tracks the changes and can create database migration scripts
(in /locallibrary/catalog/migrations/)
to automatically migrate the underlying data structure
in the database to match the model.
使用 Object-Relational-Mapper (ORM)
對應 Django code 到 database 的 data structure
Django 追蹤其變動，然後自動 migrate 到 database 中
When we created the website
Django automatically added a number of models
for use by the admin section of the site (which we'll look at later).
>每當新建立一個 Django 專案時，Django會自動加入一些 models
>來給 admin 頁面使用
Run the following commands to define tables for those models in the database
(make sure you are in the directory that contains manage.py):
```
(venv) ~/djangogirls$ python3 manage.py makemigrations
(venv) ~/djangogirls$ python3 manage.py migrate
```
You'll need to run the above commands every time your models change
in a way that will affect the structure of the data
that needs to be stored
(including both addition and removal of whole models and individual fields).
>每當model code有改變後，務必要執行``database migrations``，
>如此一來，database 內容才會更新
The ``makemigrations`` command creates (but does not apply)
the migrations for all applications installed in your project
(you can specify the application name as well
to just run a migration for a single project).
This gives you a chance to checkout the code for these migrations
before they are applied —
when you're a Django expert you may choose to tweak them slightly!
>``makemigrations`` 指令像是檢查程式碼，並做好準備
The ``migrate`` command actually applies the migrations to your database
(Django tracks which ones have been added to the current database).
> ``migrate`` 指令真的將改變套用到 database 上
-----
###### 從 cmd 證實 Django 新專案，在執行``database migrations``後的改變
假設 database 名稱為 ``djangoproject1``
```
$ psql -d djangoproject1
```
執行``database migrations``：
```
(venv) ~/djangogirls$ python3 manage.py makemigrations
(venv) ~/djangogirls$ python3 manage.py migrate
```
原本空的 database 會多了下面許多 tables:
```
djangoproject1# \dt
```
```
public | auth_group | table | project1user
public | auth_group_permissions | table | project1user
public | auth_permission | table | project1user
public | auth_user | table | project1user
public | auth_user_groups | table | project1user
public | auth_user_user_permissions | table | project1user
public | django_admin_log | table | project1user
public | django_content_type | table | project1user
public | django_migrations | table | project1user
public | django_session | table | project1user
```
觀察``auth_user`` table 的內容:
```
select * from public.auth_user;
```
```
id | password | last_login | is_superuser | username | first_name | last_name | email | is_staff | is_active | date_joined
----+--------------------------------------------------------------------------------+-------------------------------+--------------+----------+------------+-----------+-----------------------+----------+-----------+-------------------------------
1 | pbkdf2_sha256$100000$u8e7bbVWzpZM$tsxpWIDgxzONOLXfchTGnJdWH0llJc0OtT+mv/S9e3k= | 2019-03-22 17:50:30.247775+08 | t | sammy | | | sammy@goglobal.com.tw | t | t | 2019-03-22 16:00:49.198393+08
2 | pbkdf2_sha256$100000$6kn4B7a8aFkL$phjxHoQIhOVwxHYOkt8Jkm//3YzdxLPkZrp4PFULq0k= | | f | test1 | | |
```
Sign in with Wallet
Connect another wallet