Expected unicode, got quoted_name

[TrentGlover]

Error returned when calling parquetobj.table_to_parquet(parq_file_name, row['schema_name'], row['table_name'] , sql, db_engine, batch_size)

error is

Traceback (most recent call last):
  File "C:\Users\a093789\AppData\Roaming\Python\Python310\site-packages\fastparquet\writer.py", line 1499, in write_thrift
    return f.write(obj.to_bytes())
TypeError: Expected unicode, got quoted_name
TypeError: Expected unicode, got quoted_name
Exception ignored in: 'fastparquet.cencoding.write_list'

Code:

from tempfile import NamedTemporaryFile
import pyodbc, fastparquet
from datetime import date
import pandas as pd
from sqlalchemy import Table, create_engine, schema

if name == 'main':

sender = 'bi-pssis@mwiah.com'
receivers = ['tglover@mwiah.com', '2082833572@tmomail.net']
port = 25

constr = "DRIVER={SQL Server Native Client 11.0};SERVER=bi-prtr;DATABASE=archive;Trusted_Connection=yes;"

parqfilepath = 'C:\\appl\Python\Data\Parquet\\'
parqfilename = 'raw-cna-mwia-archive-archive_{}-interval_{}_{}_dumpedon_{}.parquet'
parqerrorfilepath = '\\\\cake\\DWBackup\EAP-Archive\\data\\parquet\\'
batch_size = 1000000

tempfile = NamedTemporaryFile(mode='w', delete=False, newline='', dir='./')
number_of_rows = 0

continue_processing: bool = True
todays_date = date.today()

line_count = 1
from_date = date.today()
to_date = date.today()

try:
    conn = pyodbc.connect(constr)
except:
    print("{c} is not working".format(c=constr))

cursor = conn.cursor()
db_engine = create_engine('mssql+pyodbc:///?odbc_connect=' + constr)
sql = 'select * from archive.Ibs.PHOALD WITH (NOLOCK) Where Convert(date, Transaction_time) > Convert(date, \'2019-05-20\') and Convert(date, Transaction_time) <= Convert(date, \'2019-06-20\')'
print(f"{sql}")

parq_file_name = 'testfile.parquet'

db_metadata = schema.MetaData(bind=db_engine, schema='IBS')

db_table = Table('PHOALD', db_metadata, autoload=True)

column_dict = dict()
for column in db_table.columns:

     column_dict[column.name] = str

result = db_engine.execute(sql)

row_batch = result.fetchmany(size=batch_size)
iteration = 1
append = False

while (len(row_batch) > 0):

     b_df = pd.DataFrame(row_batch, columns=column_dict, dtype=str)

     b_df = b_df.astype(dtype=column_dict)

     if not b_df.empty:
         fastparquet.write(parqfilename, b_df, write_index=False, compression='GZIP', append=append)
         append = True
         row_batch = result.fetchmany(size=batch_size)
         iteration = iteration + 1

• Dask version: 2022.9.1
• Python version: 3.10
• Operating System: Windows 10 Enterprise version 10.0.19041 Build 19041
• Install method (conda, pip, source): pip

[martindurant]

That's a lot of code and stuff I'm not familiar with, it would be really useful to try to pare down the reproducer to the minimum required to show the problem.

At a complete guess columns=column_dict in pd.DataFrame() is passing some non-string items derived from the database stuff. You may need explicit str() around these.

[TrentGlover]

