getting error while running the following python script to get a table from mssql server

Question

im getting error while running this python script

import pypyodbc as odbc
import pandas as pd

# uid = <username>;
# pwd = <password>;

# Set up a connection to the database
DRIVER_NAME = 'SQL SERVER'
SERVER_NAME = 'some_server_name'
DATABASE_NAME = 'some_database'
connection_string = f"""
DRIVER = {{{DRIVER_NAME}}};
SERVER = {SERVER_NAME};
DATABASE = {DATABASE_NAME};
Trust_Connection = yes;
"""

# Define the name of the stored procedure to execute

# Execute the stored procedure and retrieve the results as a pandas DataFrame
query = 'select * from some_table'
df = pd.read_sql_query(query, connection_string)

# Close the database connection
connection_string.close()

# Print the DataFrame
print(df)

the error is follows

Traceback (most recent call last):
  File "D:\files\python\sql_py.py", line 22, in <module>
    df = pd.read_sql_query(query, connection_string)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Laptop\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\sql.py", line 396, in read_sql_query
    pandas_sql = pandasSQL_builder(con)
                 ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Laptop\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\sql.py", line 752, in pandasSQL_builder
    raise ImportError("Using URI string without sqlalchemy installed.")
ImportError: Using URI string without sqlalchemy installed.

Answer 1

The error you are getting could be due to a few things, but it is likely related to the connection_string object. Here are a few things you could check:

1. Make sure that you have installed the pypyodbc package, which provides a Python DB API 2.0-compliant interface to ODBC databases. You can install it using pip: pip install pypyodbc.

2. Check that you have the correct driver name for your SQL Server instance. The driver name might be different depending on your version of SQL Server and your operating system.

3. Verify that the SERVER_NAME and DATABASE_NAME variables are set to the correct values for your database.

4. Check that you have the necessary permissions to access the database. Make sure that your username and password are correct, or try using integrated security if that is an option.

5. Ensure that the SQL Server is reachable from your network.

Also, note that you should remove the connection_string.close() line, since connection_string is not a connection object, and thus cannot be closed. Instead, you should close the odbc connection object that you create using odbc.connect().

import pypyodbc as odbc
import pandas as pd

# Set up a connection to the database
DRIVER_NAME = 'SQL Server'
SERVER_NAME = 'some_server_name'
DATABASE_NAME = 'some_database'
uid = 'your_username'
pwd = 'your_password'
connection_string = f"DRIVER={{{DRIVER_NAME}}};SERVER={SERVER_NAME};DATABASE={DATABASE_NAME};UID={uid};PWD={pwd}"

# Define the name of the stored procedure to execute
query = 'SELECT * FROM some_table'

# Execute the stored procedure and retrieve the results as a pandas DataFrame
with odbc.connect(connection_string) as conn:
    df = pd.read_sql_query(query, conn)

# Print the DataFrame
print(df)