How to create a table in PostgreSQL using pyscopg2?

Question

I'm running a Docker Compose application which includes postgres as a service. Here is a portion of docker-compose.yml:

version: '3'

services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=foo
    ports:
      - 5432:5432

After docker-compose build followed by docker-compose up I see the container is running if I do docker ps:

kurt@kurt-ThinkPad:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
8a7a20686728        postgres            "docker-entrypoint..."   28 minutes ago      Up 17 seconds       0.0.0.0:5432->5432/tcp   apkapi_postgres_1

Since port 5432 is mapped to the localhost, I figured I could do the following (in ipython):

In [1]: import psycopg2

In [2]: conn = psycopg2.connect(dbname="postgres", user="postgres", password="fo
   ...: o", host="localhost")

In [3]: cursor = conn.cursor()

In [4]: cursor.execute("CREATE TABLE apks (s3_uri text);")

All these commands execute without any error. To check the result, I tried (using the container ID from docker ps):

kurt@kurt-ThinkPad:~$ docker exec -it 8a7a20686728 bash
root@8a7a20686728:/# psql -U postgres
psql (9.6.3)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | mytable | table | postgres
(1 row)

The problem is that I see a table mytable which I had created before, but not the table apks which I tried to create using psycopg2. Am I not creating the table correctly?

Not sure if it matters in this scenario but are you closing the cursor and the connection? — somesingsomsing
– somesingsomsing, Commented May 31, 2017 at 15:31

user7954200 · Accepted Answer · 2017-05-31 21:32:33Z

5

It sounds like you may not have autocommit enabled so any changes you make to the database aren't being saved. Try enabling it using conn.autocommit = True or, after the execute command, conn.commit().

answered May 31, 2017 at 21:32

user7954200

662 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a table in PostgreSQL using pyscopg2?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related