Skip to content

Commit cb49a32

Browse files
mike-kfedmike-kfed
committed
code for 0x21 Postgresql
1 parent 49c4d48 commit cb49a32

File tree

9 files changed

+261
-0
lines changed

9 files changed

+261
-0
lines changed

‎21_postgresql/import_cases.sh‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
psql -f import_cases.sql

‎21_postgresql/import_cases.sql‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
drop table cases, month_cases;
2+
begin;
3+
4+
CREATE TABLE cases(
5+
human_date text,
6+
d date,
7+
daily_confirmed integer,
8+
total_confirmed integer,
9+
daily_recovered integer,
10+
total_recovered integer,
11+
daily_deceased integer,
12+
total_deceased integer
13+
);
14+
15+
\copy cases from 'case_time_series.csv' with csv header;
16+
17+
ALTER TABLE cases DROP COLUMN "human_date";
18+
19+
CREATE TABLE month_cases (
20+
mon text primary key,
21+
new_cases integer,
22+
recovered integer
23+
);
24+
commit;

‎21_postgresql/python/helper.py‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
MOST_RECOVERED = """SELECT * FROM cases
2+
WHERE daily_recovered = (SELECT max(daily_recovered) FROM cases);"""
3+
GROUP_BY_MONTH = """
4+
SELECT date_trunc('month', d)::date as month,
5+
sum(daily_confirmed) as new_cases,
6+
sum(daily_recovered) as recovered
7+
FROM cases
8+
GROUP BY month
9+
ORDER BY month"""

‎21_postgresql/python/pg_async.py‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
import asyncio
2+
import psycopg2.extras
3+
import aiopg
4+
import helper
5+
6+
7+
async def go():
8+
async with aiopg.create_pool("host=localhost user=htd") as pool:
9+
async with pool.acquire() as conn:
10+
async with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
11+
await cur.execute(helper.MOST_RECOVERED)
12+
rows = await cur.fetchall()
13+
print('most recoveries', dict(rows[0]))
14+
15+
await cur.execute("BEGIN") # conn.commit() not available
16+
await cur.execute("DELETE FROM month_cases")
17+
await cur.execute(helper.GROUP_BY_MONTH)
18+
ret = []
19+
async for row in cur:
20+
ret.append(row)
21+
for row in ret:
22+
print(row)
23+
await cur.execute(
24+
'''INSERT INTO month_cases
25+
(mon, new_cases, recovered)
26+
VALUES (%s, %s, %s)''', row)
27+
await cur.execute("COMMIT") # conn.commit() not available
28+
29+
30+
if __name__ == '__main__':
31+
loop = asyncio.get_event_loop()
32+
loop.run_until_complete(go())

‎21_postgresql/python/pg_blocking.py‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
import psycopg2
2+
import psycopg2.extras
3+
import helper
4+
5+
if __name__ == '__main__':
6+
con = psycopg2.connect("host=localhost user=htd")
7+
cursor = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
8+
ins_cursor = con.cursor()
9+
cursor.execute(helper.MOST_RECOVERED)
10+
for row in cursor:
11+
print('most recoveries', dict(row))
12+
13+
ins_cursor.execute("DELETE FROM month_cases")
14+
cursor.execute(helper.GROUP_BY_MONTH)
15+
for row in cursor:
16+
print(tuple(row))
17+
ins_cursor.execute(
18+
'''INSERT INTO month_cases
19+
(mon, new_cases, recovered)
20+
VALUES (%s, %s, %s)''', row)
21+
22+
con.commit()

‎21_postgresql/rust/pg_async/Cargo.toml‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
[package]
2+
name = "pg_async"
3+
version = "0.1.0"
4+
authors = ["Bedroom Builds"]
5+
edition = "2018"
6+
7+
# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
8+
9+
[dependencies]
10+
chrono = "0.4.19"
11+
tokio = { version = "1.5", features = ["full"] }
12+
13+
tokio-postgres = "0.7.2"
14+
postgres = { version = "0.19", features = ["with-chrono-0_4"] }
15+
futures = "0.3.14"
16+
#tokio-postgres = { version = "0.7", features = ["default"] }

