[PYTHON] I want to connect to PostgreSQL from various languages

This article is the 4th day of PostgreSQL Advent Calendar 2015. The third day was kasa_zip.

The field of data analysis has been very busy in recent years. On a large scale, you may need around Spark / Hadoop, but if it is a certain scale, RDBMS can handle it. In particular, PostgreSQL has abundant JSON support, so a medium-sized analysis platform would be sufficient.

So, let's organize the driver modules for connecting to PostgreSQL from each language.

For Java

PostgerSQL JDBC driver is provided, so let's use it. download

jdbc.png

When choosing the JDBC driver to use, choose based on the JDK version of the execution environment. For example, JDK 1.8 requires a JDBC 42 driver, so use one of 9.4 build 1202-1206. Since the communication protocol of PostgreSQL is since it is common to 7.4 or later, even if you use the 9.4 driver, you can connect to PostgreSQL 7.4 or later. There is no problem. You should use the latest driver that supports the JDK you are using.

Since the JDBC driver for PostgreSQL is Pure Java, it does not require libpq and operates independently, so only the downloaded driver should be added to the CLASSPATH.

For Python

In the case of Python, it is organized in the PostgreSQL wiki. jdbc1.png

As you can see in the table, Python provides multiple drivers. Do you often hear the name of the following three?

Looking at the number of DLs, pyscopg2 is overwhelming. In fact, it has a lot of usage records, and it is easy to use because it is compatible with both Python 2/3 series. However, psycopg2 depends on ** libpq **, so a package such as postgresql-devel is required.

On the other hand, pg8000 and py-postgresql are implemented in Pure Python, so libpq is not required. In that respect, the introduction cost is low, but ** pg8000 ** supports ** Python2 series ** only </ del> ** py-postgresql ** supports ** Python3 series only **. Please note that.

If it is a simple tool, the PurePython driver is easier, but considering stability etc., it seems safe to use ** psycopg2 **.

