How to convert python datetime to timestamp and insert in oracle database using to_sql
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier
when I try to insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine. My code:
import sqlalchemy as sa
import datetime
import itertools
...
oracle_db = sa.create_engine('oracle://username:password@host:port/database')
connection= oracle_db.connect()
...
dat_ult_alt = datetime.datetime.now()
df_plano['DAT_ULT_ALT'] = pd.Series(list(itertools.repeat(dat_ult_alt, max)))
df_plano.to_sql('table_name', connection, if_exists='append', index=False)
This code works to fields of type "Date" but does not work with fields of type "timestamp". Do you know what I need to do to convert dat_ult_alt to timestamp?
python oracle sqlalchemy timestamp pandas-to-sql
add a comment |
I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier
when I try to insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine. My code:
import sqlalchemy as sa
import datetime
import itertools
...
oracle_db = sa.create_engine('oracle://username:password@host:port/database')
connection= oracle_db.connect()
...
dat_ult_alt = datetime.datetime.now()
df_plano['DAT_ULT_ALT'] = pd.Series(list(itertools.repeat(dat_ult_alt, max)))
df_plano.to_sql('table_name', connection, if_exists='append', index=False)
This code works to fields of type "Date" but does not work with fields of type "timestamp". Do you know what I need to do to convert dat_ult_alt to timestamp?
python oracle sqlalchemy timestamp pandas-to-sql
add a comment |
I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier
when I try to insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine. My code:
import sqlalchemy as sa
import datetime
import itertools
...
oracle_db = sa.create_engine('oracle://username:password@host:port/database')
connection= oracle_db.connect()
...
dat_ult_alt = datetime.datetime.now()
df_plano['DAT_ULT_ALT'] = pd.Series(list(itertools.repeat(dat_ult_alt, max)))
df_plano.to_sql('table_name', connection, if_exists='append', index=False)
This code works to fields of type "Date" but does not work with fields of type "timestamp". Do you know what I need to do to convert dat_ult_alt to timestamp?
python oracle sqlalchemy timestamp pandas-to-sql
I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier
when I try to insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine. My code:
import sqlalchemy as sa
import datetime
import itertools
...
oracle_db = sa.create_engine('oracle://username:password@host:port/database')
connection= oracle_db.connect()
...
dat_ult_alt = datetime.datetime.now()
df_plano['DAT_ULT_ALT'] = pd.Series(list(itertools.repeat(dat_ult_alt, max)))
df_plano.to_sql('table_name', connection, if_exists='append', index=False)
This code works to fields of type "Date" but does not work with fields of type "timestamp". Do you know what I need to do to convert dat_ult_alt to timestamp?
python oracle sqlalchemy timestamp pandas-to-sql
python oracle sqlalchemy timestamp pandas-to-sql
edited Nov 22 '18 at 19:22
Patricia Rocha Faria
asked Nov 22 '18 at 14:17
Patricia Rocha FariaPatricia Rocha Faria
12
12
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.
create table test ( tstamp TIMESTAMP);
import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()
select * from test;
TSTAMP
------------------------------
22-11-18 09:14:19.422278000 PM
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
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%2f53432923%2fhow-to-convert-python-datetime-to-timestamp-and-insert-in-oracle-database-using%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
Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.
create table test ( tstamp TIMESTAMP);
import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()
select * from test;
TSTAMP
------------------------------
22-11-18 09:14:19.422278000 PM
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
add a comment |
Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.
create table test ( tstamp TIMESTAMP);
import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()
select * from test;
TSTAMP
------------------------------
22-11-18 09:14:19.422278000 PM
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
add a comment |
Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.
create table test ( tstamp TIMESTAMP);
import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()
select * from test;
TSTAMP
------------------------------
22-11-18 09:14:19.422278000 PM
Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.
create table test ( tstamp TIMESTAMP);
import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()
select * from test;
TSTAMP
------------------------------
22-11-18 09:14:19.422278000 PM
answered Nov 22 '18 at 15:50
Kaushik NayakKaushik Nayak
22.4k41332
22.4k41332
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
add a comment |
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat
– Patricia Rocha Faria
Nov 22 '18 at 19:09
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%2f53432923%2fhow-to-convert-python-datetime-to-timestamp-and-insert-in-oracle-database-using%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