vendredi 26 février 2016

parse .sql file with comments into SQLite with python

I have created a .sql file from a DIA diagram using the commandline tool parsediasql.exe

The resultant .sql file contains the commands and plenty of comments as well.

I created the following script to create the SQLIte database from the .sql definition file:

import sqlite3
import os
from unidecode import unidecode

def remove_non_ascii(text):
    return unidecode(unicode(text, encoding = "utf-8"))

def strip_non_ascii(string):
    ''' Returns the string without non ASCII characters'''
    stripped = (c for c in string if 0 < ord(c) < 127)
    return ''.join(stripped)

userhome = os.path.expanduser('~')
desktop = userhome + '\\Desktop\\'
#conn = sqlite3.connect(desktop + 'dbejemplo.db')
conn = sqlite3.connect('dbejemplo.db')
cursor = conn.cursor()

filename = '.\\Schematic\\code.sql'
with open(filename, 'r',  encoding="utf-8") as myfile:
    sqlcode = myfile.read().replace('\n', '')
orders = sqlcode.split(';')

for order in orders:
    try:
        sql = strip_non_ascii(order) + ';'
        cursor.execute(sql)
    except:
        print('Error:\n', sql)

This script works when I remove absolutely all the comets the .sql file by hand. How can I remove the .sql comments using python? or How can I just send all the commands at once to SQLite interpreter?

This SQL code should do for a test:

-- Arranques
create table startups (
   id        INTEGER      not null,
   Stage     INTEGER              ,--  Numero de la etapa (1 a 4)
   Seq_descr VARCHAR(256)         ,--  Descripcion de la secuencia
   Qfuel1    FLOAT                ,--  Valores de contador (kg) del combustible 1 (principal del motor)
   Qfuel2    FLOAT                ,--  Valores de contador (kg) del combustible 2
   Qfuel3    FLOAT                ,--  Valores de contador (kg) del combustible 3
   Qfuel4    FLOAT                ,--  Valores de contador (kg) del combustible 4
   Eaux      FLOAT                ,--  Valores de contador de energía activa de servicios auxiliares (kWh)
   Enet      FLOAT                ,--  Valores de contador de la energía neta activa  (kWh)
   constraint pk_startups primary key (id)
)   ;

create table companies (
   id   INTEGER      not null,
   name VARCHAR(256)         ,--  Nombre de la compañia
   constraint pk_companies primary key (id)
)   ;

Aucun commentaire:

Enregistrer un commentaire