CODEWITHSUNDEEP
pythonoracle-databasesqlalchemysqldatatypesvarchar2
Henrik Poulsen
Henrik Poulsen

Reputation: 995

Set Oracle VARCHAR2 length i Byte using SQLALCHEMY

I'm currently moving some data around using python and pandas.

Here I am getting a bit stuck on the data types i can read in the existing database being in Byte and SQL Alchemy definition being in Char

For example

from sqlalchemy.dialects.oracle import VARCHAR2

dtyp = {'COLUMN': VARCHAR2(12)}

df.to_sql(<tableName>, engine.connect(), schema=<schema>, if_exists='replace', index=False, chunksize=1000, dtype=table['dtyp'])

This will create a column on my target db with type VARCHAR2(12 Char)

How can I tell sqlalchemy to create the column with type VARCHAR(12 Byte)?

Looking through the documentation I see no obvious arguments on VARCHAR2 that I can set

EDIT I figured out how to create my own custom type

class BYTE_VARCHAR2(types.UserDefinedType):
def __init__(self, precision = 8):
    self.precision = precision

def get_col_spec(self, **kw):
    return "VARCHAR2(%s Byte)" % self.precision

def bind_processor(self, dialect):
    def process(value):
        return value
    return process

def result_processor(self, dialect, coltype):
    def process(value):
        return value
    return process

Upvotes: 2

Views: 1363

Answers (2)

Littlefoot
Littlefoot

Reputation: 143083

I don't know tools you use, but - as far as Oracle is concerned - you'd specify CHAR or BYTE while creating a table, e.g.

SQL> create table test
  2    (col_1    varchar2  (12 char),
  3     col_2    varchar2  (12 byte)
  4    );

Table created.

See if you can do the same.

Upvotes: 1

Padders
Padders

Reputation: 276

Firstly I am not familiar with sqlalchemy, so if an option exists within that framework I would not know of it.

However this is controllable at the oracle database session level by setting NLS parameter 'nls_length_semantics', e.g.

ALTER SESSION SET nls_length_semantics = BYTE

I would expect you to be able to execute this statement in your session prior to creating the table.

Upvotes: 1

Related Questions