使用pyodbc和sqlalchemy连接SQL服务器,不能使用“use database_name;"; [英] Connect to SQL server using pyodbc and sqlalchemy, cannot use "use database_name;"
本文介绍了使用pyodbc和sqlalchemy连接SQL服务器,不能使用“use database_name;";的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的代码如下:
import urllib
import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session
def list_dbs():
sql = """
use master;
SELECT name
FROM sys.databases;
"""
try:
odbc_connect = "DRIVER={SQL Server};Server=localhost;Database=master;port=1433"
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % urllib.quote_plus(odbc_connect),
echo=True, connect_args={'autocommit': True})
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)
result = session.execute(sql)
for v in result:
print(v)
except Exception as e:
print(e)
list_dbs()
然后出现错误此结果对象不返回行.它已自动关闭."
Then an error "This result object does not return rows. It has been closed automatically."
但后来我删除了use master;"它有效:
But then I remove "use master;" and it works:
因为我有很多数据库,所以我必须使用use databasename".有什么想法吗?
Because I have many databases, I have to use "use databasename." Any ideas?
推荐答案
您正面临 GitHub 上讨论的问题 此处.
You are facing the issue discussed on GitHub here.
就目前而言,您需要单独执行 USE ...
,然后再执行您的查询.示例:
As it stands now, you need to execute the USE ...
separately and then execute your queries. Example:
with engine.begin() as conn:
db_name = conn.execute(text("SELECT DB_NAME()")).fetchone()[0]
print(f'Current database: {db_name}') # Current database: myDb
tables = conn.execute(text("SELECT TOP 3 name FROM sys.tables ORDER BY name")).fetchall()
print(tables) # [('MillionRows',), ('myTable',), ('person',)]
conn.execute(text("USE master"))
db_name = conn.execute(text("SELECT DB_NAME()")).fetchone()[0]
print(f'Current database: {db_name}') # Current database: master
tables = conn.execute(text("SELECT TOP 3 name FROM sys.tables ORDER BY name")).fetchall()
print(tables) # [('MSreplication_options',), ('spt_fallback_db',), ('spt_fallback_dev',)]
这篇关于使用pyodbc和sqlalchemy连接SQL服务器,不能使用“use database_name;";的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文