Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.

#1 April 23, 2010 21:00:02

Tim S.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


Hello.

This code works fine:

>>> import cx_Oracle>>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort),lDatabaseName)>>> lConnectString = "%s/%...@%s" % (lDatabaseUsername,lDatabasePassword, lDsn)>>> lConnection = cx_Oracle.connect(lConnectString)
>>> cursor = lConnection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
>>> print lOutput
<cx_Oracle.STRING with value 'N'>
>>> print lOutput.getvalue()
NHowever, if I change this to get the connection from Django, it allfalls in a big heap:>>> from django.db import connection
>>> cursor = connection.cursor()
>>> import cx_Oracle
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
Traceback (most recent call last):
File "<console>", line 1, in <module>File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py",line 19, in executereturn self.cursor.execute(sql, params)File "/web/djangocourse\django\db\backends\oracle\base.py", line 435,in executequery = convert_unicode(query % tuple(args), self.charset)
TypeError: not all arguments converted during string formatting

Can anyone point me in the direction of how I can fix this?

Cheers,

Tim.

--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#2 April 23, 2010 21:32:46

Skylar S.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


Looking at the source for execute on django-trunk in
django.db.backends.oracle

If you have a param, you will have an arg for the query (type:
string).http://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly... your params should be a list of things to interpolate into the
query string, yes?http://docs.python.org/library/stdtypes.html#string-formattingOn Apr 23, 3:59 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:
> Hello.
>
> This code works fine:
>
>  >>> import cx_Oracle
>  >>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort),
> lDatabaseName)
>  >>> lConnectString = "%s/%...@%s" % (lDatabaseUsername,
> lDatabasePassword, lDsn)
>  >>> lConnection = cx_Oracle.connect(lConnectString)
>  >>> cursor = lConnection.cursor()
>  >>> lOutput = cursor.var(cx_Oracle.STRING)
>  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
>  >>> print lOutput
> <cx_Oracle.STRING with value 'N'>
>  >>> print lOutput.getvalue()
> N
>
> However, if I change this to get the connection from Django, it all
> falls in a big heap:
>
>  >>> from django.db import connection
>  >>> cursor = connection.cursor()
>  >>> import cx_Oracle
>  >>> lOutput = cursor.var(cx_Oracle.STRIN
>  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
> Traceback (most recent call last):
>    File "<console>", line 1, in <module>
>    File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py",
> line 19, in execute
>      return self.cursor.execute(sql, params)
>    File "/web/djangocourse\django\db\backends\oracle\base.py", line 435,
> in execute
>      query = convert_unicode(query % tuple(args), self.charset)
> TypeError: not all arguments converted during string formatting
>
> Can anyone point me in the direction of how I can fix this?
>
> Cheers,
>
> Tim.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group
> athttp://groups.google.com/group/django-users?hl=en.

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#3 April 23, 2010 21:44:30

I.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


On Apr 23, 1:59 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:
> Hello.
>
> This code works fine:
>
>  >>> import cx_Oracle
>  >>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort),
> lDatabaseName)
>  >>> lConnectString = "%s/%...@%s" % (lDatabaseUsername,
> lDatabasePassword, lDsn)
>  >>> lConnection = cx_Oracle.connect(lConnectString)
>  >>> cursor = lConnection.cursor()
>  >>> lOutput = cursor.var(cx_Oracle.STRING)
>  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
>  >>> print lOutput
> <cx_Oracle.STRING with value 'N'>
>  >>> print lOutput.getvalue()
> N
>
> However, if I change this to get the connection from Django, it all
> falls in a big heap:
>
>  >>> from django.db import connection
>  >>> cursor = connection.cursor()
>  >>> import cx_Oracle
>  >>> lOutput = cursor.var(cx_Oracle.STRING)
>  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
> Traceback (most recent call last):
>    File "<console>", line 1, in <module>
>    File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py",
> line 19, in execute
>      return self.cursor.execute(sql, params)
>    File "/web/djangocourse\django\db\backends\oracle\base.py", line 435,
> in execute
>      query = convert_unicode(query % tuple(args), self.charset)
> TypeError: not all arguments converted during string formatting
>
> Can anyone point me in the direction of how I can fix this?
>
> Cheers,
>
> Tim.


Hi Tim,

Django cursors universally use the 'format' dbapi paramstyle rather
than the 'named' style natively used by cx_Oracle . To convert
your query, replace the parameter markers with %s and pass the
parameters as a list rather than a dictionary.

If you instead want to work with the underlying cx_Oracle cursor
directly, you can access that as cursor.cursor. However, this is not
documented API, so don't expect it to be stable. I'm also not sure
whether it works at all with the sqlite3 backend.

HTH,
Ian

