A script doesn't execute the command in cur.execute()

Question

I'm new to PostgreSQL and psycopg2, and I face a problem.

import psycopg2


def create_tables(whichone):

     in_str_station = "CREATE TABLE station (id SMALLINT NOT NULL PRIMARY KEY,  name VARCHAR(40) NOT NULL," \
                 " country VARCHAR(3) NOT NULL, latitude VARCHAR(10),  " \
                 "longitude VARCHAR(10),height SMALLINT);"

     in_str_dailyData = "CREATE TABLE daily_data (id BIGSERIAL NOT NULL PRIMARY KEY," \
                   "s_id SMALLINT NOT NULL REFERENCES station(id)," \
                   "d_date DATE NOT NULL, d_mean NUMERIC(6, 1), quality SMALLINT);"

     int_str_monthlyMean = "CREATE TABLE monthly_mean (id BIGSERIAL NOT NULL PRIMARY KEY,"\
                      "s_id SMALLINT NOT NULL REFERENCES station(id),"\
                      "m_date DATE NOT NULL, m_mean NUMERIC(9, 3),"\
                      "var NUMERIC(9, 3), std NUMERIC(9, 3));"

     in_str_yearlymean = "CREATE TABLE yearly_mean (id BIGSERIAL NOT NULL PRIMARY KEY, " \
                    "s_id SMALLINT NOT NULL REFERENCES station(id)," \
                    "y_date DATE NOT NULL, y_mean NUMERIC(9, 3),var NUMERIC(9, 3)," \
                    "std NUMERIC(9, 3), var_m NUMERIC(9, 3), std_m NUMERIC(9, 3));"

     database_list = {'station': in_str_station, 'monthly_mean': int_str_monthlyMean,
                 'daily_data': in_str_dailyData, 'yearly_mean': in_str_yearlymean}

     try:
        conn = psycopg2.connect(
          host="localhost",
          database="climate",
          user="postgres",
          password="1")

       cur = conn.cursor()
       in_str = database_list.get(whichone)
       cur.execute(in_str)
       output_ = cur.fetchall()
       print(output_)
       cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
       print(error)
    finally:
       if conn is not None:
          conn.close()

After I run the script, no matter which one of the in_str_ I choose, the table is not created. I have checked and when I copy the content of in_str that I executed in cur.execute and use it in the PostgreSQL shell, everything works.

Where did I make the mistake?

This code defines the create_tables() function but does not call it. If you have other code that actually calls it, please show that code. — John Gordon
– John Gordon, Commented Dec 9, 2020 at 16:56

AdamKG · Accepted Answer · 2020-12-09 16:58:48Z

1

Call conn.commit() after cur.execute(), but before conn.close(). Transactions are not implicitly committed with psycopg2:

If the connection is closed (using the close() method) or destroyed (using del or by letting it fall out of scope) while a transaction is in progress, the server will discard the transaction.

answered Dec 9, 2020 at 16:58

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

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

1 Comment

Piotr Nowakowski Over a year ago

Okey thanks it works after i added this after cur.execute()

user7753123 · Accepted Answer · 2020-12-09 16:51:18Z

0

I don't know if you just did that here but it's indented wrong. You need to indent code after the function.

def foo(a):
    pass

answered Dec 9, 2020 at 16:51

user7753123

1 Comment

Piotr Nowakowski Over a year ago

oh sorry mistake when I was pasting code there are indentation everywhere

Collectives™ on Stack Overflow

A script doesn't execute the command in cur.execute()

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related