PostgreSQL:
1. Why not sqlite? a. Can not mutitasking. b. Lack of advanced function. c. Security Issues.
2. In postgreSQL, there are two components; server and client. The most popular clinet is psycopg2, and the port number is 5432.
3. The use of psycopg2 is similar as SQLite:
import psycopg2
conn = psycopg2.connect("dbname=dq user=dq") #Because it can run mutiple database in the same time, so for each database we need to define user.
cur = conn.cursor()
print(cur)
conn.close()
4. Create a table in psycopg2:
quest = "create table notes(id integer primary key, body text, title text);"
cur.execute(quest)
conn.close()
5. SQL transaction: in PostgreSQL, we use transaction to hold the data in pending status after executing them. And then we use commit() function to apply them into the database.
query = "create table notes(id integer primary key, body text, title text)"
cur.execute(query)
conn.commit() #apply the query into the database
conn.close()
6. If we do not want to use transaction function, we can enable autocommit function:
onn = psycopg2.connect("dbname=dq user=dq")
conn.autocommit = True
cur = conn.cursor()
cur.execute("CREATE TABLE notes(id integer PRIMARY KEY, body text, title text)")
7. In postgreSQL, we can create mutiple database in different category:
conn = psycopg2.connect("dbname = dq user = dq")
cur = conn.cursor()
conn.autocommit = True
cur.execute("create database income owner dq")
conn.close()
8. Delete a database：
conn.autocommit = True
cur.execute("drop database income")
conn.close()
