Skip to main content
 首页 » 编程设计

python之反射(reflect) SQL Server 表时删除排序规则表达式

2024年11月24日9duanxz

我正在尝试使用 Python SQLAlchemy 将 MS SQL Server View 反射(reflect)到 SQLite 表中。

问题是 sqlalchemy 将 COLLATE "SQL_Latin1_General_CP1_CI_AS"添加到各种 NVARCHAR 列,这在 sqlite 中不受支持。

是否有一种与列名无关的方法从表定义中删除所有 COLLATES?

from sqlalchemy import * 
from sqlalchemy.orm import create_session 
from sqlalchemy.ext.declarative import declarative_base 
import urllib 
from sqlalchemy.orm import sessionmaker 
 
#Create and engine and get the metadata 
Base = declarative_base() 
source_connection = 'msql+pyodbc...' 
source_engine = create_engine(source_connection) 
metadata = MetaData(bind=source_engine) 
SourceSession = sessionmaker(source_engine) 
 
#destination 
dest_engine = create_engine('sqlite:///...', echo=True) 
DestSession = sessionmaker(dest_engine) 
 
 
#Reflect each database table we need to use, using metadata 
class tblR(Base): 
    __table__ = Table('tblR', metadata,  
                      Column("r_id", Integer, primary_key=True), 
                      autoload=True) 
 
 
#Create a session to use the tables     
 
 
# This is the query we want to persist in a new table: 
sourceSession = SourceSession() 
query= sourceSession.query(tblR.r_id, tblR.MK_Assumed).filter_by(r_id=0) 
 
 
# Build the schema for the new table 
# based on the columns that will be returned  
# by the query: 
metadata = MetaData(bind=dest_engine) 
columns = [Column(desc['name'], desc['type']) for desc in query.column_descriptions] 
column_names = [desc['name'] for desc in query.column_descriptions] 
table = Table("newtable", metadata, *columns) 
 
 
# Create the new table in the destination database 
table.create(dest_engine) 
 
# Finally execute the query 
destSession = DestSession() 
for row in query: 
    destSession.execute(table.insert(row)) 
destSession.commit() 

我收到以下错误:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such collation sequence: SQL_Latin1_General_CP1_CI_AS [SQL: '\nCREATE TABLE newtable (\n\tr_id INTEGER, \n\t"MK_Assumed" NVARCHAR(20) COLLATE "SQL_Latin1_General_CP1_CI_AS"\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8) 

请您参考如下方法:

我遇到了类似的问题,我是这样解决的:

for table in metadata.sorted_tables : 
    for col in table.c: 
        if getattr(col.type, 'collation', None) is not None: 
            col.type.collation = None 

编辑:为了更明确的控制,您似乎可以指定'BINARY'、'NOCASE' 或'RTRIM '

sqlite.org > datatypes > collations