Styjun

Can I pay with HKD in Macau or Shenzhen?

What's the 1 inch size square knob sticking out of wall?

What is a "staved" town, like in "Staverton"?

If hash functions append the length, why does length extension attack work?

Are there any English words pronounced with sounds/syllables that aren't part of the spelling?

Company requiring me to let them review research from before I was hired

My current job follows "worst practices". How can I talk about my experience in an interview without giving off red flags?

Book in which the "mountain" in the distance was a hole in the flat world

Impact of throwing away fruit waste on a peak > 3200 m above a glacier

Killing a star safely

Can an infinite group have a finite number of elements with order k?

What kind of world would drive brains to evolve high-throughput sensory?

Found old paper shares of Motorola Inc that has since been broken up

Do I care if the housing market has gone up or down, if I'm moving from one house to another?

Inverse Colombian Function

Chemistry Riddle

Strange LED behavior: Why is there a voltage over the LED with only one wire connected to it?

Pass USB 3.0 connection through D-SUB connector

Why do people say "I am broke" instead of "I am broken"?

Why is there an extra "t" in Lemmatization?

How to run a substitute command on only a certain part of the line

Ultraproduct of Dividing Lines

Why is the UH-60 tail rotor canted?

RC differentiator giving a higher output amplitude than input amplitude

Calling date from text file in sql query using python

Calling an external command in PythonHow do I copy a file in Python?How to get file creation & modification date/times in Python?Extracting extension from filename in PythonHow to remove an element from a list by index?If Python is interpreted, what are .pyc files?Find all files in a directory with extension .txt in PythonHow do you append to a file in Python?Why is reading lines from stdin much slower in C++ than Python?How to remove a key from a Python dictionary?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I am trying to input the dates from outside text file into sql query using python Below is my code:

import cx_Oracle
import pandas as pd
import numpy as np
dsn_tns = cx_Oracle.makedsn(ip, port,service_name = SERVICE_NAME)

db = cx_Oracle.connect('username', 'password', dsn_tns)
curs = db.cursor()

