Continue executing sql statements if error found python sqlite3

Question

I have a file containing sql commands and I want to execute the commands given in the file and if any command throws any error then ignore the error and execute the next command. Here is a sample file:

drop table department;
drop table classroom;

create table classroom
    (building       varchar(15),
     room_number        varchar(7),
     capacity       numeric(4,0),
     primary key (building, room_number)
    );

create table department
    (dept_name      varchar(20), 
     building       varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

For eg. if the classroom table doesn't exists then the drop table command will produce an error and the program will terminate. I want that the program keep running and execute all commands in the file.

The problem I'm facing is that the create table command is in multiple lines so I don't know how to execute that.

Read the entire file into a string, use.. I think it's the executescript method on the sqlite connection object? — Shawn
– Shawn, Commented Oct 22, 2020 at 18:32
Why are you using VARCHAR with specified length while it's ignored by sqlite? — Olvin Roght
– Olvin Roght, Commented Oct 22, 2020 at 18:33
@OlvinRoght I didn't create this file. I'm currently learning sql in my university and this is a practice problem but I couldn't figure out how to do it. — O_o
– O_o, Commented Oct 22, 2020 at 18:37

peterlustig222 · Accepted Answer · 2020-10-22 19:30:57Z

1

Take a look at the sqlite3 manual:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
with open('PATH_TO_SQL_FILE', 'r') as fp:
    text = fp.read().split(';')
    for command in text:
       try:
           cur.execute(command)
       except sqlite3.Error:
           pass

edited Oct 22, 2020 at 19:30

answered Oct 22, 2020 at 19:00

peterlustig222

613 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

O_o Over a year ago

How will this handle errors? Please read the question carefully.

peterlustig222 Over a year ago

Oh my bet. Is this what you are looking for: stackoverflow.com/questions/36816640/…

O_o Over a year ago

No, this is not what I want to do.

peterlustig222 Over a year ago

I updated the answer. can you elaborate why this is not what you want to do?

O_o Over a year ago

Take a look at the comments under the question.

|

user14488480 · Accepted Answer · 2020-10-22 18:47:40Z

0

import sqlite3

#define connection and cursor
connection = sqlite3.connect ('test_db')
cursor =connection.cursor()


command1 ="""create table classroom
    (building       varchar(15),
    room_number        varchar(7),
    capacity       numeric(4,0),
    primary key (building, room_number)
    )"""

cursor.execute(command1)


command2 ="""create table department
    (dept_name      varchar(20), 
    building       varchar(15), 
    budget             numeric(12,2) check (budget > 0),
    primary key (dept_name)
    )"""
cursor.execute(command2)

answered Oct 22, 2020 at 18:47

user14488480

5 Comments

user14488480 Over a year ago

if you want to add more sql statements then you need to add more commands (command3,command4 etc)

O_o Over a year ago

this will only work for the sample file. I want it to work for any file. Please read the question carefully.

O_o Over a year ago

I don't have any information about the file I want to take the file as input and then execute the sql commands given in it.

user14488480 Over a year ago

Also I think it will be difficult if you do not have any information about the file...For example if you want to drop a table that does not exist yet...I expect an error message...

O_o Over a year ago

Yes there will be an error message and I want to catch the error and then execute the next command

Collectives™ on Stack Overflow

Continue executing sql statements if error found python sqlite3

2 Answers 2

7 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related