Oracle - How to get VARCHAR2 columns to use BYTE and not CHAR?

[Matthew Moisen]

(Cross posting from Stackoverflow)

It looks like SQLAlchemy defaults to creating VARCHAR2 columns as CHAR. How can I have it create with BYTE instead?

from sqlalchemy import MetaData, Column, String
from sqlalchemy.ext.declarative import declarative_base

metadata = MetaData()
Base = declarative_base(metadata=metadata)

class Foo(Base):
    __tablename__ = 'foo'
    name = Column(String(10), primary_key=True)

Foo.__table__.create(bind=engine)
 

This creates the following table:

CREATE TABLE XXMD.FOO
(
  NAME  VARCHAR2(10 CHAR)                       NOT NULL
)

Instead, I would like it to create the following:

CREATE TABLE XXMD.FOO
(
  NAME  VARCHAR2(10 BYTE)                       NOT NULL
)

I would prefer to use CHAR, but I'm integrating with a few systems whose table's are BYTE and need my corresponding tables to match theirs.
We also have some tables/code that are reliant on Oracle's data dictionary which use BYTE instead of CHAR.

I've tried using sqlalchemy.dialects.oracle.VARCHAR2, but it also defaults to CHAR.

Best regards,

Matthew Moisen

---

I'm using SQLAlchemy 1.1.5, cx_Oracle 5.3, an Oracle 12CR1 Client, and an Oracle 12CR1 DB.

[Mike Bayer]

On Wed, Aug 2, 2017 at 6:54 PM, Matthew Moisen <mkmo...@gmail.com> wrote:
> (Cross posting from Stackoverflow)
>
> It looks like SQLAlchemy defaults to creating VARCHAR2 columns as CHAR. How
> can I have it create with BYTE instead?

the most immediate approach would be to write your own compilation
function for oracle varchar, using @compiles - see
http://docs.sqlalchemy.org/en/rel_1_1/core/custom_types.html#overriding-type-compilation
for the general idea. Alternatively you can make a subclass of
VARCHAR or String and then use a similar @compiles approach.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.