Oracle with Python - How to check if column already exists?

Question

I'm trying to create an Oracle table from a list of attributes with python. Sadly, I have multiple attributes with the same name thus I can't add them to the table. Also I don't want my program to stop because of that. Now I'm trying with this solution:

connection = cx_Oracle.connect('user/password')
cursor = connection.cursor()

if not tableExists(connection, 'TableName'):
first_column_name = next(iter(attributes), None)
query_table = 'CREATE TABLE TableName ("{}" VARCHAR2(255))'.format(first_column_name)
cursor.execute(query_table)


for attribute in attributes[1:]:
    query_column= '''
    DECLARE
        v_column_exists number := 0;  
    BEGIN
        Select count(*) into v_column_exists
            from user_tab_cols
            where upper(column_name) = "{}"
            and upper(table_name) = 'TableName';

        if (v_column_exists = 0) then
            execute immediate 'alter table TableName add ("{}" VARCHAR2(255)))';
        end if;
    end;
    '''.format(attribute, attribute)

    cursor.execute(query_column)

I've pasted the long query code from this answer. The table is created with the first attribute as intended but as I would start to add more columns I get:

Traceback (most recent call last):
File "main.py", line 52, in <module>
cursor.execute(query_column)
cx_Oracle.DatabaseError: ORA-06550: line 7, column 41:
PL/SQL: ORA-00904: "Order count [A221]": invalid identifier
ORA-06550: line 5, column 9:
PL/SQL: SQL Statement ignored

What am I missing?

Anthony Tuininga · Accepted Answer · 2019-03-19 00:33:58Z

2

I'd suggest simply building up the create table statement instead of building the table and then altering it to add columns to it!

You can get rid of duplicates in a list by using the following code:

listWithoutDups = list(dict.fromkeys(listWithDups))

Then, you can build your statement as follows:

columns = ['"%s" varchar2(255)' % n for n in listWithoutDups]
sql = "create table SomeTableName (%s)" % ",".join(columns)
cursor.execute(sql)

You'll note I included double quotes around the column names -- that's necessary if you want to create columns that don't follow Oracle standards (include special characters, spaces, etc.) but be aware that also makes the names case sensitive and you will need to specify quotes as well when you perform any operation on the table.

answered Mar 19, 2019 at 0:33

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MattSom Over a year ago

Many thanks, Good Sir! Yeah, this morning I've realized I should make everything ready before creating the whole table in one command. Thanks for the dict.fromkeys tip, I've implemented it with a longer, more tedious logic, but this one is more elegant.

Anthony Tuininga Over a year ago

Glad to be of help!

Collectives™ on Stack Overflow

Oracle with Python - How to check if column already exists?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related