Sqlite3 : Wrong table became deleted during attach database query from python

Question

I`m using SQLite version 3.3.6 from python 2.7.2.

While executing this query with executescript() from python script:

attach database 'Capacity.db' as WeekAgo;
drop table if exists maximums;
create table maximums (Element TEXT UNIQUE, Score NUMBER , Report TEXT);
insert into maximums (Element, Score, Report) select * from WeekAgo.maximums;

I always ending up with deletion of WeekAgo.maximums table in attached database and script crashing that no WeekAgo.maximums exists anymore. What am I doing wrong? Without DROP IF EXISTS, everything works fine.

I`m amused why during DROP it drops WeekAgo.maximums instead of maximums ??? It can at least say that maximums not found , to force me use DB name as prefix, to strictly identify tables. Actually exactly what you suggested below. I expect some error to point me that i did not identified table properly. — Daniel Gurianov
– Daniel Gurianov, Commented Jul 24, 2012 at 12:44
"The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist." sqlite.org/lang_droptable.html — user647772
– user647772, Commented Jul 24, 2012 at 12:46

user647772 · Accepted Answer · 2012-07-24 12:40:37Z

5

Use the main keyword:

attach database 'Capacity.db' as WeekAgo;
drop table if exists main.maximums;
create table main.maximums (Element TEXT UNIQUE, Score NUMBER , Report TEXT);
insert into main.maximums (Element, Score, Report) select * from WeekAgo.maximums;

See https://www.sqlite.org/lang_attach.html

If two or more tables in different databases have the same name and the database-name prefix is not used on a table reference, then the table chosen is the one in the database that was least recently attached.

answered Jul 24, 2012 at 12:40

user647772

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

4 Comments

Daniel Gurianov Over a year ago

Thanks, for your answer. It is really unexpected behaviour. Logically , without DB prefix, it should use DB that was opened first , like main workplace.

user647772 Over a year ago

Logicially it should behave as documented :)

Jon Clements Over a year ago

I've sent a link of this post to the relevant reporting system - will post an update if it gets a ticket number or something.

Jon Clements Over a year ago

Not necessarily an error; just not the behaviour one is led to expect.

Collectives™ on Stack Overflow

Sqlite3 : Wrong table became deleted during attach database query from python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related