http://docs.djangoproject.com/en/1.1/topics/db/sql/#connections-and-cursors--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#4 April 23, 2010 21:45:09

Skylar S.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


Ah, I guess it's only %s and strings for execute? At anyrate, the
third argument to connection.execute(self, query, parms=None), params,
is a list of strings, each member of the list should have a '%s'
placeholder in the query. The string-formatting link is a not really
germane ..

On Apr 23, 4:32 pm, Skylar Saveland <skylar.savel...@gmail.com> wrote:
> Looking at the source for execute on django-trunk in
> django.db.backends.oracle
>
> If you have a param, you will have an arg for the query (type:
> string).
>
>http://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-...
>
> ... your params should be a list of things to interpolate into the
> query string, yes?
>
>http://docs.python.org/library/stdtypes.html#string-formatting>
> On Apr 23, 3:59 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:
>
>
>
>
>
> > Hello.
>
> > This code works fine:
>
> >  >>> import cx_Oracle
> >  >>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort),
> > lDatabaseName)
> >  >>> lConnectString = "%s/%...@%s" % (lDatabaseUsername,
> > lDatabasePassword, lDsn)
> >  >>> lConnection = cx_Oracle.connect(lConnectString)
> >  >>> cursor = lConnection.cursor()
> >  >>> lOutput = cursor.var(cx_Oracle.STRING)
> >  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
> >  >>> print lOutput
> > <cx_Oracle.STRING with value 'N'>
> >  >>> print lOutput.getvalue()
> > N
>
> > However, if I change this to get the connection from Django, it all
> > falls in a big heap:
>
> >  >>> from django.db import connection
> >  >>> cursor = connection.cursor()
> >  >>> import cx_Oracle
> >  >>> lOutput = cursor.var(cx_Oracle.STRIN
> >  >>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
> > Traceback (most recent call last):
> >    File "<console>", line 1, in <module>
> >    File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py",
> > line 19, in execute
> >      return self.cursor.execute(sql, params)
> >    File "/web/djangocourse\django\db\backends\oracle\base.py", line 435,
> > in execute
> >      query = convert_unicode(query % tuple(args), self.charset)
> > TypeError: not all arguments converted during string formatting
>
> > Can anyone point me in the direction of how I can fix this?
>
> > Cheers,
>
> > Tim.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Django users" group.
> > To post to this group, send email to django-us...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > django-users+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/django-users?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group
> athttp://groups.google.com/group/django-users?hl=en.

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#5 April 23, 2010 21:50:15

I.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


On Apr 23, 2:44 pm, Ian <ian.g.ke...@gmail.com> wrote:
> Django cursors universally use the 'format' dbapi paramstyle rather
> than the 'named' style natively used by cx_Oracle .  To convert
> your query, replace the parameter markers with %s and pass the
> parameters as a list rather than a dictionary.
>
> If you instead want to work with the underlying cx_Oracle cursor
> directly, you can access that as cursor.cursor.  However, this is not
> documented API, so don't expect it to be stable.  I'm also not sure
> whether it works at all with the sqlite3 backend.

One other note: if you just need to call a SP in the database, then
you can use cursor.callproc or cursor.callfunc and skirt this entire
issue.

HTH,
Ian

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#6 April 23, 2010 21:56:30

I.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


On Apr 23, 2:45 pm, Skylar Saveland <skylar.savel...@gmail.com> wrote:
> Ah, I guess it's only %s and strings for execute?  At anyrate, the
> third argument to connection.execute(self, query, parms=None), params,
> is a list of strings, each member of the list should have a '%s'
> placeholder in the query.  The string-formatting link is a not really
> germane ..

Despite the '%s', it's purely a placeholder syntax, not a formatting
system. The params don't have to be strings, because they don't
actually get interpolated into the query at any point. Ints, floats,
datetime objects, and cursor variables all work.

Ian

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#7 April 25, 2010 18:13:49

Tim S.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


Ian wrote:On Apr 23, 1:59 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:Hello.

This code works fine:

>>> import cx_Oracle
>>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort),
lDatabaseName)
>>> lConnectString = "%s/%...@%s" % (lDatabaseUsername,
lDatabasePassword, lDsn)
>>> lConnection = cx_Oracle.connect(lConnectString)
>>> cursor = lConnection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
>>> print lOutput
<cx_Oracle.STRING with value 'N'>
>>> print lOutput.getvalue()
N

However, if I change this to get the connection from Django, it all
falls in a big heap:

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> import cx_Oracle
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py",
line 19, in execute
return self.cursor.execute(sql, params)
File "/web/djangocourse\django\db\backends\oracle\base.py", line 435,
in execute
query = convert_unicode(query % tuple(args), self.charset)
TypeError: not all arguments converted during string formatting

Can anyone point me in the direction of how I can fix this?

