Python: Write values from a CSV-file into a postgresql database
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have made a python-script that takes an excel-file, converts it into a csv-file. My problem is that I have several null values in my csv that needs to be null, but as I try to insert it into the database, I get this error
psycopg2.DataError: invalid input syntax for type date: "None"
LINE 1: ...', 'Some info here', 'And some more', '1995-09-06', 'None'
This is the code I written to convert excel-files into a CSV
def excel_to_csv():
xlsx = pd.read_excel(excel_path + fileName + '.xlsx')
xlsx.to_csv(csv_file, encoding='utf-8', index=False, na_rep=None, quoting=csv.QUOTE_NONE)
excel_to_csv()
And when I check the CSV-file, everything is formatted correctly, with values seperated by , and the null value is None, without quotation marks. It seems like the problem start when I try to read the CSV-file and then inserting it into the database. I use csv.reader for this
with open(csv_file, 'rb') as f:
reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
next(reader)
for row in reader:
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
row
)
Does anyone know whats wrong here?
python postgresql pandas csv
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
add a comment |
I have made a python-script that takes an excel-file, converts it into a csv-file. My problem is that I have several null values in my csv that needs to be null, but as I try to insert it into the database, I get this error
psycopg2.DataError: invalid input syntax for type date: "None"
LINE 1: ...', 'Some info here', 'And some more', '1995-09-06', 'None'
This is the code I written to convert excel-files into a CSV
def excel_to_csv():
xlsx = pd.read_excel(excel_path + fileName + '.xlsx')
xlsx.to_csv(csv_file, encoding='utf-8', index=False, na_rep=None, quoting=csv.QUOTE_NONE)
excel_to_csv()
And when I check the CSV-file, everything is formatted correctly, with values seperated by , and the null value is None, without quotation marks. It seems like the problem start when I try to read the CSV-file and then inserting it into the database. I use csv.reader for this
with open(csv_file, 'rb') as f:
reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
next(reader)
for row in reader:
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
row
)
Does anyone know whats wrong here?
python postgresql pandas csv
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
The docs specify that all values will be stringNo automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)
– Charles Landau
Nov 26 '18 at 14:15
add a comment |
I have made a python-script that takes an excel-file, converts it into a csv-file. My problem is that I have several null values in my csv that needs to be null, but as I try to insert it into the database, I get this error
psycopg2.DataError: invalid input syntax for type date: "None"
LINE 1: ...', 'Some info here', 'And some more', '1995-09-06', 'None'
This is the code I written to convert excel-files into a CSV
def excel_to_csv():
xlsx = pd.read_excel(excel_path + fileName + '.xlsx')
xlsx.to_csv(csv_file, encoding='utf-8', index=False, na_rep=None, quoting=csv.QUOTE_NONE)
excel_to_csv()
And when I check the CSV-file, everything is formatted correctly, with values seperated by , and the null value is None, without quotation marks. It seems like the problem start when I try to read the CSV-file and then inserting it into the database. I use csv.reader for this
with open(csv_file, 'rb') as f:
reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
next(reader)
for row in reader:
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
row
)
Does anyone know whats wrong here?
python postgresql pandas csv
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
I have made a python-script that takes an excel-file, converts it into a csv-file. My problem is that I have several null values in my csv that needs to be null, but as I try to insert it into the database, I get this error
psycopg2.DataError: invalid input syntax for type date: "None"
LINE 1: ...', 'Some info here', 'And some more', '1995-09-06', 'None'
This is the code I written to convert excel-files into a CSV
def excel_to_csv():
xlsx = pd.read_excel(excel_path + fileName + '.xlsx')
xlsx.to_csv(csv_file, encoding='utf-8', index=False, na_rep=None, quoting=csv.QUOTE_NONE)
excel_to_csv()
And when I check the CSV-file, everything is formatted correctly, with values seperated by , and the null value is None, without quotation marks. It seems like the problem start when I try to read the CSV-file and then inserting it into the database. I use csv.reader for this
with open(csv_file, 'rb') as f:
reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
next(reader)
for row in reader:
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
row
)
Does anyone know whats wrong here?
python postgresql pandas csv
python postgresql pandas csv
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
asked Nov 26 '18 at 14:04
KremklattKremklatt
186
186
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
The docs specify that all values will be stringNo automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)
– Charles Landau
Nov 26 '18 at 14:15
add a comment |
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
The docs specify that all values will be stringNo automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)
– Charles Landau
Nov 26 '18 at 14:15
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
The docs specify that all values will be string
No automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)– Charles Landau
Nov 26 '18 at 14:15
The docs specify that all values will be string
No automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)– Charles Landau
Nov 26 '18 at 14:15
add a comment |
2 Answers
2
active
oldest
votes
Edit: I see CSV reader is giving you lists of strings, so I'm editing my example accordingly
Postgres doesn't recognize the "None" (but your sql API apparently recognizes None.) You can try replacing all "None" with None. Something like:
for row in reader:
clean_row =
for x in row:
if x == 'None':
clean_row.append(None)
else:
clean_row.append(x)
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
clean_row
)
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x)This works! The database will now treat the value as a null value. Thanks!
– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
add a comment |
My thought is that you need it to be NULL rather than None. See : postgresql insert null value on query.
answered Nov 26 '18 at 14:08
erncyperncyp
326311
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
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%2f53482812%2fpython-write-values-from-a-csv-file-into-a-postgresql-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Edit: I see CSV reader is giving you lists of strings, so I'm editing my example accordingly
Postgres doesn't recognize the "None" (but your sql API apparently recognizes None.) You can try replacing all "None" with None. Something like:
for row in reader:
clean_row =
for x in row:
if x == 'None':
clean_row.append(None)
else:
clean_row.append(x)
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
clean_row
)
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x)This works! The database will now treat the value as a null value. Thanks!
– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
add a comment |
Edit: I see CSV reader is giving you lists of strings, so I'm editing my example accordingly
Postgres doesn't recognize the "None" (but your sql API apparently recognizes None.) You can try replacing all "None" with None. Something like:
for row in reader:
clean_row =
for x in row:
if x == 'None':
clean_row.append(None)
else:
clean_row.append(x)
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
clean_row
)
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x)This works! The database will now treat the value as a null value. Thanks!
– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
add a comment |
Edit: I see CSV reader is giving you lists of strings, so I'm editing my example accordingly
Postgres doesn't recognize the "None" (but your sql API apparently recognizes None.) You can try replacing all "None" with None. Something like:
for row in reader:
clean_row =
for x in row:
if x == 'None':
clean_row.append(None)
else:
clean_row.append(x)
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
clean_row
)
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
Edit: I see CSV reader is giving you lists of strings, so I'm editing my example accordingly
Postgres doesn't recognize the "None" (but your sql API apparently recognizes None.) You can try replacing all "None" with None. Something like:
for row in reader:
clean_row =
for x in row:
if x == 'None':
clean_row.append(None)
else:
clean_row.append(x)
cur.execute(
"INSERT INTO databasetable (foo, foo2, foo3, foo4) VALUES (%s, %s, %s, %s);",
clean_row
)
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
edited Nov 26 '18 at 14:30
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
answered Nov 26 '18 at 14:09
Charles LandauCharles Landau
2,7711317
2,7711317
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x)This works! The database will now treat the value as a null value. Thanks!
– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
add a comment |
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x)This works! The database will now treat the value as a null value. Thanks!
– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
1
1
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:
for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x) This works! The database will now treat the value as a null value. Thanks!– Kremklatt
Nov 26 '18 at 14:24
This solved it for me, but I had to edit the code a little bit. It seems like csv.reader reads None as a string so if you edit the code this:
for row in reader: clean_row = for x in row: if x == 'None': clean_row.append(None) else: clean_row.append(x) This works! The database will now treat the value as a null value. Thanks!– Kremklatt
Nov 26 '18 at 14:24
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
Ok I'll edit to reflect this
– Charles Landau
Nov 26 '18 at 14:28
add a comment |
My thought is that you need it to be NULL rather than None. See : postgresql insert null value on query.
answered Nov 26 '18 at 14:08
erncyperncyp
326311
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
add a comment |
My thought is that you need it to be NULL rather than None. See : postgresql insert null value on query.
answered Nov 26 '18 at 14:08
erncyperncyp
326311
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
add a comment |
My thought is that you need it to be NULL rather than None. See : postgresql insert null value on query.
answered Nov 26 '18 at 14:08
erncyperncyp
326311
My thought is that you need it to be NULL rather than None. See : postgresql insert null value on query.
answered Nov 26 '18 at 14:08
erncyperncyp
326311
answered Nov 26 '18 at 14:08
erncyperncyp
326311
answered Nov 26 '18 at 14:08
erncyperncyp
326311
answered Nov 26 '18 at 14:08
erncyperncyp
326311
326311
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
add a comment |
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
It's still the same error, it seems like the csv.reader is treating both "NULL" and "None" as strings, not null values.
– Kremklatt
Nov 26 '18 at 14:16
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%2f53482812%2fpython-write-values-from-a-csv-file-into-a-postgresql-database%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
Is "None" a legal date value for whatever database you are using?
– stark
Nov 26 '18 at 14:07
According to your error message you are trying to set a date column to "None".
– Red Cricket
Nov 26 '18 at 14:07
Yes, i know this, but it is supposed to be a null value. "None" in python is the same as null in a database according to the documentation. But it seems like the csv.reader is treating all the values of the csv as strings
– Kremklatt
Nov 26 '18 at 14:09
The docs specify that all values will be string
No automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats)– Charles Landau
Nov 26 '18 at 14:15