Setting Data Frame Column Names with Data Frame includes extra characters: ('ColumnName',)-CodePudding

I've got a python script set to pull data and column names from a Pervasive PSQL database, and it then creates the table and records in MS SQL. I'm creating data frames for the data and for the column names, then renaming the the data's column names from the column name date frame.

However, when the table is created in MS SQL the column names come in as = ('ColumnName',)

The desired column names would not have ('',) and should read as = ColumnName

Below is the code i'm using to get here. Any help on formatting the column names to not include those extra characters would be very helpful!

'''
Start - Pull Table Data
'''
conn = pyodbc.connect(conn_str, autocommit=True)
cursor = conn.cursor()
statement = 'select * from ' db_tbl_name
stRows = cursor.execute(statement)
df_data = pandas.DataFrame((tuple(t) for t in stRows))
df_data = df_data.applymap(str)
'''
End - Pull Table Data
'''

'''
Start - Pull Column Names
'''
conn = pyodbc.connect(conn_str, autocommit=True)
cursor_col = conn.cursor()
statement_col = "select CustomColumnName from " db_col_tbl_name " where CustomTableName = '" db_tbl_name "' and ODBCOptions > 0 order by FieldNumber"
stRows_col = cursor_col.execute(statement_col)
df_col = pandas.DataFrame((tuple(t) for t in stRows_col))

'''
End - Pull Column Names
'''

'''
Start - Add Column Names to Table data (df_data)
'''

df_data.columns = df_col

'''
End - Add Column Names to Table data (df_data)
'''

'''
Start - Create a sqlalchemy engine
'''

params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                 "SERVER=Server;"
                                 "DATABASE=DB;"
                                 "UID=UID;"
                                 "PWD=PWD;")

engine = sqlalchemy.create_engine("mssql pyodbc:///?odbc_connect={}".format(params))

'''
End - Create a sqlalchemy engine
'''



'''
Start - Create sql table and rows from sqlalchemy engine
'''

df_data.to_sql(name=db_tbl_name, con=engine, if_exists='replace')

'''
End - Create sql table and rows from sqlalchemy engine
'''

CodePudding user response：

This worked for me and should resolve the issue. Change.

df_col = pandas.DataFrame((tuple(t) for t in stRows_col))

df_col=[]
for row in stRows_col:
    df_col.append(row[0])

Pyodbc would be moving the data it captures into pyodbc objects. The type(stRows_col) would yield <class 'pyodbc.Cursor'> and row would give you a <class 'pyodbc.Row'>. You can get the value for the pyodbc.Row by using row[0]