‎21_postgresql/rust/pg_async/src/main.rs‎

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,80 @@
1+
use std::collections::HashMap;
2+
use tokio_postgres::{types::Type, Error, NoTls};
3+
4+
static MOST_RECOVERED: &str = r#"SELECT * FROM cases
5+
WHERE daily_recovered = (SELECT max(daily_recovered) FROM cases);"#;
6+
static GROUP_BY_MONTH: &str = "
7+
SELECT date_trunc('month', d)::date as month,
8+
sum(daily_confirmed) as new_cases,
9+
sum(daily_recovered) as recovered
10+
FROM cases
11+
GROUP BY month
12+
ORDER BY month";
13+
14+
/// silly way to convert row into Vec of Strings from postgres types
15+
/// see https://docs.rs/postgres/latest/postgres/types/trait.FromSql.html
16+
async fn row_to_str_vec(row: &tokio_postgres::Row) -> Vec<String> {
17+
(0..row.len())
18+
.into_iter()
19+
.map(|ci| match *row.columns().get(ci).unwrap().type_() {
20+
Type::DATE => row.get::<usize, chrono::NaiveDate>(ci).to_string(),
21+
Type::INT4 => row.get::<usize, i32>(ci).to_string(),
22+
Type::INT8 => row.get::<usize, i64>(ci).to_string(),
23+
_ => "blah".to_string(),
24+
})
25+
.collect()
26+
}
27+
28+
async fn row_to_str_hashmap(row: &tokio_postgres::Row) -> HashMap<String, String> {
29+
let values = row_to_str_vec(row).await;
30+
values
31+
.into_iter()
32+
.zip(row.columns().iter())
33+
.map(|(v, k)| (k.name().to_string(), v))
34+
.collect()
35+
}
36+
37+
#[tokio::main]
38+
async fn main() -> Result<(), Error> {
39+
let (mut client, connection) =
40+
tokio_postgres::connect("host=localhost user=htd", NoTls).await?;
41+
42+
// connection object performs communication with DB, spawned to run on its own
43+
tokio::spawn(async move {
44+
if let Err(e) = connection.await {
45+
eprintln!("connection error: {}", e);
46+
}
47+
});
48+
49+
let rows = client.query(MOST_RECOVERED, &[]).await?;
50+
51+
let value: chrono::NaiveDate = rows[0].get(0);
52+
println!("{:?}", value);
53+
println!("{:?}", row_to_str_vec(&rows[0]).await);
54+
println!("{:?}", row_to_str_hashmap(&rows[0]).await);
55+
56+
let transaction = client.transaction().await?;
57+
transaction.query("DELETE FROM month_cases", &[]).await?;
58+
let ins_mv = transaction
59+
.prepare(
60+
"INSERT INTO month_cases
61+
(mon, new_cases, recovered)
62+
VALUES ($1, $2, $3)",
63+
)
64+
.await?;
65+
66+
for row in transaction.query(GROUP_BY_MONTH, &[]).await? {
67+
println!("{:?}", row_to_str_vec(&row).await);
68+
let date: chrono::NaiveDate = row.get(0);
69+
let new_cases: i64 = row.get(1);
70+
let recovered: i64 = row.get(2);
71+
transaction
72+
.execute(
73+
&ins_mv,
74+
&[&date.to_string(), &(new_cases as i32), &(recovered as i32)],
75+
)
76+
.await?;
77+
}
78+
transaction.commit().await?;
79+
Ok(())
80+
}

‎21_postgresql/rust/pg_blocking/Cargo.toml‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
[package]
2+
name = "pg_blocking"
3+
version = "0.1.0"
4+
authors = ["Bedroom Builds"]
5+
edition = "2018"
6+
7+
# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
8+
9+
[dependencies]
10+
chrono = "0.4.19"
11+
postgres = { version = "0.19.1", features=["with-chrono-0_4"]}

‎21_postgresql/rust/pg_blocking/src/main.rs‎

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
use postgres::{types::Type, Client, Error, NoTls};
2+
use std::collections::HashMap;
3+
4+
static MOST_RECOVERED: &str = r#"SELECT * FROM cases
5+
WHERE daily_recovered = (SELECT max(daily_recovered) FROM cases);"#;
6+
static GROUP_BY_MONTH: &str = "
7+
SELECT date_trunc('month', d)::date as month,
8+
sum(daily_confirmed) as new_cases,
9+
sum(daily_recovered) as recovered
10+
FROM cases
11+
GROUP BY month
12+
ORDER BY month";
13+
14+
/// silly way to convert row into Vec of Strings from postgres types
15+
/// see https://docs.rs/postgres/latest/postgres/types/trait.FromSql.html
16+
fn row_to_str_vec(row: &postgres::Row) -> Vec<String> {
17+
(0..row.len())
18+
.into_iter()
19+
.map(|ci| match *row.columns().get(ci).unwrap().type_() {
20+
Type::DATE => row.get::<usize, chrono::NaiveDate>(ci).to_string(),
21+
Type::INT4 => row.get::<usize, i32>(ci).to_string(),
22+
Type::INT8 => row.get::<usize, i64>(ci).to_string(),
23+
_ => "blah".to_string(),
24+
})
25+
.collect()
26+
}
27+
28+
fn row_to_str_hashmap(row: &postgres::Row) -> HashMap<String, String> {
29+
let values = row_to_str_vec(row);
30+
values
31+
.into_iter()
32+
.zip(row.columns().iter())
33+
.map(|(v, k)| (k.name().to_string(), v))
34+
.collect()
35+
}
36+
37+
fn main() -> Result<(), Error> {
38+
let mut client = Client::connect("host=localhost user=htd", NoTls)?;
39+
40+
let rows = client.query(MOST_RECOVERED, &[])?;
41+
42+
let value: chrono::NaiveDate = rows[0].get(0);
43+
println!("{:?}", value);
44+
println!("{:?}", row_to_str_vec(&rows[0]));
45+
println!("{:?}", row_to_str_hashmap(&rows[0]));
46+
47+
let mut transaction = client.transaction()?;
48+
transaction.query("DELETE FROM month_cases", &[])?;
49+
let ins_mv = transaction.prepare(
50+
"INSERT INTO month_cases
51+
(mon, new_cases, recovered)
52+
VALUES ($1, $2, $3)",
53+
)?;
54+
for row in transaction.query(GROUP_BY_MONTH, &[])? {
55+
println!("{:?}", row_to_str_vec(&row));
56+
let date: chrono::NaiveDate = row.get(0);
57+
let new_cases: i64 = row.get(1);
58+
let recovered: i64 = row.get(2);
59+
transaction.execute(
60+
&ins_mv,
61+
&[&date.to_string(), &(new_cases as i32), &(recovered as i32)],
62+
)?;
63+
}
64+
transaction.commit()?;
65+
Ok(())
66+
}

0 commit comments

Comments
 (0)