Adding a new column to the table in a database using sqlalchemy, pyodbc

Question

cnx=sqlalchemy.create_engine("mssql+pyodbc://Omnius:[email protected]:1433/Basis?driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0")
cnx1 = pyodbc.connect('driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0;server=SRVWUDEN0835;database=Basis;uid=Omnius; pwd=MainBrain1')
sqlquery = "select top 10 TXN_KEY,SEND_AGENT,PAY_AGENT from Pretty_Txns"
cursor = cnx1.cursor()
df = pd.read_sql(sqlquery,cnx)
model_columns = df.columns.tolist()
 db_columns  = cursor.execute("select TXN_KEY,SEND_AGENT from result").fetchall()
 columns = [column[0] for column in cursor.description]
  to_create =list(set(model_columns) -set(columns))
  for c in to_create:
        a = df[c]
        sql = DDL('ALTER TABLE %s ADD column %s  %s' % (result,a,String(9)))
         cnx.execute(sql)

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'column'. (156) (SQLExecDirectW)") [SQL: u"ALTER TABLE result ADD column ['API352676', 'AED002782', 'ACB020203', 'ASE094882', 'AII071196', 'AHX012817', 'AED000438', 'AEL051943', 'ADJ031448', 'APM033226']  VARCHAR(9)"]

Above code shows how to add a new column to a table in a database using sqlalchemy and pyodbc.For the most part it works fine but fails at the last step.

MaxU - stand with Ukraine · Accepted Answer · 2016-05-31 18:33:30Z

3

you have incorrectly built your alter table ... add column ... SQL.

It should look like as follows (for single column):

ALTER TABLE table_name ADD COLUMN column_name data_type(precision);

or for multiple columns:

ALTER TABLE table_name ADD COLUMN (column1_name data_type(precision), column2_name data_type(precision), column3_name data_type(precision));

I would recommend to add all columns in one command as it might lock dictionary structures for a short time. And i would also recommend you NOT to do it in the application, but do it manually once if it's possible. If it's not possible/applicable you can change your code as follows:

'ALTER TABLE {} ADD column ({})'.format(result, ', '.join(['{} {}'.format(c, String(9)) for c in to_create]))

answered May 31, 2016 at 18:33

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Collectives™ on Stack Overflow

Adding a new column to the table in a database using sqlalchemy, pyodbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related