Create a table by putting data from psycopg2 on QuestDB

Question

Can QuestDB create a table by putting data from psycopg2 in it?

• I want to initial table using psycopg2

Answer 1

Just like you would do with PostgreSQL. If the table exists, you can just insert. If the table doesn't exist, you need to create it first. QuestDB will automatically create the table if it doesn't exist when using other protocols, but not with the Postgres one.

Here you have a very simplistic example:

import time
import psycopg2 as pg

conn_str = 'user=admin password=quest host=127.0.0.1 port=8812 dbname=qdb'
with pg.connect(conn_str) as connection:
    with connection.cursor() as cur:

        cur.execute(''' 
                    CREATE TABLE IF NOT EXISTS new_table2(
                    ts TIMESTAMP, device_code UUID, temperature DOUBLE
                    ) timestamp(ts) PARTITION BY DAY WAL;
                    ''')

        timestamp = time.time_ns() // 1000
        cur.execute('''
                        INSERT INTO new_table2
                        VALUES (%s, %s, %s);
                    ''',
                    (timestamp, 'ab632aba-be36-43e5-a4a0-4895e9cd3f0d', 79))

        connection.commit()
        time.sleep(0.5)

        cur.execute('SELECT * FROM new_table2')
        print('Selecting rows from test table using cursor.fetchall')
        records = cur.fetchall()

        print("Print each row and its columns values")
        for row in records:
            print("y = ", row[0], row[1], row[2], "\n")

Of course error handling should be implemented, and you don't need to commit after every insert, but that example should help you insert data using the postgresql protocol.

Another detail is that I am creating the table using the WAL parameter. I do that because WAL tables can accept concurrent inserts when using the postgresql protocol, but non-WAL tables get locked in that case. More info at https://questdb.io/docs/concept/write-ahead-log/