restricted to code as much as I could to repeat the problem. Regards, Trent Glover AmerisourceBergen MWI Animal Health Data Engineer Work Phone: 208.955.9527 Cell/text: 208.283.3572 3041 W Pasadena Dr. Boise, ID 83705 United States www.mwiah.com United in our responsibility to create healthier futures Confidentiality notice: This electronic mail transmission may contain privileged, confidential and/or protected personal information and is intended only for the review of the party to whom it is addressed. Any unauthorized use or disclosure of the information contained herein may be a violation of applicable law. If you have received this transmission in error, please immediately return it to the sender, delete it and destroy it without reading it. Unintended transmission shall not constitute the waiver of the attorney-client or any other privilege. From: Martin Durant ***@***.***> Sent: Thursday, September 29, 2022 7:33 AM To: dask/fastparquet ***@***.***> Cc: Glover, Trent ***@***.***>; Author ***@***.***> Subject: Re: [dask/fastparquet] Expected unicode, got quoted_name (Issue #810) CAUTION: This email originated from outside of the organization. DO NOT CLICK links or open attachments unless you recognize the sender and know the content is safe. That's a lot of code and stuff I'm not familiar with, it would be really useful to try to pare down the reproducer to the minimum required to show the problem. At a complete guess columns=column_dict in pd.DataFrame() is passing some non-string items derived from the database stuff. You may need explicit str() around these. — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/dask/fastparquet/issues/810*issuecomment-1262291035__;Iw!!I5RyydYb1W3tjTUU!19pPU5SOOMfH4SNWFo6-INpKzV65Tjubm_vb358BXIVq2zEiKA-RGp4FzEyupBGnHOiyD6uR6FxH3sY_AP0aeXBaRfVi3iRacnrM$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/AV3FIZCDRMRRPSNGE6X4BSLWAWLBHANCNFSM6AAAAAAQYHCYAA__;!!I5RyydYb1W3tjTUU!19pPU5SOOMfH4SNWFo6-INpKzV65Tjubm_vb358BXIVq2zEiKA-RGp4FzEyupBGnHOiyD6uR6FxH3sY_AP0aeXBaRfVi3qxIUgmb$>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[TrentGlover]

same error when attempting to turn all columns into strings...

data_query: str = "Select {} from archive.Ibs.PHOALD WITH (NOLOCK) Where Convert(date, Transaction_time) > Convert("
"date, '2019-05-20') and Convert(date, Transaction_time) <= Convert(date, '2019-06-20') "

...

cursor = conn.cursor()
metadata_sql = 'select top 1 * from archive.Ibs.PHOALD WITH (NOLOCK)'
print(metadata_sql)
cursor.execute(metadata_sql)
column_names = ''
field_names = [i[0] for i in cursor.description]
for j in field_names:
    j = 'convert(nvarchar, ' + j + ') as ' + j
    column_names = column_names + ',' + j
column_names = column_names[1:]
print(f"{column_names}")

sql = data_query.format(column_names)
print(f"{sql}")

[martindurant]

A "quoted_name" is entering into your dataframe metadata somewhere, so the task is to find out where. Maybe fastparquet should be converting it to a string internally, but I would bet that requiring a real string is a reasonable expectation in this case.

I'm afraid I don't really know what your code it doing, I don't normally work with DB cursor objects. You might find it easier to use pd.from_sql_query instead, as I suspect it might do the correct conversions for you.

[TrentGlover]

the problem definitely looks like it is native to the fastparquet code. i've called it without modification and it is still throwing the error.

[martindurant]

i've called it without modification and it is still throwing the error.

What did you call exactly? Did you manage to make a dataframe showing this problem without SQL?

[TrentGlover]

The construction and the population of the dataframes are within the __write_parquet function which is called from the table_to_parquet function within the fastparquet code. I have not attempted to build a dataframe outside of this code. I’m new to all of this so I apologize if I don’t get exactly how this works, but I’m assuming that unless I have something useful to add, I really shouldn’t be messing with the code that is part of the offering. Regards, Trent Glover AmerisourceBergen MWI Animal Health Data Engineer Work Phone: 208.955.9527 Cell/text: 208.283.3572 3041 W Pasadena Dr. Boise, ID 83705 United States www.mwiah.com United in our responsibility to create healthier futures Confidentiality notice: This electronic mail transmission may contain privileged, confidential and/or protected personal information and is intended only for the review of the party to whom it is addressed. Any unauthorized use or disclosure of the information contained herein may be a violation of applicable law. If you have received this transmission in error, please immediately return it to the sender, delete it and destroy it without reading it. Unintended transmission shall not constitute the waiver of the attorney-client or any other privilege. From: Martin Durant ***@***.***> Sent: Monday, October 3, 2022 9:26 AM To: dask/fastparquet ***@***.***> Cc: Glover, Trent ***@***.***>; Author ***@***.***> Subject: Re: [dask/fastparquet] Expected unicode, got quoted_name (Issue #810) CAUTION: This email originated from outside of the organization. DO NOT CLICK links or open attachments unless you recognize the sender and know the content is safe. i've called it without modification and it is still throwing the error. What did you call exactly? Did you manage to make a dataframe showing this problem without SQL? — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/dask/fastparquet/issues/810*issuecomment-1265638515__;Iw!!I5RyydYb1W3tjTUU!yDykGC6DR4jSE4osXPBo8SqykzsuoID8OJWsRP-aoSkRphGqbOUaNcfCc-SANS-7zaZSp880UiKnVS3g2PKgo4jg1601nvHnAMbZ$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/AV3FIZD2VOEUR62LJZUZNHDWBL3GZANCNFSM6AAAAAAQYHCYAA__;!!I5RyydYb1W3tjTUU!yDykGC6DR4jSE4osXPBo8SqykzsuoID8OJWsRP-aoSkRphGqbOUaNcfCc-SANS-7zaZSp880UiKnVS3g2PKgo4jg1601nizsvokU$>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[martindurant]

I mean:

     b_df = pd.DataFrame(row_batch, columns=column_dict, dtype=str)
     b_df = b_df.astype(dtype=column_dict)

here both the original columns and the ones supplied in dtype= have keys that came from the SQL driver, and probably responsible for the "quoted_name" in the error. Try converting them to actual str before doing anything with them:

columns = {str(k): v for k, v in columns_dict.items()}

Are the dtypes really all str?