初识PostgreSQL常用命令

• PostgreSQL

  PostgreSQL is a pwerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature roubustness, and performance.

• Object-Relational Database

  An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language.

• SQLAlchemy

  SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

• SQLAlchemy support PostgreSQL

• 常用命令

  基于psql这个client客户端

  postgres=# \l		# 查看所有database
  postgres=# \l+		# 查看所有更多详细信息
  postgres=# SELECT datname FROM pg_database;		# 查看数据库名称
  postgres=# CREATE DATABASE dbName;				# 创建数据库
  postgres=# \c dbName;							# 选择数据库
  $ psql -h localhost -p 5432 -U postgres dbName	# 从命令行窗口直接进入
  
  postgres=# \d									# 查看所有表格
  postgres=# \d tableName							# 查看某表具体信息
  
  postgres=# \i basics.sql						# 从指定文件中读取SQL命令
  postgres=# CREATE TABLE weather(				# 创建Table
  	city	varchar(80),	
  	temp_lo	int,			--low temperature
  	temp_hi int,			--high temperature
  	prcp	real,			--precipitation
  	date	date
  	);
  postgres=# DROP TABLE weather;					# 删除表格
  
  postgres=# INSERT INTO weather (date, city, temp_hi, temp_lo)
      VALUES ('1994-11-29', "Hayward", 54, 37);	# 顺序可以乱
      
  postgres=# COPY weather FROM '/home/user/weather.txt';	# 从flat-text files直接输入
  postgres=# SELECT * FROM weather;
  

  SQL is case insensitive about key words and identifiers;

  Two dashes ("–") introduce comments;

  Type names are not key words in the syntax;

  Mapping between Python and PostgreSQL types;

• Python API

  database的创建只能通过psql之类客户端登录后创建；

  python API在建立engine时都需要直接指定database。

  针对postgresql，读取&写入数据采用sqlalchemy，创建表格采用psycopg2.

• References

1. Postgresql Tutorial：PostgreSQL Show Databases
2. 菜鸟教程
3. How do I create a table in PostgreSQL with SQLAlchemy?
4. how to create a table in SQLAlchemy using Postgres?
5. Using PostgreSQL through SQLAlchemy
6. 我终于学会了使用python操作postgresql