[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.
- Other than Java and Python are thin because I haven't touched them so much ...
For Java
PostgerSQL JDBC driver is provided, so let's use it. download
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.
As you can see in the table, Python provides multiple drivers. Do you often hear the name of the following three?
- psycopg2: Number of downloads last month (718640)
- pg8000: Number of downloads last month (46977)
- py-postgresql: Number of downloads last month (3339)
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.
- The small number of DLs for py-postgresql seems to be due to the fact that the number of Python3 users themselves is currently small.
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