sql output to pandas dataframe using python in pycharm IDE

Question

i am trying to save my sql output to pandas dataframe, using that i have to apply some logic and output save it to table.

how can i save the resultset to pandas dataframe.

code :

import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=DESKTOP-XXXXX;"
                      "Database=MOVIE_INFO;"
                      "Trusted_Connection=yes;")


cursor = cnxn.cursor()

cursor.execute('SELECT * FROM MOVIE_SRC')

for row in cursor:
    print('row = %r' % (row,)

Thanks

i tried another approach like

import pyodbc
import pandas as pd


cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=DESKTOP-XXXX;"
                      "Database=MOVIE;"
                      "Trusted_Connection=yes;")
cnxn = cnxn.cursor()
crsr = cnxn.cursor()
for table_name in crsr.tables(tableType='TABLE'):
    print(table_name)
cursor = cnxn.cursor()
sql = "Select *"
sql = sql + " From MOVIE"
print(sql)
cursor.execute(sql)
data = pd.read_sql(sql, cnxn)

but getting error

AttributeError: 'pyodbc.Cursor' object has no attribute 'cursor'

Please share your suggestion. Thanks

Possible duplicate of [How to convert SQL Query result to PANDAS Data Structure?](https://stackoverflow.com/questions/12047193/how-to-convert-sql-query-result-to-pandas-data-structure) — Andrey Portnoy, Sep 02 '18 at 16:15
@AndreyPortnoy: getting error like AttributeError: 'str' object has no attribute 'frame_query' — akhrot, Sep 02 '18 at 16:26
Here is a better duplicate https://stackoverflow.com/questions/39835770/move-data-from-pyodbc-to-pandas — Andrey Portnoy, Sep 02 '18 at 16:29
Please take a look at this last link, most likely it answers your question. — Andrey Portnoy, Sep 02 '18 at 16:44
I have no idea what you are doing, please stop throwing errors at me — Andrey Portnoy, Sep 02 '18 at 17:58

pjw · Accepted Answer · 2018-09-02T18:27:11.030

Although there are direct read methods in Pandas like pandas.read_sql() you should be able to take your successful cursor object, define new variables as empty Python lists and append the rows, then create a Pandas dataframe. Assuming your table is setup with columns as separate variables, here is some example code:

import Pandas as pd

# create some empty lists:
var1 = []
var2 = []
var3 = []

# append rows from the cursor object:
for row in cursor:
  var1.append(row[0])
  var2.append(row[1])
  var3.append(row[2])

# Create a dictionary with header names if desired:
my_data = {'header1': var1,
           'header2': var2,
           'header3': var3}

# Make a Pandas dataframe:
df = pd.DataFrame(data = my_data)

sql output to pandas dataframe using python in pycharm IDE

1 Answers1