Airflow PostgresHook returning an ID from an Insert statement not committing
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 '18 at 3:25
Murray Bryant
152
add a comment |
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 '18 at 3:25
Murray Bryant
152
add a comment |
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 '18 at 3:25
Murray Bryant
152
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 '18 at 3:25
Murray Bryant
152
asked Nov 13 '18 at 3:25
Murray Bryant
152
asked Nov 13 '18 at 3:25
Murray Bryant
152
asked Nov 13 '18 at 3:25
Murray Bryant
152
asked Nov 13 '18 at 3:25
Murray Bryant
152
152
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 12:44
andscoop
56927
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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 12:44
andscoop
56927
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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
add a comment |
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 '18 at 12:44
andscoop
56927
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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
add a comment |
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 '18 at 12:44
andscoop
56927
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 '18 at 12:44
andscoop
56927
answered Nov 13 '18 at 12:44
andscoop
56927
answered Nov 13 '18 at 12:44
andscoop
56927
answered Nov 13 '18 at 12:44
andscoop
56927
56927
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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
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 '18 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 '18 at 1:58
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
@MurrayBryant is your code in a place where you would be able to share it (or at least a generic version of it)? I've been struggling through the same commit issue with get_pandas_df() and am having trouble coding this without executing the same SQL twice. Similarly, pg_hook.get_autocommit() didn't work for me. Thanks!
– Dan
Dec 11 '18 at 21:24
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
