Query Mapping between MS-SQL and SQLite

If for some reason you need to migrate your MS-SQL Server database to SQLite Database, among many other things, here are some things that you should pay attention to.

Things to consider:

  1. Each Statement must end with “;
  2. Remove references to schema on query
    1. eg: References to [dbo].
    2. Select * from [dbo].[table1] in SQL = select * from [table1] in SQLite
  3. Watch out for special functions
    1. Substring in SQL = Substr in SQLite
    2. Len in SQL = Length in SQLite
  4. Other things to watch out for:
    1. Setting Identity Column
      1. IDENTITY in SQL = AutoIncrement in SQLite
    2. Setting Primary Keys
      1. SQL has multiple ways to set Primary Keys, some ways do not work in SQLite
  5. Querying for Information Schema for tables/columns
    1. SQL has INFORMATION_SCHEMA.TABLES and SQLite has sqlite_master/sqlite_temp_master tables
  6. SQLite doesn’t support Truncate command
    1. Replace Truncate with Delete From
  7. Manually update DateTime value after inserting into SQLite to a string value
    1. DateTime in SQL and SQLite are different
    2. SQL: CAST(0x000090CE00000000 AS DateTime) shows valid date/time in SQL but shows different format in SQLite which is not readable by frameworks such as ADO.NET
    3. To be on safe side update with string value.
    4. Eg: update table1 set create_dt= ‘2018-03-30 00:36:10.520’;


Python Replace Texts For Each File

A useful function to replace certain texts for each files in a given folder:

Python Replace texts in all Files From a Folder and re-write

import os
# Getting the current work directory (cwd)
thisdir = os.getcwd()

folder = thisdir + '\\path_to_your_folder'

# r=root, d=directories, f = files
for r, d, f in os.walk(folder):
for file in f:
fPath = os.path.join(r, file)

read_lines = []
with open(fPath, 'r') as fp:
read_lines = fp.readlines()
read_lines = [line.rstrip('\n') for line in read_lines]

with open(fPath, 'w') as fw:
for line in read_lines:
newText = line.replace("your text 1 to replace", "replace with").replace('your text 2 to replace', 'repalce with').replace('you got the idea', 'your text to replace with').strip()
newText = newText + ";"
# do something like append/prepend

if not newText.startswith('--'):
#skip certain lines if you want before writing
fw.write(newText + "\n")