pyodbc connection makingit through direct pyodbc connnection but not working with sqlalchemy

Why is there a difference in how these two methods establish a connection to the SQL Server?

sql_server = 'myserver.database.windows.net'
sql_database = 'pv'
sql_username = 'sqladmin'
sql_password = 'password1'   
sql_driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+sql_driver+';SERVER=tcp:'+sql_server+';DATABASE='+sql_database+';UID='+sql_username+';PWD='+ sql_password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 SAPPHIRE_CASE_ID FROM PV_ALL_SUBMISSIONS_SL")
        row = cursor.fetchone()
        while row:
            print (str(row[0]))
            row = cursor.fetchone()

On the other hand, this method fails:

import pyodbc
sql_engine = sqlalchemy.create_engine(f'mssql+pyodbc://{sql_username}:{sql_password}@{sql_server}/{sql_database}?driver=ODBC+Driver+17+for+SQL+Server')
df.to_sql('PV_ALL_CLOSED_CASES_SL', con=sql_engine, if_exists='append')

An error occurs:

OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)') (Background on this error at: https://sqlalche.me/e/14/e3q8)

Although both methods use the same connection details, one for reading and the other for writing, only one succeeds at establishing a connection. The issue appears to be related to the different ways of handling special characters like "@" in the password when using create_engine() method.

Despite using the same server, username, and password variables in both connections, the presence of special characters in the real password seems to hinder the latter connection string from working properly.

pythonsql-serverpython-3.x

Answer №1

Shoutout to @Larnu for the solution:

import sqlalchemy
from sqlalchemy.engine import URL

connection_string = f"DRIVER={sql_driver};SERVER={sql_server};DATABASE={sql_database};UID={sql_username};PWD={sql_password}"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
sql_engine = sqlalchemy.create_engine(connection_url)

No need to url encode when using a cx_Oracle connection, but it's all good now.

Answer №2

Encountered a similar issue where pyodbc functioned correctly when used directly but not with SQLAlchemy.

After testing the following combination of dependencies:

  • Python 3.10.9
  • SQLAlchemy 2.0.15
  • pyodbc 4.0.39
  • ODBC Driver 17 for SQL Server

The only successful solution was using URL.create() from SQLAlchemy and passing all ODBC parameters directly without creating a connection string beforehand.

engine_str = URL.create(
    drivername="mssql+pyodbc",
    username=<username>,
    password=<password>,
    host=<server>,
    port=1433,
    database=<database>,
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "TrustServerCertificate": "no",
        "Connection Timeout": "30",
        "Encrypt": "yes",
    },
)

engine = create_engine(engine_str)

Basing my approach on the ODBC connection string provided by Azure:

Driver={ODBC Driver 18 for SQL Server};Server=<server>,1433;Database=<database>;Uid=<username>;Pwd=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

The solution I discovered can be found here.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

