Airflow PostgresHook returning an ID from an Insert statement not committing
up vote
0
down vote
favorite
I am using PostgresHook in an Airflow operator.
pg_hook = PostgresHook(postgres_conn_id='postgres_default')
insert_activities_sql = "INSERT INTO activities (---) VALUES (---) RETURNING id "
activity_results = pg_hook.get_first(insert_activities_sql,parameters=insert_activities_params)
This does return the Id but the record is not committed into the activities table. I have tried running get_records and get_first and neither commit.
.run commits but does not return the results id.
Is this the correct way to insert a record and then return the id?
postgresql airflow
asked Nov 13 at 3:25
Murray Bryant
102
add a comment |
up vote
0
down vote
favorite
I am using PostgresHook in an Airflow operator.
pg_hook = PostgresHook(postgres_conn_id='postgres_default')
insert_activities_sql = "INSERT INTO activities (---) VALUES (---) RETURNING id "
activity_results = pg_hook.get_first(insert_activities_sql,parameters=insert_activities_params)
This does return the Id but the record is not committed into the activities table. I have tried running get_records and get_first and neither commit.
.run commits but does not return the results id.
Is this the correct way to insert a record and then return the id?
postgresql airflow
asked Nov 13 at 3:25
Murray Bryant
102
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am using PostgresHook in an Airflow operator.
pg_hook = PostgresHook(postgres_conn_id='postgres_default')
insert_activities_sql = "INSERT INTO activities (---) VALUES (---) RETURNING id "
activity_results = pg_hook.get_first(insert_activities_sql,parameters=insert_activities_params)
This does return the Id but the record is not committed into the activities table. I have tried running get_records and get_first and neither commit.
.run commits but does not return the results id.
Is this the correct way to insert a record and then return the id?
postgresql airflow
asked Nov 13 at 3:25
Murray Bryant
102
I am using PostgresHook in an Airflow operator.
pg_hook = PostgresHook(postgres_conn_id='postgres_default')
insert_activities_sql = "INSERT INTO activities (---) VALUES (---) RETURNING id "
activity_results = pg_hook.get_first(insert_activities_sql,parameters=insert_activities_params)
This does return the Id but the record is not committed into the activities table. I have tried running get_records and get_first and neither commit.
.run commits but does not return the results id.
Is this the correct way to insert a record and then return the id?
postgresql airflow
postgresql airflow
asked Nov 13 at 3:25
Murray Bryant
102
asked Nov 13 at 3:25
Murray Bryant
102
asked Nov 13 at 3:25
Murray Bryant
102
asked Nov 13 at 3:25
Murray Bryant
102
asked Nov 13 at 3:25
Murray Bryant
102
102
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You can call get_autocommit() to check whether or not autocommit is enabled and then set_autocommit() to enable explicitly. It appears that the Airflow DBApiHook is naively assuming you will not be committing anything when fetching records. Setting it explicitly should resolve that issue.
If you would like even more control over what is happening, you can call get_conn() or get_cursor() and replicate the logic that is happening inside of run() and get_first() to manually commit.
answered Nov 13 at 12:44
andscoop
54927
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You can call get_autocommit() to check whether or not autocommit is enabled and then set_autocommit() to enable explicitly. It appears that the Airflow DBApiHook is naively assuming you will not be committing anything when fetching records. Setting it explicitly should resolve that issue.
If you would like even more control over what is happening, you can call get_conn() or get_cursor() and replicate the logic that is happening inside of run() and get_first() to manually commit.
answered Nov 13 at 12:44
andscoop
54927
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
add a comment |
up vote
1
down vote
accepted
You can call get_autocommit() to check whether or not autocommit is enabled and then set_autocommit() to enable explicitly. It appears that the Airflow DBApiHook is naively assuming you will not be committing anything when fetching records. Setting it explicitly should resolve that issue.
If you would like even more control over what is happening, you can call get_conn() or get_cursor() and replicate the logic that is happening inside of run() and get_first() to manually commit.
answered Nov 13 at 12:44
andscoop
54927
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You can call get_autocommit() to check whether or not autocommit is enabled and then set_autocommit() to enable explicitly. It appears that the Airflow DBApiHook is naively assuming you will not be committing anything when fetching records. Setting it explicitly should resolve that issue.
If you would like even more control over what is happening, you can call get_conn() or get_cursor() and replicate the logic that is happening inside of run() and get_first() to manually commit.
answered Nov 13 at 12:44
andscoop
54927
You can call get_autocommit() to check whether or not autocommit is enabled and then set_autocommit() to enable explicitly. It appears that the Airflow DBApiHook is naively assuming you will not be committing anything when fetching records. Setting it explicitly should resolve that issue.
If you would like even more control over what is happening, you can call get_conn() or get_cursor() and replicate the logic that is happening inside of run() and get_first() to manually commit.
answered Nov 13 at 12:44
andscoop
54927
answered Nov 13 at 12:44
andscoop
54927
answered Nov 13 at 12:44
andscoop
54927
answered Nov 13 at 12:44
andscoop
54927
54927
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
add a comment |
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
Thanks for the pointer. pg_hook.get_autocommit() returned an error. But as you suggested I just replicated the logic inside the run and manually committed
– Murray Bryant
Nov 15 at 1:58
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273338%2fairflow-postgreshook-returning-an-id-from-an-insert-statement-not-committing%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