As an aside, inside the postgres-toolkit published by @snaga, [PsqlWrapper.py](https://github. A library is implemented that parses and returns standard output in a manageable form while issuing SQL via psql under the name com / uptimejp / postgres-toolkit / blob / develop / lib / PsqlWrapper.py). If you want to create a lightweight tool that works with PostgreSQL personally, such an implementation may be an option.

For Node.js

When connecting to PostgreSQL with Node.js, node-postgres is often used. This is an implementation of Pure Javascript, so you can install it below.

$ npm install pg

In addition, node-postgres also has ** pg-native ** that uses libpq, but in many cases it seems that it is used by ** pg **. In node-postgres, the acquisition result is returned in JSON format.

[
    {
        "empno": "7369",
        "ename": "SMITH",
        "job": "CLERK",
        "mgr": "7902",
        "hiredate": "1980-12-17T00:00:00.000Z",
        "sal": "800.00",
        "comm": null,
        "deptno": "20"
    },
    {
        "empno": "7499",
        "ename": "ALLEN",
        "job": "SALESMAN",
        "mgr": "7698",
        "hiredate": "1981-02-20T00:00:00.000Z",
        "sal": "1600.00",
        "comm": "300.00",
        "deptno": "30"
    }
]

It's a fairly intuitive format, but the JSON type and JSONB type added in 9.1 or later are also as follows. It will be returned as JSON.

sample_db=# create table json_test(json_col json);
CREATE TABLE
sample_db=# insert into json_test values('{"A":{"B":[1,2,3]}}'::json);
INSERT 0 1
sample_db=# insert into json_test values('{"C":{"D":[4,5]}}'::json);
INSERT 0 1

[
    {
        "json_col": {
            "A": {
                "B": [
                    1,
                    2,
                    3
                ]
            }
        }
    },
    {
        "json_col": {
            "C": {
                "D": [
                    4,
                    5
                ]
            }
        }
    }
]

When it comes to Node.js, the backend is often MongoDB, but PostgreSQL is also well suited for Node.js.

For .NET Framework

You can also connect to PostgreSQL from .NET by using Npgsql. You can Get it via Nuget. Npgsql is Pure C #, so it doesn't require libpq. This is nice because sometimes it's a bit cumbersome to prepare PostgreSQL in a Windows environment.

Since it is compatible with ADO.NET, if you have used ADO.NET with other DBs, you can use it without any hesitation.

By the way, Mono seems to work, but I haven't tried it ...

For PHP

Since PostgreSQL function is prepared for php, you will use this. Note that php must be compiled with the ** —with-pgsql ** option to use it. Also, ** —with-pdo-pgsql ** is required when using via PDO. Since it depends on libpq, it is necessary to install a package such as postgresql-devel separately.

You can check if PostgreSQL can be used via PostgreSQL functions or PDO with PHP that has already been introduced by using <? Php phpinfo ()?>.

You can also check the compile options from ** php-config **.

Execution example


$ ./php-config --configure-options
--prefix=/home/sample/apps/php ...--with-pgsql=/home/sample/posgrehome --with-pdo-pgsql=/home/sample/posgrehome 

For Ruby

It seems that Ruby often uses the ruby-pg module. This also seems to be an implementation that uses libpq, so you need to install a package such as postgresql-devel separately.

gem install pg

In addition, if you are building PostgreSQL from source, install it in a different directory than usual, and it will be moss when you install ruby-pg. In that case, specify the path to ** pg_config ** as follows. (It is also written in the manual)

gem install pg -- --with-pg-config=<path to pg_config>

at the end

Roughly speaking, I have summarized the story of using PostgreSQL from various languages. Drivers are available for all major languages, so if you haven't used PostgreSQL yet, why not give it a try?

The person in charge of PostgreSQL Advent Calendar 2015 Day 5 is nuko_yokohama.

Recommended Posts

I want to connect to PostgreSQL from various languages
I want to use jar from python
I want to email from Gmail using Python.
[Python] I want to manage 7DaysToDie from Discord! 1/3
I want to perform SageMaker inference from PHP
I want to make fits from my head
I want to use ceres solver from python
[Python] I want to manage 7DaysToDie from Discord! 2/3
I want to make C ++ code from Python code!
I want to see the file name from DataLoader
I want to detect images of cats from Instagram
[Python3] I want to generate harassment names from Japanese!
Connect to Postgresql with GO
I want to solve Sudoku (Sudoku)
Connect to sqlite from python
I want to start a lot of processes from python
How to connect to various DBs from Python (PEP 249) and SQLAlchemy
I want to calculate the allowable downtime from the operating rate
I want to install a package from requirements.txt with poetry
I want to send a message from Python to LINE Bot
I tried to get various information from the codeforces API
I want to understand systemd roughly
Connect to utf8mb4 database from python
I want to scrape images to learn
Ssh connect to GCP from Windows
I want to do ○○ with Pandas
I want to copy yolo annotations
I want to debug with Python
I want to automatically find high-quality parts from the videos I shot
I want to make a parameter list from CloudFormation code (yaml)
I want to connect remotely to another computer, and the nautilus command
I want to mess with ALB's default security group from CDK
I want to pin Spyder to the taskbar
I want to detect objects with OpenCV
Connect to Packetix VPN from CentOS 7 minimal.
Connect to Docker's MySQL container from Flask
I want to output to the console coolly
Why I moved from Java to Dart
I want to find variations in various statistics! Recommendation for re-sampling (Bootstrap)
I want to print in a comprehension
I want to operate DB using Django's ORM from an external application
I want to scrape them all together.
I want to handle the rhyme part1
I want to send a signal only from the sub thread to the main thread
Tips for manipulating numpy.ndarray from c ++ -I want to use an iterator-
I want to know how LINUX works!
I want to blog with Jupyter Notebook
A story I was addicted to when inserting from Python to a PostgreSQL table
[Python memo] I want to get a 2-digit hexadecimal number from a decimal number
I want to handle the rhyme part3
How to utilize multi-core from multiple languages
I want to build a Python environment
I want to use Linux on mac
I want to pip install with PythonAnywhere
I want to analyze logs with Python
I want to play with aws with python
Connect to postgreSQL from Python and use stored procedures in a loop.
I wrote unit tests for various languages
I want to use IPython Qt Console
I want to display the progress bar
I want to make an automation program!