Python - Doubt-transform into Dataframe

Question

good morning! People connected using python in Sql database using pymssql

import pymssql
conn = pymssql.connect(server='nomeservidor', user='usuario', password='senha', 
database='banco')
cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT ROTAID,DESCRICAO FROM ROTAS')
for row in cursor:
    print("%d,%s" % (row['ROTAID'], row['DESCRICAO']))
conn.close()

but he brings me the data in this format below

2,ROTA 02
3,ROTA 03
4,ROTA 04
5,ROTA 05
6,ROTA 06
7,ROTA 07

I would like to turn into a Dataframe table as below, would have as ?

ROTAID  DESCRICAO
2        ROTA 02
3        ROTA 03
4        ROTA 04
5        ROTA 05
6        ROTA 06
7        ROTA 07

I thank you in advance for any help!

Answer 1

The library pandas has a specific method to create a dataframe from a query SQL. The method is the read_sql_query

df = pandas.read_sql_query("SELECT ROTAID,DESCRICAO FROM ROTAS", con=cursor)

The above control shall be sufficient.

Answer 2

import pandas as pd    

cursor.execute('SELECT ROTAID,DESCRICAO FROM ROTAS')
df = pd.DataFrame(columns=["ROTAID", "DESCRICAO"])
for row in cursor:
   df = df.append({
                  "ROTAID": row['ROTAID'],
                  "DESCRICAO": row['DESCRICAO']
                  }, ignore_index=True)
conn.close()