Cheers,

Tim.Hi Tim,

Django cursors universally use the 'format' dbapi paramstyle rather
than the 'named' style natively used by cx_Oracle . To convert
your query, replace the parameter markers with %s and pass the
parameters as a list rather than a dictionary.

If you instead want to work with the underlying cx_Oracle cursor
directly, you can access that as cursor.cursor. However, this is not
documented API, so don't expect it to be stable. I'm also not sure
whether it works at all with the sqlite3 backend.

HTH,
Ian

http://docs.djangoproject.com/en/1.1/topics/db/sql/#connections-and-cursorsThanks all, but I'm getting an ORA error now:

>>> import cx_Oracle
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN %s := 'N'; END;", )
Traceback (most recent call last):
File "<console>", line 1, in <module>File "/home/...snip.../django/db/backends/oracle/base.py", line 349,in executeraise e
DatabaseError: ORA-06550: line 1, column 23:PLS-00103: Encountered the symbol "end-of-file" when expecting one ofthe following:; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.

Clues anyone?

Cheers,

Tim.

--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#8 April 26, 2010 16:44:40

I.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


On Apr 25, 11:13 am, Tim Sawyer <list.dja...@calidris.co.uk> wrote:

>  >>> import cx_Oracle
>  >>> from django.db import connection
>  >>> cursor = connection.cursor()
>  >>> lOutput = cursor.var(cx_Oracle.STRING)
>  >>> cursor.execute("BEGIN %s := 'N'; END;", )
> Traceback (most recent call last):
>    File "<console>", line 1, in <module>
>    File "/home/...snip.../django/db/backends/oracle/base.py", line 349,
> in execute
>      raise e
> DatabaseError: ORA-06550: line 1, column 23:
> PLS-00103: Encountered the symbol "end-of-file" when expecting one of
> the following:
>     ; <an identifier> <a double-quoted delimited-identifier>
> The symbol ";" was substituted for "end-of-file" to continue.

The trailing semicolon is automatically stripped by the backend, which
is necessary for most of the queries it runs but incorrect for PL/
SQL. The easiest fix is to add a space or a second semicolon at the
end. Or you can do what Django does internally, which is to terminate
the PL/SQL block with a forward-slash on its own line after the
semicolon, as if the block were to be entered into sqlplus. In that
case the forward-slash will be stripped, but the semicolon will be
left alone.

HTH,
Ian

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#9 April 27, 2010 21:36:35

Tim S.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


Ian wrote:On Apr 25, 11:13 am, Tim Sawyer <list.dja...@calidris.co.uk> wrote:>>> import cx_Oracle
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN %s := 'N'; END;", )
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/...snip.../django/db/backends/oracle/base.py", line 349,
in execute
raise e
DatabaseError: ORA-06550: line 1, column 23:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the following:
; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.The trailing semicolon is automatically stripped by the backend, which
is necessary for most of the queries it runs but incorrect for PL/
SQL. The easiest fix is to add a space or a second semicolon at the
end. Or you can do what Django does internally, which is to terminate
the PL/SQL block with a forward-slash on its own line after the
semicolon, as if the block were to be entered into sqlplus. In that
case the forward-slash will be stripped, but the semicolon will be
left alone.

HTH,
IanHmm, nearly. That gets around the error, but the return value isn'tpopulated.>>> import cx_Oracle
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN %s := 'N'; END; ", )
>>> print lOutput
<cx_Oracle.STRING with value None>
>>> print lOutput.getvalue()
None
>>>Any more clues, or am I just going to have to stick with creating aspecific Oracle connection to do this?Cheers,

Tim.

--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#10 April 28, 2010 18:34:31

I.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Oracle cursor.execute problem


On Apr 27, 2:36 pm, Tim Sawyer <list.dja...@calidris.co.uk> wrote:
> Hmm, nearly.  That gets around the error, but the return value isn't
> populated.
>
>  >>> import cx_Oracle
>  >>> from django.db import connection
>  >>> cursor = connection.cursor()
>  >>> lOutput = cursor.var(cx_Oracle.STRING)
>  >>> cursor.execute("BEGIN %s := 'N'; END; ", )
>  >>> print lOutput
> <cx_Oracle.STRING with value None>
>  >>> print lOutput.getvalue()
> None
>  >>>
>
> Any more clues, or am I just going to have to stick with creating a
> specific Oracle connection to do this?

That would be a bug. I've just checked in a fix, which will be
included in Django 1.2 and the next 1.1.X release. If you're not
willing to use trunk or wait for the release, my recommendation would
be to use cursor.callfunc if possible. Otherwise, you can get the
cx_Oracle cursor from cursor.cursor and use that directly.

Ian

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

Board footer

Moderator control

Enjoy the 15th of December
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support