Contents [hide]
输出结果
预期结果是一个(正则表达式)
1 | r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$" |
预期结果例
1 2 | 1 name1 male 2019/10/29 18:04:45 2019/10/29 18:04:45 2 name2 female 2019/10/29 18:04:45 2019/10/29 18:04:45 |
先决条件
假设您准备了这样的.env文件来连接到PostgreSQL服务器
1 2 3 | DBHOST=0.0.0.0 DBUSER=exampleuser DBPASSWORD=example20141024 |
然后使用以下命令准备数据库和用户。
1 2 3 4 | CREATE USER exampleuser PASSWORD 'example20141024'; CREATE DATABASE example; GRANT ALL ON DATABASE example TO exampleuser; CREATE TYPE sex_example AS ENUM ('male', 'female'); |
编程代码示例
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | class PsqlManipulator { constructor(opt={}) { const { Pool, Client } = require( 'pg' ); require( 'dotenv' ).config(); this .rdbh = new Client({ host: process.env.DBHOST, user: process.env.DBUSER, password: process.env.DBPASSWORD, database: 'example' }) } async beginTransaction() { const sql = "BEGIN" await this .executeSQL(sql) } async createExampleData() { const datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ]; for (const data of datas) { const query = { text: 'INSERT INTO example (name, sex) VALUES ($1, $2)' , values: [data[0], data[1]] } await this .executeSQL(query); } } async createExampleTable() { const sql = ` CREATE TABLE example ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, sex example_sex DEFAULT NULL, created_at timestamp with time zone DEFAULT NOW(), updated_at timestamp with time zone DEFAULT NOW(), PRIMARY KEY (id) ); CREATE INDEX created_at_idx ON example(created_at); CREATE INDEX name_idx ON example(name); CREATE INDEX updated_at_idx ON example(updated_at); `; await this .executeSQL(sql); } async createExampleType() { const sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')" ; await this .executeSQL(sql); } async dbclose() { if ( typeof ( this .rdbh) !== "undefined" ) { await this .rdbh.end(); } } async dbconnect() { await this .rdbh.connect(); const timezone = "Asia/Tokyo" ; const sql = "SET TIME ZONE '" + timezone + "'" await this .executeSQL(sql) } async dropExampleTable() { const sql = "DROP TABLE IF EXISTS example" ; await this .executeSQL(sql); } async dropExampleType() { const sql = "DROP TYPE IF EXISTS example_sex" ; await this .executeSQL(sql); } async endTransaction() { const sql = "END" await this .executeSQL(sql) } async executeSQL(sql) { try { const res = await this .rdbh.query(sql); } catch (err){ const sql = "ROLLBACK" await this .executeSQL(sql) console.log(err.name + ': ' + err.message); } } async selectFromExampleTable() { const sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example" ; try { const res = await this .rdbh.query(sql) for (const row of res.rows){ console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join( "\t" )) } } catch (err) { const sql = "ROLLBACK" await this .executeSQL(sql) console.log(err.name + ': ' + err.message) process.exit(-1) } } }; module.exports = PsqlManipulator; if (!module.parent) { ( async () => { const rdb = new PsqlManipulator() await rdb.dbconnect() await rdb.beginTransaction() await rdb.dropExampleTable() await rdb.dropExampleType() await rdb.createExampleType() await rdb.createExampleTable() await rdb.createExampleData() await rdb.selectFromExampleTable() await rdb.dropExampleTable() await rdb.dropExampleType() await rdb.endTransaction() await rdb.dbclose() })() } |
Perl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | package PsqlManipulator; use strict; use DBI; use Dotenv; sub new() { my $self = {}; Dotenv->load(); return bless $self ; } sub beginTransaction(){ my $self = shift ; my $sql = "BEGIN" ; $self ->executeSQL( $sql ); } sub createExampleData(){ my $self = shift ; my @datas = ( [ 'name1' , 'male' ], [ 'name2' , 'female' ] ); my $sth = $self ->{ 'rdbh' }->prepare( "INSERT INTO example (name, sex) VALUES (?, ?)" ); foreach my $data_ref ( @datas ) { $sth ->execute( $data_ref ->[0], $data_ref ->[1]); } $sth ->finish(); } sub createExampleTable() { my $self = shift ; my $sql = <<EOF; CREATE TABLE example ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, sex example_sex DEFAULT NULL, created_at timestamp with time zone DEFAULT NOW(), updated_at timestamp with time zone DEFAULT NOW(), PRIMARY KEY (id) ); CREATE INDEX created_at_idx ON example(created_at); CREATE INDEX name_idx ON example(name); CREATE INDEX updated_at_idx ON example(updated_at); EOF $self ->executeSQL( $sql ); } sub createExampleType() { my $self = shift ; my $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')" ; $self ->executeSQL( $sql ); } sub dbclose(){ my $self = shift ; $self ->{ 'rdbh' }->disconnect(); } sub dbconnect(){ my $self = shift ; my $database = "example" ; my $timezone = "Asia/Tokyo" ; $self ->{ 'rdbh' } = DBI-> connect ( "DBI:Pg:database=" . $database . ";host=" . $ENV { 'DBHOST' }, $ENV { 'DBUSER' }, $ENV { 'DBPASSWORD' }, { AutoCommit => 0, PrintError => 1, PrintWarn => 0 }) or die "Failed to connect to db." ; my $sql = "SET TIME ZONE '" . $timezone . "'" ; $self ->{ 'rdbh' }-> do ( $sql ); } sub dropExampleTable(){ my $self = shift ; my $sql = "DROP TABLE IF EXISTS example" ; $self ->executeSQL( $sql ); } sub dropExampleType(){ my $self = shift ; my $sql = "DROP TYPE IF EXISTS example_sex" ; $self ->executeSQL( $sql ); } sub endTransaction(){ my $self = shift ; $self ->{ 'rdbh' }->commit(); } sub executeSQL(){ my $self = shift ; my $sql = shift ; $self ->{ 'rdbh' }-> do ( $sql ) or die ( "Failed to execute " . $sql ); } sub selectFromExampleTable(){ my $self = shift ; my $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example" ; my $sth = $self ->{ 'rdbh' }->prepare( $sql ) or die ( "Failed to prepare " . $sql ); $sth ->execute() or die ( "Failed to execute " . $sql ); while ( my $row_ref = $sth ->fetchrow_hashref) { print ( join ( "\t" , $row_ref ->{ 'id' }, $row_ref ->{ 'name' }, $row_ref ->{ 'sex' }, $row_ref ->{ 'created_at' }, $row_ref ->{ 'updated_at' }). "\n" ); } $sth ->finish(); } if ($0 eq __FILE__) { my $rdb = new PsqlManipulator(); $rdb ->dbconnect(); $rdb ->beginTransaction(); $rdb ->dropExampleTable(); $rdb ->dropExampleType(); $rdb ->createExampleType(); $rdb ->createExampleTable(); $rdb ->createExampleData(); $rdb ->selectFromExampleTable(); $rdb ->dropExampleTable(); $rdb ->dropExampleType(); $rdb ->endTransaction(); $rdb ->dbclose(); } else { 1; } |
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | <?php require_once __DIR__ . '/vendor/autoload.php' ; class PsqlManipulator { private $rdbh ; public function __construct( $opt =[]) { $dotenv = Dotenv\Dotenv::create(__DIR__); $dotenv ->load(); } function beginTransaction() { $this ->rdbh->beginTransaction(); } function createExampleData(){ $datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ]; $sql = "INSERT INTO example (name, sex) VALUES (?, ?)" ; $sth = $this ->rdbh->prepare( $sql ); foreach ( $datas as $data ) { $sth ->execute([ $data [0], $data [1]]); } } function createExampleTable(){ $sql = <<< EOF CREATE TABLE example ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, sex example_sex DEFAULT NULL, created_at timestamp with time zone DEFAULT NOW(), updated_at timestamp with time zone DEFAULT NOW(), PRIMARY KEY (id) ); CREATE INDEX created_at_idx ON example(created_at); CREATE INDEX name_idx ON example(name); CREATE INDEX updated_at_idx ON example(updated_at); EOF; $this ->executeSQL( $sql ); } function createExampleType(){ $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')" ; $this ->executeSQL( $sql ); } function dbclose() { $this ->rdbh = null; } function dbconnect(){ $dbname = "example" ; $timezone = "Asia/Tokyo" ; try { $this ->rdbh = new PDO( 'pgsql:dbname=example;host=' . getenv ( 'DBHOST' ), getenv ( 'DBUSER' ), getenv ( 'DBPASSWORD' )); $this ->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e ) { die ( "Failed to connect: " . $e ->getMessage()); } $sql = "SET TIME ZONE '" . $timezone . "'" ; $this ->executeSQL( $sql ); } function dropExampleTable() { $sql = "DROP TABLE IF EXISTS example" ; $this ->executeSQL( $sql ); } function dropExampleType(){ $sql = "DROP TYPE IF EXISTS example_sex" ; $this ->executeSQL( $sql ); } function endTransaction(){ $this ->rdbh->commit(); } function executeSQL( $sql ) { $sqls = explode ( ';' , $sql ); foreach ( $sqls as $sql ) { if ( $sql ) { $sth = $this ->rdbh->prepare( $sql ); $sth ->execute(); } } } function rollback(){ $this ->rdbh->rollback(); } function selectFromExampleTable(){ $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example" ; $sth = $this ->rdbh->prepare( $sql ); $sth ->execute(); while ( $row = $sth ->fetch(PDO::FETCH_ASSOC)){ print (implode( "\t" , [ $row [ 'id' ], $row [ 'name' ], $row [ 'sex' ], $row [ 'created_at' ], $row [ 'updated_at' ]]). "\n" ); } } } if ( !isset(debug_backtrace()[0]) ) { $pro = new PsqlManipulator(); try { $pro ->dbconnect(); } catch (PDOException $e ){ echo $e ->getMessage(); exit (1); } try { $pro ->beginTransaction(); } catch (PDOException $e ){ echo $e ->getMessage(); $pro ->dbclose(); exit (1); } try { $pro ->dropExampleTable(); $pro ->dropExampleType(); $pro ->createExampleType(); $pro ->createExampleTable(); $pro ->createExampleData(); $pro ->selectFromExampleTable(); $pro ->dropExampleTable(); $pro ->dropExampleType(); $pro ->endTransaction(); $pro ->dbclose(); } catch (PDOException $e ){ $pro ->rollback(); $pro ->dbclose(); throw $e ; } } |
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | from firstclass_dotenv import Dotenv import psycopg2 import os class PsqlManipulator(): def __init__( self , opt = []): dotenv = Dotenv() dotenv.load() def beginTransaction( self ): sql = "BEGIN" self .executeSQL(sql) def createExampleType( self ): sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')" self .executeSQL(sql) def createExampleTable( self ): sql = '''\ CREATE TABLE example ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, sex example_sex DEFAULT NULL, created_at timestamp with time zone DEFAULT NOW(), updated_at timestamp with time zone DEFAULT NOW(), PRIMARY KEY (id) ); CREATE INDEX created_at_idx ON example(created_at); CREATE INDEX name_idx ON example(name); CREATE INDEX updated_at_idx ON example(updated_at); ''' self .executeSQL(sql) def createExampleData( self ): datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ] with self .rdbh.cursor() as cur: for data in datas: cur.execute( "INSERT INTO example (name, sex) VALUES (%s, %s)" , [data[ 0 ], data[ 1 ]]) def dbclose( self ): self .rdbh.close() def dbconnect( self ): dbname = "example" timezone = "Asia/Tokyo" self .rdbh = psycopg2.connect( "host=" + os.environ[ 'DBHOST' ] + " dbname=" + dbname + " user=" + os.environ[ 'DBUSER' ] + " password=" + os.environ[ 'DBPASSWORD' ]) self .executeSQL( "SET TIME ZONE '" + timezone + "'" ) def dropExampleTable( self ): sql = "DROP TABLE IF EXISTS example" self .executeSQL(sql) def dropExampleType( self ): sql = "DROP TYPE IF EXISTS example_sex" self .executeSQL(sql) def endTransaction( self ): self .rdbh.commit() def executeSQL( self , sql): with self .rdbh.cursor() as cur: cur.execute(sql) def rollback( self ): self .rdbh.rollback() def selectFromExampleTable( self ): sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example" with self .rdbh.cursor() as cur: cur.execute(sql) for [ id , name, sex, created_at, updated_at] in cur: print ( "\t" .join([ str ( id ), name, sex, str (created_at), str (updated_at)])) if __name__ = = "__main__" : psql = PsqlManipulator() psql.dbconnect() try : psql.beginTransaction() try : psql.dropExampleTable() psql.dropExampleType() psql.createExampleType() psql.createExampleTable() psql.createExampleData() psql.selectFromExampleTable() psql.dropExampleTable() psql.dropExampleType() psql.endTransaction() except Exception as e: psql.rollback() print (e) finally : psql.dbclose() except Exception as e: print (e) finally : psql.dbclose() |
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | require 'dotenv' require 'pg' class PsqlManipulator def initialize(opt=[]) Dotenv.load( File .join( File .dirname( File .expand_path( __FILE__ )), '.env' ) ) end def dbconnect dbname = "example" timezone = "Asia/Tokyo" @rdbh = PG : :connect ( :host => ENV [ "DBHOST" ], :user => ENV [ "DBUSER" ], :password => ENV [ "DBPASSWORD" ], :dbname => dbname) sql = "SET client_min_messages = 'error'" @rdbh .query(sql) sql = "SET TIME ZONE '" + timezone + "'" @rdbh .query(sql) end def beginTransaction sql = "BEGIN" executeSQL(sql) end def createExampleType sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')" executeSQL(sql) end def createExampleTable sql = '' ' CREATE TABLE example ( id SERIAL NOT NULL , name VARCHAR ( 255 ) NOT NULL , sex example_sex DEFAULT NULL , created_at timestamp with time zone DEFAULT NOW (), updated_at timestamp with time zone DEFAULT NOW (), PRIMARY KEY (id) ); CREATE INDEX created_at_idx ON example(created_at); CREATE INDEX name_idx ON example(name); CREATE INDEX updated_at_idx ON example(updated_at); '' ' executeSQL(sql) end def createExampleData datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ] datas. each do |data| @rdbh .exec_params( "INSERT INTO example (name, sex) VALUES ($1, $2)" , [data[ 0 ], data[ 1 ]]) end end def dbclose @rdbh .close() end def dropExampleTable sql = "DROP TABLE IF EXISTS example" executeSQL(sql) end def dropExampleType sql = "DROP TYPE IF EXISTS example_sex" executeSQL(sql) end def endTransaction sql = "END" @rdbh .query(sql) end def executeSQL(sql) @rdbh .query(sql) end def selectFromExampleTable sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example" results = @rdbh .query(sql) results. each do |row| print([row[ "id" ], row[ 'name' ], row[ 'sex' ], row[ 'created_at' ], row[ 'updated_at' ]].join( "\t" ) + "\n" ) end end end if $0 == __FILE__ rdb = PsqlManipulator. new () begin rdb.dbconnect() rescue => err print(err) exit( 1 ) end begin rdb.beginTransaction() begin rdb.dropExampleTable() rdb.dropExampleType() rdb.createExampleType() rdb.createExampleTable() rdb.createExampleData() rdb.selectFromExampleTable() rdb.dropExampleTable() rdb.dropExampleType() rdb.endTransaction() rescue => err puts( "Automatically roll backed." ) print(err) rdb.dbclose() exit( 1 ) end rescue => err rdb.dbclose() print(err) exit( 1 ) end end |
作者: 栗田 創。1st Class, inc的首席执行官/首席技术官。搜索引擎工程师、网站管理员
证书: AWS Solution Architect,Professional Scrum Master I ,一级IT工程师(日本国家证书),TOEIC900+,东京大学経済学部経営学科卒业
请关注我的Weblio帐户以获取更新
IT相关服务:VPS排名