我试图使用pythonsqlachemy将mssqlserver视图反映到SQLite表中。你知道吗
问题是sqlalchemy向各种NVARCHAR列添加了一个COLLATE“SQL\u Latin1\u General\u CP1\u CI\u AS”,这在sqlite中是不受支持的。你知道吗
是否有一种与列名无关的方法可以从表定义中删除所有排序?你知道吗
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)
我也遇到了类似的问题,我就是这样解决的:
编辑:对于更显式的控制,似乎可以指定
'BINARY
'、'NOCASE'
或'RTRIM'
sqlite.org > datatypes > collations
相关问题 更多 >
编程相关推荐