CODEWITHSUNDEEP
pythonpostgresql
arcee123
arcee123

Reputation: 243

insert records do not show up in postgres

I am using python to perform basic ETL to transfer records from a mysql database to a postgres database. I am using python to commence the tranfer:

python code

  source_cursor = source_cnx.cursor()
  source_cursor.execute(query.extract_query)
  data = source_cursor.fetchall()
  source_cursor.close()

  # load data into warehouse db
  if data:
    target_cursor = target_cnx.cursor()
    #target_cursor.execute("USE {};".format(datawarehouse_name))
    target_cursor.executemany(query.load_query, data)
    print('data loaded to warehouse db')
    target_cursor.close()
  else:
    print('data is empty')

MySQL Extract (extract_query):

SELECT `tbl_rrc`.`id`,
   `tbl_rrc`.`col_filing_operator`,
   `tbl_rrc`.`col_medium`,
   `tbl_rrc`.`col_district`,
   `tbl_rrc`.`col_type`,
    DATE_FORMAT(`tbl_rrc`.`col_timestamp`, '%Y-%m-%d %T.%f') as `col_timestamp` 
from `tbl_rrc`

PostgreSQL (loading_query)

INSERT INTO geo_data_staging.tbl_rrc
    (id,
    col_filing_operator,
    col_medium,
    col_district,
    col_type,
    col_timestamp)
    VALUES
    (%s,%s,%s,%s,%s);

Of note, there is a PK constraint on Id.

The problem is while I have no errors, I'm not seeing any of the records in the target table. I tested this by manually inserting a record, then running again. The code errored out violating PK constraint. So I know it's finding the table.

Any idea on what I could be missing, I would be greatly appreciate it.

Upvotes: 2

Views: 1032

Answers (1)

jmelesky
jmelesky

Reputation: 3980

Using psycopg2, you have to call commit() on your cursors in order for transactions to be committed. If you just call close(), the transaction will implicitly roll back.

There are a couple of exceptions to this. You can set the connection to autocommit. You can also use your cursors inside a with block, which will automatically commit if the block doesn't throw any exceptions.

Upvotes: 4

Related Questions