Attach database to sqlite while also specifying schema-CodePudding

I am integration testing my company's application and I have been tasked with using sqlite specifically to mock our SQL Server. In the integration testing, I am calling some mssql queries. These queries go something like this:

SELECT
    column1
    ,column2
FROM
    [databaseName].dbo.TableName t1

I am able to attach dbo in sqlite, like so:

import sqlite3

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
attach = "ATTACH DATABASE ? AS dbo"
cur.execute(attach, (":memory:",))

table = """CREATE TABLE dbo.TableName(
Column1 INT PRIMARY KEY NOT NULL
,Column2 INT
);"""
cur.execute(table)

query = """INSERT INTO dbo.TableName (Column1, Column2) VALUES (?, ?);"""
cur.execute(query, (1, 999))

I have been unsuccessful in creating a solution where DatabaseName can be called- something like,

CREATE TABLE databaseName.dbo.TableName(....);

I have already tried this code, which fails:

attach = "ATTACH DATABASE ? AS databaseName.dbo"
cur.execute(attach, (":memory:",))

How can I implement this kind of solution in Python?

CodePudding user response：

SQLite won't allow you a db.schema.table reference, that's just the way it works.

You can ATTACH DATABASE with a schema-name containing a space, dot or another character by enclosing it in single quotes, but then you also need to access it in the same way, which will not work with your SQL Server queries.

ATTACH DATABASE ':memory:' AS 'db.schema';
CREATE TABLE 'db.schema'.table (id INT);

.databases
-- main:
-- test.schema:

.tables
-- test.schema.t

SELECT * FROM test.schema.t
-- Error: near ".": syntax error

SELECT * FROM 'test.schema'.t
-- nothing but no error

All in all, it's just not doable to test a large number of SQL Server queries on SQLite, because of bracketed names [], incompatible functions like IIF(), or things like VALUES expressions.