conn = psycopg2.connect(
"postgresql://maxroach@localhost:26257/bank?sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key&sslcert=certs/client.maxroach.crt")
def create_accounts(conn):
with conn.cursor() as cur:
cur.execute(
"CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)"
)
cur.execute("UPSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)")
logging.debug("create_accounts(): status message: %s", cur.statusmessage)
conn.commit()
def delete_accounts(conn):
with conn.cursor() as cur:
cur.execute("DELETE FROM bank.accounts")
logging.debug("delete_accounts(): status message: %s", cur.statusmessage)
conn.commit()
create_accounts(conn)
public static void main(String[] args) {
// Configure the database connection.
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerName("localhost");
ds.setPortNumber(26257);
ds.setDatabaseName("bank");
ds.setUser("maxroach");
ds.setPassword(null);
ds.setSsl(true);
ds.setSslMode("require");
ds.setSslRootCert("certs/client.root.crt");
ds.setSslCert("certs/client.maxroach.crt");
ds.setSslKey("certs/client.maxroach.key.pk8");
ds.setReWriteBatchedInserts(true); // add `rewriteBatchedInserts=true` to pg connection string
ds.setApplicationName("BasicExample");
// Set up the 'accounts' table.
createAccounts();
// Insert a few accounts "by hand", using INSERTs on the backend.
int updatedAccounts = updateAccounts();
System.out.printf("BasicExampleDAO.updateAccounts:\n => %s total updated accounts\n", updatedAccounts);
}
public static void createAccounts(PGSimpleDataSource ds) {
String sql = "CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT, CONSTRAINT balance_gt_0 CHECK (balance >= 0))";
try (Connection connection = ds.getConnection()) {
connection.execSQLUpdate(sql);
}
}
public static int updateAccounts(PGSimpleDataSource ds) {
String sql1 = "INSERT INTO accounts (id, balance) VALUES (1, 1000)";
String sql2 = "INSERT INTO accounts (id, balance) VALUES (2, 250)";
try (Connection connection = ds.getConnection()) {
connection.execSQLUpdate(sql1);
connection.execSQLUpdate(sql2);
}
}
func main() {
config, err := pgx.ParseConfig("postgresql://maxroach@localhost:26257/bank?sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key&sslcert=certs/client.maxroach.crt")
if err != nil {
log.Fatal("error configuring the database: ", err)
}
config.TLSConfig.ServerName = "localhost"
// Connect to the "bank" database.
conn, err := pgx.ConnectConfig(context.Background(), config)
if err != nil {
log.Fatal("error connecting to the database: ", err)
}
defer conn.Close(context.Background())
// Create the "accounts" table.
if _, err := conn.Exec(context.Background(),
"CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)"); err != nil {
log.Fatal(err)
}
// Insert two rows into the "accounts" table.
if _, err := conn.Exec(context.Background(),
"INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
log.Fatal(err)
}
# Connect to the "bank" database.
conn = PG.connect(
user: 'maxroach',
dbname: 'bank',
host: 'localhost',
port: 26257,
sslmode: 'require',
sslrootcert: 'certs/ca.crt',
sslkey: 'certs/client.maxroach.key',
sslcert: 'certs/client.maxroach.crt'
)
# Create the "accounts" table.
conn.exec('CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)')
# Insert two rows into the "accounts" table.
conn.exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)')
