[Django] 04 - Connect to RDS PostgreSQL
故事背景
Ref: Amazon RDS for PostgreSQL
一、本地ORM访问PostgreSQL
本地时，如何访问的 PostgreSQL，参见：[Django] 02 - Django REST Framework (DRF)
首先，在 .env.dev 设置sql的相关环境变量。
然后，Django 读取环境变量，设置 settings.py，包括：host, port设置。
-
Psycopg
Goto: https://www.psycopg.org/
Psycopg is the most popular PostgreSQL adapter for the Python programming language. Its core is a complete implementation of the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.
-
pgAdmin
PostgreSQL Tools.
pgAdmin is the most popular and feature-rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
二、PostgreSQL
Ref: PostgreSQL 教程
Ref: 如何在 Ubuntu 18.04 上安装 pgAdmin4【图形界面】
三、AWS RDS
Goto: Amazon RDS for PostgreSQL
Ref: Create An AWS Aurora PostgreSQL Database and Connect Using PgAdmin【创建好数据库并查看】
Ref: Postgres Database + AWS RDS | Django (3.0) Crash Course Tutorials (pt 21)【如何通过Django操作数据库】
数据库操作：本地
一、本地安装 postgresql
Ref: How To Install and Use PostgreSQL on Ubuntu 18.04【安装细节】
系统先添加一个用户，然后切换到该用户，进入psql。
sudo adduser sammy sudo -i -u sammy psql
二、创建 database
$ createdb sammy
$ psql -d sammy psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1)) Type "help" for help. sammy=# \conninfo You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".
三、添加 table
表：playground
CREATE TABLE playground ( equip_id serial PRIMARY KEY, type varchar (50) NOT NULL, color varchar (25) NOT NULL, location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')), install_date date );
查看。
sammy=# \dt List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row)
sammy=# \d List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)
四、添加内容
sammy=# INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28'); INSERT 0 1
sammy=# INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16'); INSERT 0 1
sammy=# \d List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)
sammy=# \dt List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row) sammy=# SELECT * FROM playground; equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2017-04-28 2 | swing | yellow | northwest | 2018-08-16 (2 rows)
数据库操作：RDS
Ref: Create An AWS Aurora PostgreSQL Database and Connect Using PgAdmin
一、数据库链接
[文件 .env.dev]
DEBUG=1 SECRET_KEY=foo DJANGO_ALLOWED_HOSTS=localhost 127.0.0.1 0.0.0.0 [::1] music-instance-1.xxxxdatogdsq.eu-west-2.rds.amazonaws.com SQL_ENGINE=django.db.backends.postgresql SQL_DATABASE=postgres SQL_USER=postgres SQL_PASSWORD=12345678 SQL_HOST=music-instance-1.xxxxdatogdsq.eu-west-2.rds.amazonaws.com SQL_PORT=5432 DATABASE=postgres
[文件 settings.py]
DATABASES = { "default": { "ENGINE": os.environ.get("SQL_ENGINE", "django.db.backends.sqlite3"), "NAME": os.environ.get("SQL_DATABASE", os.path.join(BASE_DIR, "db.sqlite3")), "USER": os.environ.get("SQL_USER", "user"), "PASSWORD": os.environ.get("SQL_PASSWORD", "password"), "HOST": os.environ.get("SQL_HOST", "localhost"), "PORT": os.environ.get("SQL_PORT", "5432"), } }
二、预加载数据
$ docker-compose exec movies python manage.py loaddata movies.json
加载初始化数据后的内容，如下：
-
movies_movie
-
movies_customuser
name='CustomUser', fields=[ ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('password', models.CharField(max_length=128, verbose_name='password')), ('last_login', models.DateTimeField(blank=True, null=True, verbose_name='last login')), ('is_superuser', models.BooleanField(default=False, help_text='Designates that this user has all permissions without explicitly assigning them.', verbose_name='superuser status')), ('username', models.CharField(error_messages={'unique': 'A user with that username already exists.'}, help_text='Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only.', max_length=150, unique=True, validators=[django.contrib.auth.validators.UnicodeUsernameValidator()], verbose_name='username')), ('first_name', models.CharField(blank=True, max_length=30, verbose_name='first name')), ('last_name', models.CharField(blank=True, max_length=150, verbose_name='last name')), ('email', models.EmailField(blank=True, max_length=254, verbose_name='email address')), ('is_staff', models.BooleanField(default=False, help_text='Designates whether the user can log into this admin site.', verbose_name='staff status')), ('is_active', models.BooleanField(default=True, help_text='Designates whether this user should be treated as active. Unselect this instead of deleting accounts.', verbose_name='active')), ('date_joined', models.DateTimeField(default=django.utils.timezone.now, verbose_name='date joined')), ('groups', models.ManyToManyField(blank=True, help_text='The groups this user belongs to. A user will get all permissions granted to each of their groups.', related_name='user_set', related_query_name='user', to='auth.Group', verbose_name='groups')), ('user_permissions', models.ManyToManyField(blank=True, help_text='Specific permissions for this user.', related_name='user_set', related_query_name='user', to='auth.Permission', verbose_name='user permissions')), ]
/* implement */
