0

This is my code. It will read a bunch of files with SQL commands in identical formats (i.e. comments prefaced with -, along with some blank lines, hence the if condition in my loop)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import csv,sqlite3,os

conn = sqlite3.connect('db_all.db')
c = conn.cursor()
files = os.listdir('C:\\Users\\ghb\\Desktop\\database_schema')

for file in files:
    string = ''
    with open(file, 'rb') as read:
        for line in read.readlines():
            if line[0]!='-' and len(line)!=0: string = string + line.rstrip() #error also occurs if I skip .rstrip
        print string #for debugging purposes
        c.executescript(string)
        string=''

conn.close()

Error:

Traceback (most recent call last):
  File "C:/Users/ghb/Desktop/database_schema/database_schema.py", line 16, in <module>
    c.executescript(string)
sqlite3.OperationalError: near "SET": syntax error

For fear of clutter, here is the output of the string variable (without the INSERT INTO data, that is confidential):

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";




CREATE TABLE IF NOT EXISTS `career` (
  `carKey` int(11) NOT NULL AUTO_INCREMENT,
  `persID` bigint(10) unsigned zerofill NOT NULL,
  `persKey` int(6) unsigned NOT NULL,
  `wcKey` int(2) unsigned NOT NULL,
  `wtKey` int(2) unsigned DEFAULT NULL,
  `pwtKey` int(2) unsigned DEFAULT NULL,
  `dptId` bigint(10) unsigned NOT NULL,
  `dptNr` int(4) unsigned NOT NULL,
  `dptalias` varchar(10) COLLATE utf8_icelandic_ci NOT NULL,
  `class` enum('A','B') COLLATE utf8_icelandic_ci NOT NULL,
  `getfilm` enum('yes','no') COLLATE utf8_icelandic_ci NOT NULL DEFAULT 'yes',
  `finished` enum('true','false') COLLATE utf8_icelandic_ci NOT NULL DEFAULT 'false',
  `startDate` date NOT NULL,
  `endDate` date DEFAULT NULL,
  `regDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` tinyint(4) NOT NULL,
  `status` set('LÉST','BRFL','BRFD','BRNN') COLLATE utf8_icelandic_ci DEFAULT NULL,
  `descr` text COLLATE utf8_icelandic_ci,
  PRIMARY KEY (`carKey`),
  KEY `pwtKey` (`pwtKey`),
  KEY `wtKey` (`wtKey`),
  KEY `dptId` (`dptId`),
  KEY `user` (`user`),
  KEY `persID` (`persID`),
  KEY `persKey` (`persKey`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_icelandic_ci AUTO_INCREMENT=2686 ;

Input sample:

INSERT INTO `career` (`carKey`, `persID`, `persKey`, `wcKey`, `wtKey`, `pwtKey`, `dptId`, `dptNr`, `dptalias`, `class`, `getfilm`, `finished`, `startDate`, `endDate`, `regDate`, `user`, 
(5, 34536, 346, 22, 44, 34, 3454356, 33, 'asdasd', 'ASDASD', 'ASDSD', 'true', '1991-02-04', '2010-05-02', '2009-05-02 00:01:02', 1, NULL, 'HH:
'),
Firkamon
  • 729
  • 1
  • 8
  • 18
  • Please [edit] your question and include a sample of the input. –  Jun 18 '15 at 14:04
  • 2
    Why are you manually parsing out comments and blank lines? There's no reason to do that; sqlite automatically does that. Just read the entire file and call `c.executescript` on the context. – Colonel Thirty Two Jun 18 '15 at 14:05
  • 3
    Also that query is for MySQL and won't work with SQLite; in particular, `SET` doesn't exist, all of the extra options after the table definition (`ENGINE`, etc) don't exist, and the `enum`, `set`, etc. types don't exist. – Colonel Thirty Two Jun 18 '15 at 14:06
  • Thank you for the parsing suggestion, I will do that. Sigh, oh well. Do you know of a resource with equivalences between the two? – Firkamon Jun 18 '15 at 14:08
  • Found one: http://stackoverflow.com/questions/3890518/convert-mysql-to-sqlite – Firkamon Jun 18 '15 at 14:11
  • I suggest you read the documentation at https://www.sqlite.org/docs.html. – rlbond Jun 18 '15 at 17:31

0 Answers0