Example to pass array or list to mssql stored procedure with table valued parameters03.08.18

1.
use pytds

2.
create a datatype on the mssql server like
use mydb
CREATE TYPE string_list AS TABLE (id VARCHAR(255));

3.
create a generator object from a list/array 
def rows_gen(self,styles):
    for	i in styles:
      yield (i,)

4.
create the tvp object(table valued parameters)
with the table type and the generator object
tvp = pytds.TableValuedParam(type_name='dbo.string_list',rows=rows_gen(list))

5.
call the stored procedure with the tvp as parameter
c.callproc('report_get',(tvp,))

6.
in your stored procedure receive it like
@styles string_list readonly

example cutout:

import pytds

def rows_gen(self,styles):
    for	i in styles:
      yield (i,)

def main(self,request):
    """                                                                                                                                                                                                                                                                                                                                                    
    http://127.0.0.1:8050/v1.0/report                                                                                                                                                                                                                                                                                                            
    use sl                                                                                                                                                                                                                                                                                                                                                 
    CREATE TYPE string_list AS TABLE (id VARCHAR(255));                                                                                                                                                                                                                                                                                                    
    """
    para    = request.rel_url.query
    xformat = para['f']      if 'f'      in para else 'json'
    styles  = para['styles'] if 'styles' in para else 'JNO90-OP-0.8-6.5-2.35-LSI'

styles = styles.split('\r\n')
    r = self.rows_gen(styles)
    tvp = pytds.TableValuedParam(type_name='dbo.string_list',rows=r)
    conn = pytds.connect(
      conf['host'],
      conf['database'],
      conf['user'],
      conf['password'],
      as_dict=True)

conn = dbs.open_mssql2(request.app['config']['mssql_local'])

c = conn.cursor()
    c.callproc('planning_report_get',(tvp,))
    orders = []

https://github.com/denisenkom/pytds/search?q=rows_gen%28%29&unscoped_q=rows_gen%28%29

Anonymous

03.08.18

https://github.com/denisenkom/pytds

Anonymous

Example to pass array or list to mssql stored procedure with table valued parameters03.08.18

Information Epoch 1713617473