with open("Date.txt") as file:
log=file.read().splitlines()
import datetime
var1=datetime.datetime.strptime(log[0], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
var2=datetime.datetime.strptime(log[1], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")

query = curs.execute("""SELECT * from table_name where cr_date >= TO_DATE(%s,'DD-MON-YYYY HH24:MI:SS') AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))

from pandas import DataFrame
df = DataFrame(query.fetchall())

Here i connect my query with Oracle and then trying to insert the date from Date.txt into SQL query which includes two dates like below:

27-DEC-2018 00:00:00
26-JAN-2019 00:00:00

Then i want to save my result into dataframe df but getting below error while inserting dates from text file.

Traceback (most recent call last):
File "C:UsersabDesktoporacle_connect.py", line 24, in <module>
AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Please suggest me what changes i need to do for running this code. Also let me know if my dataframe df pulls the column header too as currently i am inserting column header separately with other code. Thanks in advance

python pandas cx-oracle strptime

share|improve this question

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.
  
  – Jacobr365
  Mar 26 at 14:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It Works...Thanks Jacobr365
  
  – Abhijit Das
  Mar 26 at 14:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  glad to help. accept the answer please.
  
  – Jacobr365
  Mar 26 at 14:35
  

  
  
  
  

add a comment | 

1

I am trying to input the dates from outside text file into sql query using python Below is my code:

import cx_Oracle
import pandas as pd
import numpy as np
dsn_tns = cx_Oracle.makedsn(ip, port,service_name = SERVICE_NAME)

db = cx_Oracle.connect('username', 'password', dsn_tns)
curs = db.cursor()

with open("Date.txt") as file:
log=file.read().splitlines()
import datetime
var1=datetime.datetime.strptime(log[0], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
var2=datetime.datetime.strptime(log[1], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")

query = curs.execute("""SELECT * from table_name where cr_date >= TO_DATE(%s,'DD-MON-YYYY HH24:MI:SS') AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))

from pandas import DataFrame
df = DataFrame(query.fetchall())

Here i connect my query with Oracle and then trying to insert the date from Date.txt into SQL query which includes two dates like below:

27-DEC-2018 00:00:00
26-JAN-2019 00:00:00

Then i want to save my result into dataframe df but getting below error while inserting dates from text file.

Traceback (most recent call last):
File "C:UsersabDesktoporacle_connect.py", line 24, in <module>
AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Please suggest me what changes i need to do for running this code. Also let me know if my dataframe df pulls the column header too as currently i am inserting column header separately with other code. Thanks in advance

python pandas cx-oracle strptime

share|improve this question

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.
  
  – Jacobr365
  Mar 26 at 14:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It Works...Thanks Jacobr365
  
  – Abhijit Das
  Mar 26 at 14:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  glad to help. accept the answer please.
  
  – Jacobr365
  Mar 26 at 14:35
  

  
  
  
  

add a comment | 

I am trying to input the dates from outside text file into sql query using python Below is my code:

import cx_Oracle
import pandas as pd
import numpy as np
dsn_tns = cx_Oracle.makedsn(ip, port,service_name = SERVICE_NAME)

db = cx_Oracle.connect('username', 'password', dsn_tns)
curs = db.cursor()

with open("Date.txt") as file:
log=file.read().splitlines()
import datetime
var1=datetime.datetime.strptime(log[0], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
var2=datetime.datetime.strptime(log[1], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")

query = curs.execute("""SELECT * from table_name where cr_date >= TO_DATE(%s,'DD-MON-YYYY HH24:MI:SS') AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))

from pandas import DataFrame
df = DataFrame(query.fetchall())

Here i connect my query with Oracle and then trying to insert the date from Date.txt into SQL query which includes two dates like below:

27-DEC-2018 00:00:00
26-JAN-2019 00:00:00

Then i want to save my result into dataframe df but getting below error while inserting dates from text file.

Traceback (most recent call last):
File "C:UsersabDesktoporacle_connect.py", line 24, in <module>
AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Please suggest me what changes i need to do for running this code. Also let me know if my dataframe df pulls the column header too as currently i am inserting column header separately with other code. Thanks in advance

python pandas cx-oracle strptime

share|improve this question

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

import cx_Oracle
import pandas as pd
import numpy as np
dsn_tns = cx_Oracle.makedsn(ip, port,service_name = SERVICE_NAME)

db = cx_Oracle.connect('username', 'password', dsn_tns)
curs = db.cursor()

with open("Date.txt") as file:
log=file.read().splitlines()
import datetime
var1=datetime.datetime.strptime(log[0], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
var2=datetime.datetime.strptime(log[1], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")

query = curs.execute("""SELECT * from table_name where cr_date >= TO_DATE(%s,'DD-MON-YYYY HH24:MI:SS') AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))

from pandas import DataFrame
df = DataFrame(query.fetchall())

27-DEC-2018 00:00:00
26-JAN-2019 00:00:00

share|improve this question

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

share|improve this question

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

asked Mar 26 at 14:22

Abhijit DasAbhijit Das

822 bronze badges

1 Answer
1

active

oldest

votes

0

cx_oracle takes bind variable sin the form of ":1 , :2, :3" or dictionaries.

try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.

you could also do it with a dictionary and have it be ":val1" & ":val2" and then pass in a dictionary instead of a list like "val1":"something", "val2":"something2"

share|improve this answer

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55359468%2fcalling-date-from-text-file-in-sql-query-using-python%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

cx_oracle takes bind variable sin the form of ":1 , :2, :3" or dictionaries.

try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.

you could also do it with a dictionary and have it be ":val1" & ":val2" and then pass in a dictionary instead of a list like "val1":"something", "val2":"something2"

share|improve this answer

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

add a comment | 

0

cx_oracle takes bind variable sin the form of ":1 , :2, :3" or dictionaries.

try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.

you could also do it with a dictionary and have it be ":val1" & ":val2" and then pass in a dictionary instead of a list like "val1":"something", "val2":"something2"

share|improve this answer

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

add a comment | 

cx_oracle takes bind variable sin the form of ":1 , :2, :3" or dictionaries.

try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.

you could also do it with a dictionary and have it be ":val1" & ":val2" and then pass in a dictionary instead of a list like "val1":"something", "val2":"something2"

share|improve this answer

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

share|improve this answer

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges

answered Mar 26 at 14:32

Jacobr365Jacobr365

7661010 silver badges2424 bronze badges