使用 Tensorflow 在 PostgreSQL 中进行回归分析：第 一 部分 - 入门

dpage@ubuntu:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
[sudo] password for dpage: 
dpage@ubuntu:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
dpage@ubuntu:~$ sudo apt-get update
Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease
Get:2 http://security.ubuntu.com/ubuntu focal-security InRelease [109 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]                 
Get:4 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [81.6 kB]                                          
Get:5 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [188 kB]                
...
dpage@ubuntu:~$ sudo apt-get -y install postgresql-13 postgresql-plpython3-13
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libpq5 pgdg-keyring postgresql-client-13 postgresql-client-common postgresql-common sysstat
...
dpage@ubuntu:~$

dpage@ubuntu:~$ sudo pip3 install tensorflow pandas matplotlib seaborn
Collecting tensorflow
  Downloading tensorflow-2.4.1-cp38-cp38-manylinux2010_x86_64.whl (394.4 MB)
     |████████████████████████████████| 394.4 MB 25 kB/s 
Collecting numpy
  Downloading numpy-1.20.1-cp38-cp38-manylinux2010_x86_64.whl (15.4 MB)
     |████████████████████████████████| 15.4 MB 1.3 MB/s 
...
dpage@ubuntu:~$

dpage@ubuntu:~$ sudo su - postgres
postgres@ubuntu:~$ psql postgres
psql (13.2 (Ubuntu 13.2-1.pgdg20.04+1))
Type "help" for help.
 
postgres=# CREATE DATABASE tf;
CREATE DATABASE
postgres=# \connect tf
You are now connected to database "tf" as user "postgres".
tf=# CREATE LANGUAGE plpython3u;
CREATE EXTENSION
tf=# CREATE FUNCTION tf_version()
tf-#   RETURNS text
tf-# AS $$
tf$#   import tensorflow as tf
tf$#   return tf.__version__
tf$# $$
tf-# LANGUAGE plpython3u;
CREATE FUNCTION
tf=# SELECT tf_version();
 test_tf 
---------
 2.4.1
(1 row)
 
tf=#

tf=# CREATE FUNCTION py_add(a integer, b integer)
tf=#    RETURNS integer
tf=# AS $$
tf=#   return a+b
tf=# $$ LANGUAGE plpython3u;
tf=# SELECT py_add(4, 5);
 py_add 
--------
      9
(1 row)
 
tf=#

tf=# CREATE FUNCTION hello_world()
tf-#   RETURNS void
tf-# AS $$
tf$#   plpy.notice('Hello world!')
tf$# $$ LANGUAGE plpython3u;
CREATE FUNCTION
tf=# SELECT hello_world();
NOTICE:  Hello world!
 hello_world 
-------------
 
(1 row)
 
tf=#

tf=# CREATE FUNCTION show_tables()
tf-#   RETURNS void
tf-# AS $$
tf$#   import pandas as pd
tf$# 
tf$#   tables = plpy.execute('SELECT schemaname, tablename, tableowner FROM pg_tables;')
tf$# 
tf$#   columns = list(rows[0].keys())
tf$#   df = pd.DataFrame.from_records(tables, columns = columns)
tf$# 
tf$#   plpy.notice('Tables: \n{}'.format(df))
tf$# $$ LANGUAGE plpython3u;
tf=# SELECT show_tables();
NOTICE:  Tables: 
            schemaname                tablename tableowner
0           pg_catalog             pg_statistic   postgres
1           pg_catalog                  pg_type   postgres
2           pg_catalog         pg_foreign_table   postgres
3           pg_catalog                pg_authid   postgres
4           pg_catalog    pg_statistic_ext_data   postgres
..                 ...                      ...        ...
61          pg_catalog      pg_subscription_rel   postgres
62  information_schema  sql_implementation_info   postgres
63  information_schema                sql_parts   postgres
64  information_schema               sql_sizing   postgres
65  information_schema             sql_features   postgres
 
[66 rows x 3 columns]
 
 show_tables 
-------------
 
(1 row)
 
tf=#