0

I am completely new to PyCharm and I'm having the hardest time trying to figure out the PyCharm equivalent of running a SQL query in Jupyter Notebook using pyodbc. My ultimate goal is to run a very simple query and save the result table in a df within my python code in PyCharm. I have my PyCharm environment connected to my database, and I can see the SQL table I want to query when I click the Database explorer icon on the right side of the screen in PyCharm. However, when I go to use pyodbc to connect to the database and run my query, I get this error:

pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context (0) (SQLDriverConnect); [HY000] [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context (0)')

The code that generates this error is

conn = pyodbc.connect('Driver={SQL Server};'
                     'Server=[server name], [port name];'
                     'Database=[db name];'
                     'Trusted_Connection=yes;')
cursor = conn.cursor()

SQLstr = """
select *
from ETLStaging.dbo.Advisor
"""

advisordf = pandas.read_sql(SQLstr, conn)
advisordf

Well, more specifically it's the very first line of code that throws the error. I'm just at a loss here and I'm not sure if I'm even barking up the right tree because all of the JetBrains (PyCharm) documentation I've read seems to indicate that there's an easier way to run a SQL query in PyCharm without having to write the connection string for pyodbc. I've searched and searched but I keep finding forums and articles that refer me to other forums and articles. I can view the SQL table I want to query in the database explorer in PyCharm, but I don't know how to get it into a dataframe in my code. Do I just need to fix my connection string? Or am I going about this the completely wrong way?

Really appreciate any guidance anyone might be able to give here!

Fiona De La Fuente
  • 36
  • 4
  • PyCharm allows you to explore data sources and export query results, but it happens outside of Python context. If you're writing a program that is supposed to work with this data, then PyCharm has nothing to do with it – Marat Jun 24 '22 at 21:20

1 Answers1

0

I figured it out so just posting in case someone else ever has the same problem as me. Basically PyCharm allows you to run queries inline using pyodbc/sqlalchemy the same way you would in Jupyter. The error I posted above was because I was using the wrong connection string. I found some good sources that in aggregate helped me derive the correct connection string, including this SO thread, as well as GitHub and DataToFish.

Aside from the connection string being wrong, I also learned that the latest version of pandas no longer works with pyodbc, so I had to use sqlalchemy instead. This SO Thread was super helpful in outlining the easiest way to edit my code from using pyodbc for the SQL query to sqlalchemy.

Hope that helps someone! And also, don't be like me and pay for the developer edition of PyCharm thinking it's necessary in order to run queries in python, because I don't think it is!

Fiona De La Fuente
  • 36
  • 4