`python regular expressions function malfunctioning``

I am currently learning Python, and even though this might seem like a basic question to many of you, I am seeking some guidance in figuring out what is causing the issue here. My aim is to develop a function that can scan a text for phone numbers. i ...

pythonregexfunction

Error: Trying to access an index of an integer data type in Python is not allowed

I am currently working on a project to create a simple program that selects a random website and then counts the elements within that website. However, I have encountered an error: Traceback (most recent call last): File "element_counter.py", line 23, ...

pythonlistinttypeerror

Encountering a problem in Django when attempting to serialize decimal points, error message reads: 'ValuesListQuerySet' does not contain the attribute '_meta'

I'm trying to serialize a FloatField model instance in django, specifically within a management command. Here's the code I have: def chart_data(request): i = 1 chart = open_flash_chart() chart.title = t for manager in FusionMa ...

pythondjangojsondjango-models

Utilize BeautifulSoup and Selenium to extract dynamically generated table data from div elements and store it in a list

I am working on scraping table data using Selenium and passing it to Beautiful Soup. The current script I have pulls all text data, but it ends up as one big element in a list. Is there a way for Beautiful Soup to filter by the "table-container" div class ...

pythonlistseleniumdynamicbeautifulsoup

Incorporating a data point into a specific column within an array

As a physics undergraduate student with limited computer programming experience, particularly in Python, I am currently working on a script to shift the coordinate system of a set of coordinates from a .xyz file. The goal is to center the coordinate system ...

pythonarraysmatrix

Encountering a geb.driver.DriverCreationException while executing a Jenkins automation task using Selenium with Groovy on a LINUX machine

Encountering a geb.driver.DriverCreationException error while running a Jenkins automation job with Selenium and Groovy on a LINUX computer. Working on an Automation script using selenium with Groovy. Here are the environment details: java version = 1.8.0 ...

javapythonc#seleniumjenkins

The Python code is malfunctioning when used as a Geoprocessing Service

I've been struggling with this issue for quite some time now and I could really use some help. My goal is to create a geoprocessing service that takes permit information from a GDB, writes it to a file, and then opens it on the user's computer th ...

pythonapache-flexgisarcgis-server

Tips for extracting file paths correctly from the tkinterDND2 event object

Looking to add a "drop files here" feature to my GUI application built with tkinter in Python. I discovered the TkinterDnD2 library after finding this useful Stack Overflow response. After dropping a file, event.data returns the file name within curly br ...

pythonuser-interfacetkinter

Tips for effectively storing and displaying orbital paths in pygame

After following a tutorial on YouTube by TechWithTim, I successfully completed a solar system project in pygame. With plans to expand it further, adding more planets caused the program to crash due to memory issues related to orbit rendering. How can I eff ...

pythonperformancepygamememory-efficient

The process of "encrypting" a message using a specific set of guidelines often yields an unfinished outcome

I need to develop a function that encrypts a user's message and returns the encrypted string based on specific rules: Alphabetic Uppercase Character [A-Z]: Convert to lowercase and add a ^ character at the end Alphabetic Lowercase Character [a-z]: R ...

pythonfor-loopencryption

Tips for setting up communication between servers and multiple clients?

In an effort to establish a two-way communication between a single server and multiple clients, I have developed the following Server code: Import subprocess, time, socket, fileinput s = socket.socket(socket.AF_INET, socket.SOCK_STREAM) host='' ...

python

I encountered an error when trying to locate an element using its class name and xpath in Selenium with Python. The error message displayed was `ERR_SSL_VERSION_OR_CIPHER_MISMATCH`

I am in need of gathering information from a specific webpage that cannot be referenced openly due to its adult content. In order to proceed further, I must first click the age confirmation button on the page. My sole interest is to retrieve the page' ...

pythonseleniumxpath

Unexpected behavior occurs in Django routing with a VueJS single page application when the URL is missing a trailing slash

In my development setup, I am utilizing a Django backend with a VueJS frontend, serving a REST API through Django and using VueJS along with vue-router for the single page application. After reading up on this on Stack Overflow, I found a helpful suggesti ...

pythondjangovue.jsroutestrailing-slash

Issue with GeoPandas failing to properly reproject from EPSG 4326 to EPSG 3857

I'm encountering some difficulties when attempting to project a DataFrame in GeoPandas from EPSG:4326 to EPSG:3857 within a notebook. The original dataset is structured as follows: 0 POLYGON ((-97.44573128938707 25.889635, -97.35... 1 POL ...

pythongisgeopandas

Issue encountered when running Python scripts using Selenium

I have set up python, pip, and selenium on my computer and I am currently running a sample code on some basic websites. Code: from selenium import webdriver import time driver = webdriver.Chrome("C:\\Users\\skandregula\\Py ...

pythonseleniumselenium-webdriverselenium-chromedriver

Python urllib2 encounters difficulty in parsing a webpage

Currently, I am utilizing urllib2 within Python to scrape data from a webpage. However, I have encountered an issue where the read() method is not returning any results. Below is the code snippet I am using: import urllib2 url = 'http://edmonton.en ...

pythonurllib

Selenium fails to scroll down after opening while extracting URLs from a CSV file

Having an issue with scrolling through URLs pulled from a CSV without stopping until the end. The time.sleep() method didn't work for this. However, when working with a single URL directly, it works perfectly. Here are some example URLs: Any suggest ...

pythonselenium

Exploring how to read KMS SES-encrypted messages using boto3

I'm attempting to adapt the code from the following link to Python using boto3: https://github.com/gilt/node-s3-encryption-client/issues/3 However, I've hit a roadblock when trying to retrieve the plaintext from KMS with the code below: metadat ...

pythonencryptionboto3amazon-sesamazon-kms

Having trouble finding an element with Python Selenium after switching to a frame in Firefox browser?

I'm facing difficulty locating the element within the frame even after switching to the correct frame. Below is my code, error message, and HTML source. When I right-click on the frame and choose This Frame -> Show Only This Frame, I can find the e ...

pythonseleniumxpathiframecss-selectors

Exploring the capabilities of StringIO with separate read and write pointers

My Python program involves a thread that continuously retrieves a raw-byte buffer, which is essentially a log output from an embedded device, and converts it into an ascii-string with linebreaks. I want to be able to consume this data as lines from the ma ...

pythonpython-3.xstring