pandas.DataFrame.to_sql inserts data, but doesn't commit the transaction
I have a pandas dataframe I'm trying to insert into MS SQL EXPRESS as per below:
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@testodbc")
connection = engine.connect()
data = {'Host': ['HOST1','HOST2','HOST3','HOST4'],
'Product': ['Apache HTTP 2.2','RedHat 6.9','OpenShift 2','JRE 1.3'],
'ITBS': ['Infrastructure','Accounting','Operations','Accounting'],
'Remediation': ['Upgrade','No plan','Decommission','Decommission'],
'TargetDate': ['2018-12-31','NULL','2019-03-31','2019-06-30']}
df = pd.DataFrame(data)
When I call:
df.to_sql(name='TLMPlans', con=connection, index=False, if_exists='replace')
and then:
print(engine.execute("SELECT * FROM TLMPLans").fetchall())
I can see the data alright, but it actually doesn't commit any transaction:
D:APPSPythonpython.exe
C:/APPS/DashProjects/dbConnectors/venv/Scripts/readDataFromExcel.py
[('HOST1', 'Apache HTTP 2.2', 'Infrastructure', 'Upgrade', '2018-12-31'), ('HOST2', 'RedHat 6.9', 'Accounting', 'No plan', 'NULL'), ('HOST3', 'OpenShift 2', 'Operations', 'Decommission', '2019-03-31'), ('HOST4', 'JRE 1.3', 'Accounting', 'Decommission', '2019-06-30')]
Process finished with exit code 0
It says here I don't have to commit as SQLAlchemy does it:
Does the Pandas DataFrame.to_sql() function require a subsequent commit()?
and the below suggestions don't work:
Pandas to_sql doesn't insert any data in my table
I spent good 3 hours looking for clues all over the Internet, but I'm not getting any relevant answers, or I don't know how to ask the question.
Any guidance on what to look for would be highly appreciated.
UPDATE
I'm able to commit changes using pyodbc connection and full insert statement, however pandas.DataFrame.to_sql() with SQLAlchemy engine doesn't work. It send the data to memory instead the actual database, regardless if schema is specified or not.
I would really appreciate help with this on, or possibly it is a panda issue I need to report?
sql-server python-3.x pandas sqlalchemy pandas-to-sql asked Nov 21 '18 at 15:41
Bartek Malysz
17512
add a comment |
I have a pandas dataframe I'm trying to insert into MS SQL EXPRESS as per below:
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@testodbc")
connection = engine.connect()
data = {'Host': ['HOST1','HOST2','HOST3','HOST4'],
'Product': ['Apache HTTP 2.2','RedHat 6.9','OpenShift 2','JRE 1.3'],
'ITBS': ['Infrastructure','Accounting','Operations','Accounting'],
'Remediation': ['Upgrade','No plan','Decommission','Decommission'],
'TargetDate': ['2018-12-31','NULL','2019-03-31','2019-06-30']}
df = pd.DataFrame(data)
When I call:
df.to_sql(name='TLMPlans', con=connection, index=False, if_exists='replace')
and then:
print(engine.execute("SELECT * FROM TLMPLans").fetchall())
I can see the data alright, but it actually doesn't commit any transaction:
D:APPSPythonpython.exe
C:/APPS/DashProjects/dbConnectors/venv/Scripts/readDataFromExcel.py
[('HOST1', 'Apache HTTP 2.2', 'Infrastructure', 'Upgrade', '2018-12-31'), ('HOST2', 'RedHat 6.9', 'Accounting', 'No plan', 'NULL'), ('HOST3', 'OpenShift 2', 'Operations', 'Decommission', '2019-03-31'), ('HOST4', 'JRE 1.3', 'Accounting', 'Decommission', '2019-06-30')]
Process finished with exit code 0
It says here I don't have to commit as SQLAlchemy does it:
Does the Pandas DataFrame.to_sql() function require a subsequent commit()?
and the below suggestions don't work:
Pandas to_sql doesn't insert any data in my table
I spent good 3 hours looking for clues all over the Internet, but I'm not getting any relevant answers, or I don't know how to ask the question.
Any guidance on what to look for would be highly appreciated.
UPDATE
I'm able to commit changes using pyodbc connection and full insert statement, however pandas.DataFrame.to_sql() with SQLAlchemy engine doesn't work. It send the data to memory instead the actual database, regardless if schema is specified or not.
I would really appreciate help with this on, or possibly it is a panda issue I need to report?
sql-server python-3.x pandas sqlalchemy pandas-to-sql asked Nov 21 '18 at 15:41
Bartek Malysz
17512
Can you try closing the connection afterto_sql
– Srce Cde
Nov 21 '18 at 15:47
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02
add a comment |
I have a pandas dataframe I'm trying to insert into MS SQL EXPRESS as per below:
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@testodbc")
connection = engine.connect()
data = {'Host': ['HOST1','HOST2','HOST3','HOST4'],
'Product': ['Apache HTTP 2.2','RedHat 6.9','OpenShift 2','JRE 1.3'],
'ITBS': ['Infrastructure','Accounting','Operations','Accounting'],
'Remediation': ['Upgrade','No plan','Decommission','Decommission'],
'TargetDate': ['2018-12-31','NULL','2019-03-31','2019-06-30']}
df = pd.DataFrame(data)
When I call:
df.to_sql(name='TLMPlans', con=connection, index=False, if_exists='replace')
and then:
print(engine.execute("SELECT * FROM TLMPLans").fetchall())
I can see the data alright, but it actually doesn't commit any transaction:
D:APPSPythonpython.exe
C:/APPS/DashProjects/dbConnectors/venv/Scripts/readDataFromExcel.py
[('HOST1', 'Apache HTTP 2.2', 'Infrastructure', 'Upgrade', '2018-12-31'), ('HOST2', 'RedHat 6.9', 'Accounting', 'No plan', 'NULL'), ('HOST3', 'OpenShift 2', 'Operations', 'Decommission', '2019-03-31'), ('HOST4', 'JRE 1.3', 'Accounting', 'Decommission', '2019-06-30')]
Process finished with exit code 0
It says here I don't have to commit as SQLAlchemy does it:
Does the Pandas DataFrame.to_sql() function require a subsequent commit()?
and the below suggestions don't work:
Pandas to_sql doesn't insert any data in my table
I spent good 3 hours looking for clues all over the Internet, but I'm not getting any relevant answers, or I don't know how to ask the question.
Any guidance on what to look for would be highly appreciated.
UPDATE
I'm able to commit changes using pyodbc connection and full insert statement, however pandas.DataFrame.to_sql() with SQLAlchemy engine doesn't work. It send the data to memory instead the actual database, regardless if schema is specified or not.
I would really appreciate help with this on, or possibly it is a panda issue I need to report?
sql-server python-3.x pandas sqlalchemy pandas-to-sql asked Nov 21 '18 at 15:41
Bartek Malysz
17512
I have a pandas dataframe I'm trying to insert into MS SQL EXPRESS as per below:
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@testodbc")
connection = engine.connect()
data = {'Host': ['HOST1','HOST2','HOST3','HOST4'],
'Product': ['Apache HTTP 2.2','RedHat 6.9','OpenShift 2','JRE 1.3'],
'ITBS': ['Infrastructure','Accounting','Operations','Accounting'],
'Remediation': ['Upgrade','No plan','Decommission','Decommission'],
'TargetDate': ['2018-12-31','NULL','2019-03-31','2019-06-30']}
df = pd.DataFrame(data)
When I call:
df.to_sql(name='TLMPlans', con=connection, index=False, if_exists='replace')
and then:
print(engine.execute("SELECT * FROM TLMPLans").fetchall())
I can see the data alright, but it actually doesn't commit any transaction:
D:APPSPythonpython.exe
C:/APPS/DashProjects/dbConnectors/venv/Scripts/readDataFromExcel.py
[('HOST1', 'Apache HTTP 2.2', 'Infrastructure', 'Upgrade', '2018-12-31'), ('HOST2', 'RedHat 6.9', 'Accounting', 'No plan', 'NULL'), ('HOST3', 'OpenShift 2', 'Operations', 'Decommission', '2019-03-31'), ('HOST4', 'JRE 1.3', 'Accounting', 'Decommission', '2019-06-30')]
Process finished with exit code 0
It says here I don't have to commit as SQLAlchemy does it:
Does the Pandas DataFrame.to_sql() function require a subsequent commit()?
and the below suggestions don't work:
Pandas to_sql doesn't insert any data in my table
I spent good 3 hours looking for clues all over the Internet, but I'm not getting any relevant answers, or I don't know how to ask the question.
Any guidance on what to look for would be highly appreciated.
UPDATE
I'm able to commit changes using pyodbc connection and full insert statement, however pandas.DataFrame.to_sql() with SQLAlchemy engine doesn't work. It send the data to memory instead the actual database, regardless if schema is specified or not.
I would really appreciate help with this on, or possibly it is a panda issue I need to report?
sql-server python-3.x pandas sqlalchemy pandas-to-sql sql-server python-3.x pandas sqlalchemy pandas-to-sql asked Nov 21 '18 at 15:41
Bartek Malysz
17512
asked Nov 21 '18 at 15:41
Bartek Malysz
17512
edited Nov 23 '18 at 13:17
asked Nov 21 '18 at 15:41
Bartek Malysz
17512
asked Nov 21 '18 at 15:41
Bartek Malysz
17512
asked Nov 21 '18 at 15:41
Bartek Malysz
17512
17512
Can you try closing the connection afterto_sql
– Srce Cde
Nov 21 '18 at 15:47
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02
add a comment |
Can you try closing the connection afterto_sql
– Srce Cde
Nov 21 '18 at 15:47
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02
Can you try closing the connection after
to_sql– Srce Cde
Nov 21 '18 at 15:47
Can you try closing the connection after
to_sql– Srce Cde
Nov 21 '18 at 15:47
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02
add a comment |
0
active
oldest
votes
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%2f53415630%2fpandas-dataframe-to-sql-inserts-data-but-doesnt-commit-the-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53415630%2fpandas-dataframe-to-sql-inserts-data-but-doesnt-commit-the-transaction%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
Can you try closing the connection after
to_sql– Srce Cde
Nov 21 '18 at 15:47
adding connection.close() doesn't fix anything
– Bartek Malysz
Nov 21 '18 at 15:50
The problem here is con parameter in to_sql function, Change it from connection to "engine" and it should work
– min2bro
Nov 21 '18 at 17:14
@min2bro checked, but still the same, any other suggestions? If I understand correctly, the user credentials I'm passing have read / write permissions, otherwise the df.to_sql would throw an error, correct?
– Bartek Malysz
Nov 21 '18 at 19:02