pyodbc.connect() 有效,但 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()
的引擎,但我正在努力弄清楚如何在我的 create_connection()
函数中使用相同的细节来成功创建引擎并连接到数据库.
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 通过精确的 Pyodbc 字符串 对我有用:
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
connection_string = (
r"Driver=ODBC Driver 17 for SQL Server;"
r"Server=(local)SQLEXPRESS;"
r"Database=myDb;"
r"Trusted_Connection=yes;"
)
connection_url = URL.create(
"mssql+pyodbc",
query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)
cnxn = engine.connect()
rows = cnxn.execute("SELECT name FROM sys.tables").fetchall()
print(rows)
相关文章