Need help with flags

[j1yuan]

I am trying to use the copy_to() function, and I don't know how to supply the right values in the "flags" field in preparation of the Column object. Here is my code:

col_brandid  = Column(name='BrandId',  type=BigIntType, flags=0)
col_lenderid = Column(name='LenderId', type=BigIntType, flags=0)
col_namekey  = Column(name='NameKey',  type=NVarCharType(size=20), flags=0)
col_namekey2 = Column(name='NameKey2', type=NVarCharType(size=20), flags=0)
col_startdate = Column(name='StartDate', type=SmallDateTimeType, flags=0)
col_enddate   = Column(name='EndDate',   type=SmallDateTimeType, flags=0)
col_enrollmentcode = Column(name='EnrollmentCode', type=NVarCharType(size=10), flags=0)
col_recordjson = Column(name='RecordJson', type=VarCharMaxType, flags=0)

columns_lst=[col_brandid, col_lenderid, col_namekey, col_namekey2, col_startdate, col_enddate, col_enrollmentcode, col_recordjson]

with open(file_path, "r") as file_stream:
    cur.copy_to(file_stream, 'PrescreenRecords_Stage', sep='|', columns=columns_lst) 

The error message is:

Traceback (most recent call last):
  File "test_df_bulk.py", line 70, in <module>
    cur.copy_to(file_stream, 'PrescreenRecords_Stage', sep='|', columns=columns_lst)
  File "/home/s_dev_infsvc/.local/lib/python3.6/site-packages/pytds/__init__.py", line 982, in copy_to
    for col in metadata)
  File "/home/s_dev_infsvc/.local/lib/python3.6/site-packages/pytds/__init__.py", line 982, in <genexpr>
    for col in metadata)
TypeError: get_declaration() missing 1 required positional argument: 'self'

I wonder if anyone can show me a few examples to set the right values in the "flags" field?

[takoau]

@j1yuan You should use pytds.tds_types.BigIntType() instead of INT8TYPE, and SmallDateTimeType() instead of DATETIM4TYPE, etc.

[takoau]

@j1yuan and for flag, you may use the following values: fNullable = 1 fReadWrite = 8

[j1yuan]

I need the columns to be "not nullable". It feels that the fNullable flag would allow null. How can I specify a "not nullable" flag?

[takoau]

@j1yuan if you check https://github.com/denisenkom/pytds/blob/0e6114d9c1f367c625206b41abfd2760b9856bd7/src/pytds/__init__.py#L981 and https://github.com/denisenkom/pytds/blob/0e6114d9c1f367c625206b41abfd2760b9856bd7/src/pytds/__init__.py#L1001, you will see that flag is not in use at all. Anyways it's an INSERT BULK statement why column flags required? https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16

[takoau]

@j1yuan if you really want to set the column flags, here is the reference

fNullable is a bit flag. Its value is 1 if the column is nullable.

fNullable is at the least significant position so you may put 0 for NOT NULL, and and identity column is 0 | fIdentity

[denisenkom]

Added some information here https://python-tds.readthedocs.io/en/latest/pytds.html#pytds.tds_base.Column