1

I have to insert records in a db table with more than 50 columns. I know how to call stored procedure with parameters in Python like this:

 self.__createConnection()
    proc= "{call faftech..Hns_HolidayCheck (?)}"
    ls=[]
    ls.append(dateTime)
    param= ls
    self.__cursor.execute(proc, param)
    val= self.__cursor.fetchone()
    self.__closeConnection()

I want to know the best way to insert the data in the table with so many columns. I am using pypyOdbc.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vivek Mishra
  • 1,772
  • 1
  • 17
  • 37
  • 1
    In my opinion, there is nothing wrong with simply passing the parameters the way you describe. The only small improvement that I can suggest is to name your parameters. E.g. `proc = "EXEC faftech..Hns_HolidayCheck @some_date = ?"`. This will minimize the risk of mapping mistakes. – Gert-Jan Feb 06 '20 at 21:16

2 Answers2

3

There's sadly no easy way to do this. The best way is to provide the table_name, column_names, and values and build up the sql by yourself:

def insert_row(self, table_name, column_names, values):
    params = [ '?' ] * len(values)
    params = ','.join(params)
    column_names = [ f'[{x}]' for x in column_names ]
    column_names = ','.join(column_names)
    sql = f'insert into [{table_name}] ({column_names}) values ({params})'
    conn = self.getConnection()
    with conn.cursor() as cursor:
        conn.execute(sql, values)

Alternatively, you can send in a dict of column_names => values:

def insert_row(self, table_name, values):
    params = [ '?' ] * len(values)
    params = ','.join(params)   
    q, column_names = [], []
    for column_name, value in values.items():
        q.append(value)
        column_names.append(f'[{column_name}]')
    column_names = ','.join(column_names)
    sql = f'insert into [{table_name}] ({column_names}) values ({params})'
    conn = self.getConnection()
    with conn.cursor() as cursor:
        conn.execute(sql, q)

Alternatively, you can use django which will make this much easier to manage.

2ps
  • 15,099
  • 2
  • 27
  • 47
0

Since the columns are more than 10 it is good to create a table type in SQL and pass a table as a parameter to the stored procedure

Sadwik
  • 37
  • 1
  • 8