pandas DataFrame.to_sql and nan values
I am trying to use pandas DataFrame.to_sql to insert values in a table of my Postgres database. I have some nan values in a column of integers that does not belong to any constraint.
I get the following error :
sqlalchemy.exc.DataError: (DataError) integer out of range
When I substitute nan values with zéros, insertion happens as wanted, so it is really nan values that are to blame for my error.
I have tried converting nan values to None and to np.nan , but I get the same error.
So the question is: what nan format do I need so that pd.to_sql handles it correctly?
My restrictions are : python 2.7 pandas 0.14.1 , sqlalchemy 0.9.8 , Postgres 9.2
python postgresql pandas dataframe sqlalchemy
edited Nov 26 '18 at 11:55
Matthias
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
add a comment |
I am trying to use pandas DataFrame.to_sql to insert values in a table of my Postgres database. I have some nan values in a column of integers that does not belong to any constraint.
I get the following error :
sqlalchemy.exc.DataError: (DataError) integer out of range
When I substitute nan values with zéros, insertion happens as wanted, so it is really nan values that are to blame for my error.
I have tried converting nan values to None and to np.nan , but I get the same error.
So the question is: what nan format do I need so that pd.to_sql handles it correctly?
My restrictions are : python 2.7 pandas 0.14.1 , sqlalchemy 0.9.8 , Postgres 9.2
python postgresql pandas dataframe sqlalchemy
edited Nov 26 '18 at 11:55
Matthias
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
add a comment |
I am trying to use pandas DataFrame.to_sql to insert values in a table of my Postgres database. I have some nan values in a column of integers that does not belong to any constraint.
I get the following error :
sqlalchemy.exc.DataError: (DataError) integer out of range
When I substitute nan values with zéros, insertion happens as wanted, so it is really nan values that are to blame for my error.
I have tried converting nan values to None and to np.nan , but I get the same error.
So the question is: what nan format do I need so that pd.to_sql handles it correctly?
My restrictions are : python 2.7 pandas 0.14.1 , sqlalchemy 0.9.8 , Postgres 9.2
python postgresql pandas dataframe sqlalchemy
edited Nov 26 '18 at 11:55
Matthias
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
I am trying to use pandas DataFrame.to_sql to insert values in a table of my Postgres database. I have some nan values in a column of integers that does not belong to any constraint.
I get the following error :
sqlalchemy.exc.DataError: (DataError) integer out of range
When I substitute nan values with zéros, insertion happens as wanted, so it is really nan values that are to blame for my error.
I have tried converting nan values to None and to np.nan , but I get the same error.
So the question is: what nan format do I need so that pd.to_sql handles it correctly?
My restrictions are : python 2.7 pandas 0.14.1 , sqlalchemy 0.9.8 , Postgres 9.2
python postgresql pandas dataframe sqlalchemy
python postgresql pandas dataframe sqlalchemy
edited Nov 26 '18 at 11:55
Matthias
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
edited Nov 26 '18 at 11:55
Matthias
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
edited Nov 26 '18 at 11:55
Matthias
2,19462859
edited Nov 26 '18 at 11:55
Matthias
2,19462859
edited Nov 26 '18 at 11:55
Matthias
2,19462859
2,19462859
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
asked Nov 26 '18 at 10:32
Matina GMatina G
607213
607213
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The problem is with your pandas version: 0.14.1.
Starting with pandas 0.15, to_sql supports writing NaN values.
You can try upgrading your pandas.
Also, for now you can convert NAN to NONE like:
df = df.astype(object).where(pd.notnull(df), None)
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
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%2f53479213%2fpandas-dataframe-to-sql-and-nan-values%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
The problem is with your pandas version: 0.14.1.
Starting with pandas 0.15, to_sql supports writing NaN values.
You can try upgrading your pandas.
Also, for now you can convert NAN to NONE like:
df = df.astype(object).where(pd.notnull(df), None)
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
add a comment |
The problem is with your pandas version: 0.14.1.
Starting with pandas 0.15, to_sql supports writing NaN values.
You can try upgrading your pandas.
Also, for now you can convert NAN to NONE like:
df = df.astype(object).where(pd.notnull(df), None)
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
add a comment |
The problem is with your pandas version: 0.14.1.
Starting with pandas 0.15, to_sql supports writing NaN values.
You can try upgrading your pandas.
Also, for now you can convert NAN to NONE like:
df = df.astype(object).where(pd.notnull(df), None)
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
The problem is with your pandas version: 0.14.1.
Starting with pandas 0.15, to_sql supports writing NaN values.
You can try upgrading your pandas.
Also, for now you can convert NAN to NONE like:
df = df.astype(object).where(pd.notnull(df), None)
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
answered Nov 26 '18 at 10:39
Mayank PorwalMayank Porwal
5,0182725
5,0182725
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
add a comment |
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Thank you for your answer. I can unfortunately not upgrage pandas since it is on a server I use, and the administrator does not wish to proceed to an upgrade.. I have already tried replacing by None but it won't work, I get the same error..
– Matina G
Nov 26 '18 at 10:44
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
Please refer to this.
– Mayank Porwal
Nov 26 '18 at 10:46
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.
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%2f53479213%2fpandas-dataframe-to-sql-and-nan-values%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
