Python/SQLite Command skipped with trigger

Question

I've got some problems with my SQLite database. I've got some sql script like this:

CREATE TABLE Workers(
    Id_worker   INT NOT NULL PRIMARY KEY,
    Name        VARCHAR(20) NOT NULL,
    Surname     VARCHAR(30) NOT NULL,
    Id_job      INT NOT NULL, -- REFERENCES Job(Id_job),
    Adress      VARCHAR(30) NOT NULL,
    Start_date  SMALLDATETIME NOT NULL
);

CREATE TABLE OldWorkers(
    Id_arch     INT NOT NULL PRIMARY KEY,
    Name        VARCHAR(20) NOT NULL,
    Surname     VARCHAR(30) NOT NULL,
    Id_job      INT NOT NULL, -- REFERENCES Job(Id_job),
    Adress      VARCHAR(30) NOT NULL,   
    Start_date  SMALLDATETIME NOT NULL,
    Delete_date    SMALLDATETIME NOT NULL
);
CREATE TRIGGER OldWorkersTrigger
AFTER DELETE ON Workers
FOR EACH ROW
BEGIN 
INSERT INTO OldWorkers (Id_arch, Name, Surname, Id_job, Adress, Start_date) VALUES (old.Id_arch, old.Name, old.Surname, old.Id_job, old.Adress, old.Start_date,datatime('now'));
END;

I try to do it in Python 2.7.4 by sqlite3 like this:

conn = sqlite3.connect('Company.db')
c = conn.cursor()
fd = open('MyScript.sql', 'r')
sqlFile = fd.read()
fd.close()

# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
i = 1 
# Execute every command from the input file
for command in sqlCommands:
    # This will skip and report errors
    # For example, if the tables do not yet exist, this will skip over
    # the DROP TABLE commands
    print i," : ",command
    i = i + 1
    try:
        c.execute(command)
    except OperationalError, msg:
        print "Command skipped: ", msg

But in command wiht my trigget it returns: Command skipped: near ")": syntax error. And after END; it returns: Command skipped: cannot commit - no transaction is active

score 0 · Accepted Answer · answered Jan 14 '14 at 08:46

0

You are splitting the file at every semicolon, but the CREATE TRIGGER statement has an embedded semicolon.

To check whether a statement is complete, try the sqlite3.complete_statement function.

answered Jan 14 '14 at 08:46

CL.

173,858
17
217
259

Ofcourse! I must remember that every problem begins with semicolon :P Now with coplete_statement it works good. Thanks CL :) – user3192222 Jan 14 '14 at 18:42

score -2 · Answer 2 · answered Jan 14 '14 at 01:45

-2

SQLite can ingest and execute arbitrary SQL statements. Why not consider the following (untested):

conn = sqlite3.connect('Company.db')

c = conn.cursor()

with open('MyScript.sql', 'r') as fd:

try:

    c.execute(fd.read())

except OperationalError, msg:

    print "Command skipped: ", msg

answered Jan 14 '14 at 01:45

smitty1e

27
3

`execute` can execute only a single SQL statement. – CL. Jan 14 '14 at 08:33
Should have said executemany() see also http://stackoverflow.com/questions/5331894/i-cant-get-pythons-executemany-for-sqlite3-to-work-properly – smitty1e Jan 14 '14 at 16:00
`executemany` cannot execute multiple statements; it executes a single statement repeatedly. – CL. Jan 14 '14 at 16:06
Thus we see the value of speculating. – smitty1e Feb 22 '14 at 16:16

Python/SQLite Command skipped with trigger

2 Answers2