Relational Database 3 | Psycopg2, SELECT Operations, PostgreSql Datatypes, Mathematical Functions…
Relational Database 3 | Psycopg2, SELECT Operations, PostgreSql Datatypes, Mathematical Functions, and String Functions
- Connect To the Database With Python Package Psycopg2
(1) Install Psycopg2
Psycopg2 is a package for connecting to the PostgreSQL database through a python script. To use this package, we have to install it first.
$ pip install psycopg2
(2) Import Psycopg2
Before we use this package, we have to import it in the first place.
import psycopg2
(3) Set the Login Variables
In order to connect with the server, we have to set the variables of login information. Because we are connected to a server on the localhost, so we are going to use the address 127.0.0.1:5432 here.
host = "127.0.0.1"
port = "5432"
database = <databasename>
user = "postgres"
password = <password>
(4) Connect to the Server
We use the connect method to connect to a server.
conn = psycopg2.connect(host = host,
port = port,
database=database,
user = user,
password = password)
After we connected to the server, we can check out the status of the connection by,
conn.closed
If it returns 0 for us, that means the server is open and the connection is set up.
(4) Close a Connection
To close a connection to the server, we can use the close method.
conn.close()
After we closed the connection to the server, we can check out the status of the connection by,
conn.closed
If it resurns 1 for us, that means the connection is closed.
(5) Open a Cursor
Before we open a cursor for a connection, we have to make sure the status of the connection is open, we can check this by,
conn.closed
When it returns a 0, we can then choose to open a cursor by the cursor() method,
cur = conn.cursor()
The cursor can be used to execute a SQL query and also get the output.
(6) Run a SQL Query by Cursor
If we want to execute a SQL query, we can use the execute() method, for example,
cur.execute("SELECT * FROM works_in")After running this code, the result of the SELECT operation will not be output to the standard out, but it is going to be stored in the cursor itself.
(7) Get the Data in a Cursor
To get the data out of a cursor, we can use the fetchall() method,
cur.fetchall()
After running this method, the data inside this cursor will be cleared and we will get nothing if we run this code for one more time.
(8) Reset the Data in the Database
Suppose we use the UPDATE/INSERT/DELETE queries with the curser and we want to eliminate all the changes, what we can do is to use the rollback() method,
conn.rollback()
(9) Change the Database Permanently
Suppose we use the UPDATE/INSERT/DELETE queries with the curser and we want to make those changes work for the database permanently, what we can do is to use the commit() method,
conn.commit()
2. SELECT Operations
In the previous section, we ran a lot of SELECT queries and now we would like to structure all the things.
- Read only the unique values
SELECT DISTINCT <colname>
FROM <tablename>;
- Read with a constraint
SELECT <colname>
FROM <tablename>
WHERE <constraint>;
- Read not null values
SELECT <colname>
FROM <tablename>
WHERE <colname> IS NOT NULL;
- Read with two constraints
SELECT <colname>
FROM <tablename>
WHERE <constraint_1>
AND <constraint_2>;
- Read with either one of the two constraints
SELECT <colname>
FROM <tablename>
WHERE <constraint_1>
OR <constraint_2>;
- Read an ordered result (ascending)
SELECT <colname>
FROM <tablename>
ORDER BY <colname> ASC;
- Read an ordered result (descending)
SELECT <colname>
FROM <tablename>
ORDER BY <colname> DESC;
- Read an ordered result with two columns (all descending)
SELECT <colname1>, <colname2>
FROM <tablename>
ORDER BY 1 DESC, 2 DESC;
- Read only the first N lines of the result (N must be positive)
SELECT <colname>
FROM <tablename>
LIMIT <N>;
3. PostgreSql Datatypes
(1) Data Types
The data types for PostgreSql is as follows,
BOOLEAN
CHARACTER(n)
VARCHAR(n)
TEXT
INTEGER
REAL
SERIAL
DATE
TIME(p)
TIMESTAMP(p)
INTERVAL
JSON
(2) Convert Datatypes
- Method #1. CAST() function
CAST(<expression> AS <TargetType>)
- Method #2. :: operator
<expression>::<TargetType>
(3) An Introduction of the TIMESTAMP
TIMESTAMP can be treated as a string with the format of ‘YYYY-MM-DD HH:MI:SS’. We can use CAST to change a string in this form to a timestamp.
CAST('2020-10-10 23:30:46' AS TIMESTAMP)Or,
'2020-10-10 23:30:46'::TIMESTAMP
(4) Converting Functions
- NUMBER to STRING
TO_CHAR(<number>, <format>)
The format is,
S -> Sign
D -> Dot
G -> Separator ,
9 -> A Digit
For example,
TO_CHAR(-1125.8, '99G999D99S') -- 01,125.80-
- STRING to NUMBER
TO_NUMBER(<text>, <format>)
The format is,
S -> Sign
D -> Dot
G -> ,
9 -> A Digit
For example,
TO_NUMBER('12,454.8-', '99G999D9S')---12454.8
- TIMESTAMP to STRING
TO_CHAR(<timestamp>, <format>)
The format of the timestamp can be found from the documentation.
For example,
TO_CHAR(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
- STRING to TIMESTAMP
TO_TIMESTAMP(<text>, <format>)
The format of the timestamp can be found from the documentation.
For example,
TO_TIMESTAMP('05 Dec 2020', 'DD Mon YYYY')- STRING to DATE
TO_DATE(<text>, <format>)
The format of the timestamp can be found from the documentation.
For example,
TO_DATE('05 Dec 2020', 'DD Mon YYYY')4. Alias
The alias is used temporally as names for columns or tables using the keyword AS.
SELECT <expression> AS <alias>
FROM <tablename>;
5. Mathematical Functions
(1) NULL Type
NULL is a datatype implicates the unknown values. It can work with the following operators, but it will at most of the times get a result of NULL,
< > = != AND OR ...
But there are some expectations and these expectations are listed here,
NULL OR TRUE -> TRUE
NULL AND FALSE -> FALSE
Note that all the NULLs are identical so they are equal to each other,
NULL IS NULL -> TRUE
It can also work with mathematical functions and those functions will ignore the values of all NULLs
COUNT SUM AVG MAX MIN ...
(2) Mathematical Functions
- get the absolute value of a number
ABS(<num>)
- round the float to integer
ROUND(<num>)
- round the float to n decimal digits
ROUND(<num>, <n>)
- round the float to its ceiling integer
CEIL(<num>)
- round the float to its floor integer
FLOOR(<num>)
- power the num1 with num2
POWER(<num1>, <num2>)
- log num2 with a base of num1
LOG(<num1>, <num2>)
- find the greatest value in a list of numbers
GREATEST(<num1>, <num2>, ...)
- find the smallest value in a list of numbers
LEAST(<num1>, <num2>, ...)
6. String Functions
- Get the number of the characters in a string
LENGTH(<string>)
- String Concatenation
<str1> || <str2> || ...
- change to lower case
LOWER(<string>)
- change to upper case
UPPER(<string>)
- return the first n characters in a string
LEFT(<string>, <n>)
- return the last n characters in a string
RIGHT(<string>, <n>)
- get the substring in a string begin with <start> with length <n>
SUBSTR(<string>, <start>, <n>)
- replace substring from <ori> to <crt>
REPLACE(<string>, <ori>, <crt>)
- remove the spaces at the start and end of a string (like str.strip())
TRIM(<string>)
- remove the longest string containing only the characters from <characters> for the start and end of a string
TRIM(<characters> FROM <string>)
Or,
TRIM(both <characters> FROM <string>)
- remove the longest string containing only the characters from <characters> for the start of a string
TRIM(leading <characters> FROM <string>)
- remove the longest string containing only the characters from <characters> for the end of a string
TRIM(trailing <characters> FROM <string>)
- get the strings matching a specific pattern
<string> LIKE <pattern>
For the pattern, % means zero or more characters, and _ means a single character. For example,
'abc' LIKE 'a%'
'abc' LIKE '_b_'
- get the strings matching a regular expression
<string> SIMILAR TO <regex>
A reference of the regular expression is,
Note that patterns for SQL can also be used in the regex for the argument.