pyodbc.connect()有效，但sqlalchemy.create_engine().connect()不起作用 [英] pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

问题描述

我正在尝试编写一个Python脚本，该脚本可以使用Excel工作表并将其作为表格导入到我的SQL Server Express(具有Windows身份验证)数据库中.为此，我正在使用pandas将Excel文件读入pandas DataFrame，然后希望使用pandas.to_sql()将数据导入到我的数据库中.但是，要使用此功能，我需要使用sqlalchemy.create_engine().

I am attempting to write a Python script that can take Excel sheets and import them into my SQL Server Express (with Windows Authentication) database as tables. To do this, I am using pandas to read the Excel files into a pandas DataFrame, I then hope to use pandas.to_sql() to import the data into my database. To use this function, however, I need to use sqlalchemy.create_engine().

我能够单独使用pyodbc连接到我的数据库，并运行测试查询.此连接是通过以下代码完成的:

I am able to connect to my database using pyodbc alone, and run test queries. This conection is done with the followng code:

def create_connection(server_name, database_name):
    config = dict(server=server_name, database= database_name)

    conn_str = ('SERVER={server};DATABASE={database};TRUSTED_CONNECTION=yes')

    return pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};' + conn_str.format(**config))

...

server = '<MY_SERVER_NAME>\SQLEXPRESS'
db = '<MY_DATABASE_NAME>

connection = create_connection(server, db)
cursor = connection.cursor()
cursor.execute('CREATE VIEW test_view AS SELECT * FROM existing_table')
cursor.commit()

但是，这没什么用，因为我不能使用pandas.to_sql()-为此，我需要使用sqlalchemy.create_engine()中的引擎，但是我一直在努力寻找如何在以上功能可成功创建引擎并连接到数据库.

However, this isn't much use as I can't use pandas.to_sql() - to do so I need an engine from sqlalchemy.create_engine(), but I am struggling to figure out how to use my same details in my create_connection() function above to successfully create an engine and connect to the database.

我已经尝试了很多类似的组合:

I have tried many, many combinations along the lines of:

engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?driver={ODBC Driver 13 for SQL Server}?trusted_connection=yes")
conn = engine.connect().connection

或

engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?trusted_connection=yes")   
conn = engine.connect().connection

推荐答案

A

A Pass through exact Pyodbc string works for me from Python 3.6 on Windows:

from sqlalchemy import create_engine
import urllib
conn_str = (
    r'Driver=ODBC Driver 11 for SQL Server;'
    r'Server=(local)\SQLEXPRESS;'
    r'Database=myDb;'
    r'Trusted_Connection=yes;'
)
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn = engine.connect()
rows = cnxn.execute("SELECT name FROM sys.tables").fetchall()
print(rows)

这篇关于pyodbc.connect()有效，但sqlalchemy.create_engine().connect()不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！