alter VARCHAR column to TEXT under Oracle

[Ofir Herzas]

Changing a VARCHAR column to CLOB (TEXT) raises the following exception 
under Oracle:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-22858: invalid 
alteration of datatype
 [SQL: 'ALTER TABLE tab MODIFY col CLOB']


While this is an Oracle fault, it would be nice if the abstraction layer 
took care of it.

The proposed solution that I found was to add a new clob column, copy the 
data, remove the old column and rename

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] can't read CLOB columns

[Ofir Herzas]

Sqlalchemy 1.0.6

Trying to issue a simple select on a CLOB column, throws the following 
exception:

Traceback (most recent call last):
  File "/opt/5.3/server/handlers/orm.py", line 81, in select

result = query.all()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2399, in all
return list(self)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"
, line 84, in instances
util.raise_from_cause(err)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/compat.py"
, line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"
, line 65, in instances
fetch = cursor.fetchall()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1243, in fetchall
row = self.fetchone()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1013, in fetchone
self.cursor, self.context)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1344, in _handle_dbapi_exception
util.reraise(*exc_info)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1006, in fetchone
return self.process_rows([row])[0]
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 929, in process_rows
for row in rows]
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1204, in __init__
row[index] = processor(row[index])
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py"
, line 366, in process
return value.read()
AttributeError: 'str' object has no attribute 'read'


Is there any way around it? (other than not using Text columns)

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: alter VARCHAR column to TEXT under Oracle

[Mike Bayer]

On 11/08/2015 05:42 AM, Ofir Herzas wrote:
> Changing a VARCHAR column to CLOB (TEXT) raises the following exception
> under Oracle:
> 
> |
> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-22858:invalid
> alteration of datatype
>  [SQL:'ALTER TABLE tab MODIFY col CLOB']
> 
> |
> 
> While this is an Oracle fault, it would be nice if the abstraction layer
> took care of it.
> 
> The proposed solution that I found was to add a new clob column, copy
> the data, remove the old column and rename

Alembic offers a rough version of this feature in the form of "batch
migrations", but that involves a whole table copy.

Recipes like adding new columns and copying data can be achieved using
custom directives, see
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects
for an example of how to make new directives.  I will gladly accept
documentation illustrating a recipe for this behavior.



> 
> -- 
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
> .
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] can't read CLOB columns

[Mike Bayer]

On 11/08/2015 05:58 AM, Ofir Herzas wrote:
> Sqlalchemy 1.0.6
> 
> Trying to issue a simple select on a CLOB column, throws the following
> exception:
> 
> |
> Traceback(most recent call last):
>   File"/opt/5.3/server/handlers/orm.py",line 81,inselect
> 
> result =query.all()
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/query.py",line
> 2399,inall
> returnlist(self)
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",line
> 84,ininstances
> util.raise_from_cause(err)
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/compat.py",line
> 199,inraise_from_cause
> reraise(type(exception),exception,tb=exc_tb)
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",line
> 65,ininstances
> fetch =cursor.fetchall()
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",line
> 1243,infetchall
> row =self.fetchone()
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",line
> 1013,infetchone
> self.cursor,self.context)
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line
> 1344,in_handle_dbapi_exception
> util.reraise(*exc_info)
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",line
> 1006,infetchone
> returnself.process_rows([row])[0]
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",line
> 929,inprocess_rows
> forrow inrows]
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py",line
> 1204,in__init__
> row[index]=processor(row[index])
>  
> File"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py",line
> 366,inprocess
> returnvalue.read()
> AttributeError:'str'objecthas noattribute 'read'

the SQL expression in question here is not a CLOB, you are likely
reading from a VARCHAR field and/or SQL expression.  If Oracle were
returning a value that it considers to be a CLOB value, cx_oracle would
return it as a cx_oracle.LOB object which is not the case here.




> |
> 
> 
> Is there any way around it? (other than not using Text columns)
> 
> -- 
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> .
> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy: Database Access Using Python - Developer's Library book

[Ken Lareau]

On Thu, Nov 5, 2015 at 7:34 AM, Mike Bayer  wrote:

>
>
> On 11/03/2015 06:55 PM, Ken Lareau wrote:
>
>> I came across this during a search, and in the four different sites
>> I've checked, I've seen four different release dates varying from
>> 2008 to 2018, for example:
>>
>>
>> http://www.amazon.com/SQLAlchemy-Database-Access-Developers-Library/dp/0132364670
>>
>> (which has 2018 for the release date).  So I'm curious...
>>
>> Is this a very old book (2008) or a book that has several years
>> to be released (2018)?  Does anyone actually know which of
>> the various dates are most likely correct? :)  (I've seen a few
>> around May-June of 2016, which seems the most likely, but...)
>>
>> Given one of the authors of the book, I'm hoping I might be able
>> to find out more here. :)
>>
>
>
> it's unfortunately a non-existent book for the time being and I'm not able
> to make any predictions when that might change.


Understood, and thanks for the response.

-- 
Ken Lareau

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] confirming SQLite pragma settings?

[James Hartley]

I have successfully installed SQLAlchemy 1.0.9, & can enable foreign key
constraint support on each connection by hooking the event as specified in
the following:

http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html

SQLite also allows pragma settings to be queried in the command-line shell
as:

sqlite> pragma foreign_keys;

It would be good if I could log the setting too.  Is it possible to query
the connection for such information?

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

RE: alter VARCHAR column to TEXT under Oracle

[Ofir Herzas]

Thanks Mike,
I was hoping for a under-the-hood solution rather than a recipe since it's a 
must for using Oracle.
Nevertheless, if I get around it, I will provide the recipe (better to have 
something...)

In the meanwhile, I opted to stay with VARCHAR ...

Thanks again,
Ofir


-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Sunday, November 8, 2015 20:25
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: alter VARCHAR column to TEXT under Oracle



On 11/08/2015 05:42 AM, Ofir Herzas wrote:
> Changing a VARCHAR column to CLOB (TEXT) raises the following exception
> under Oracle:
> 
> |
> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-22858:invalid
> alteration of datatype
>  [SQL:'ALTER TABLE tab MODIFY col CLOB']
> 
> |
> 
> While this is an Oracle fault, it would be nice if the abstraction layer
> took care of it.
> 
> The proposed solution that I found was to add a new clob column, copy
> the data, remove the old column and rename

Alembic offers a rough version of this feature in the form of "batch
migrations", but that involves a whole table copy.

Recipes like adding new columns and copying data can be achieved using
custom directives, see
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects
for an example of how to make new directives.  I will gladly accept
documentation illustrating a recipe for this behavior.



> 
> -- 
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
> .
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/rDqCsRIDfh4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.