Cannot delete SQLite '.db' using os.remove

[Lynton Grice]

Hi there,

I am using SQLAlchemy to build a custom based queue implementation.

As part of the methods exposed I have a "deletequeue" that is meant to
physically delete the SQLite database from the file system.

But whenever I try delete it I get the following error:

WindowsError: (32, 'The process cannot access the file because it is
being used by another process'

I have tried calling the DISPOSE method of the ENGINE object from
SQLAlchemy but there is still some file handle holding on...

All I have done is create the database (.db file), and now I want to
delete it but no luck ;-(

Any ideas how I can close any "handles" referencing that ".db" file
for that SQLite database?

Thanks for the help

Lynton

[Michael Bayer]

here is a test that passes for me, however even if I don't close the
connection or dispose the engine the file still allows deletion. Your
first step is to ensure this program works on your platform.

from sqlalchemy import *

engine = create_engine("sqlite:///foo.db")

conn = engine.connect()
conn.execute("create table foo (bar string)")
conn.execute("insert into foo values('test')")
conn.close()
engine.dispose()

import os
os.remove('foo.db')

> Thanks for the help
>
> Lynton
>

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Lynton Grice]

HI there,

I tried your example on Windows and it works 100%...

I think it must have something to do with the way I am opening / using
the connection

db = create_engine(db_conn, echo=False, poolclass=StaticPool)
metadata = MetaData(db)
queue = Table(queueName, metadata,
Column('IDX', Integer, primary_key=True, autoincrement =
True ),
Column('ID', String(64)),
Column('CORRELATIONID', String(64)), )
queue.create()

I am also using the "SessionMaker" in other parts of the code...

self._sessionmaker = sessionmaker(bind=self._engine)
self._session = self._sessionmaker()

But I will try some other stuff now and see if I can get it to work...

Thanks

Lynton

> >http://groups.google.com/group/sqlalchemy?hl=en.- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

[Lynton Grice]

Hi Michael,

Can you paste the following code into your editor and try it out? Can
you tell me what I would need to do to close the connections in this
case?

Any help would be greatly appreciated ;-)

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import mapper
from sqlalchemy.pool import StaticPool
import os

class Message(object):
pass

if __name__ == '__main__':
db_conn = 'sqlite:///foo.db'
db = create_engine(db_conn,echo=False, poolclass=StaticPool)
metadata = MetaData(db)
queueName = 'foo'

queue = Table(queueName, metadata,
Column('IDX', Integer, primary_key=True,
autoincrement = True ),
Column('ID', String(64)),
Column('CORRELATIONID', String(64)),)
queue.create()

session_maker = sessionmaker(bind=db)
session = session_maker()
mapper(Message, queue)

msg = Message()
msg.ID = '1234'
msg.CORRELATIONID = '111'
session.add(msg)
session.commit()

print "Message count: " + str(session.query(Message).count())

db.dispose()
os.remove('foo.db')
print "Done..."

Thanks

Lynton

On Mar 8, 4:41 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:

[Andrija Zarić]

Your example runs fine on Ubuntu 9.04, with python 2.6.2 and SQLAlchemy-0.6beta1.

[Michael Bayer]

Lynton Grice wrote:
> Hi Michael,
>
> Can you paste the following code into your editor and try it out? Can
> you tell me what I would need to do to close the connections in this
> case?

I think when using SQLite, your best bet is to use NullPool in any case
which will eliminate the issue and even remove the need for calling
engine.dispose(). Though you will need to call session.close() at the
end to release the connection.

As far as StaticPool I would qualify what's happening here as a bug in
SQLAlchemy. engine.dispose() dumps the current pool and creates a new
one. StaticPool is the *one* pool that connects immediately when it is
first created, so that is why a connection is hanging around here. The
change needs to be that StaticPool only creates a connection when first
used, like all the other pools so I've added #1728 for that.

[Lynton Grice]

Hi there,

Thanks for the clarity, much appreciated ;-)

Lynton

> > On Mar 8, 4:41�pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> >> Lynton Grice wrote:
> >> > Hi there,
>
> >> > I am using SQLAlchemy to build a custom based queue implementation.
>
> >> > As part of the methods exposed I have a "deletequeue" that is meant to
> >> > physically delete the SQLite database from the file system.
>
> >> > But whenever I try delete it I get the following error:
>
> >> > WindowsError: (32, 'The process cannot access the file because it is
> >> > being used by another process'
>
> >> > I have tried calling the DISPOSE method of the ENGINE object from
> >> > SQLAlchemy but there is still some file handle holding on...
>
> >> > All I have done is create the database (.db file), and now I want to
> >> > delete it but no luck ;-(
>
> >> > Any ideas how I can close any "handles" referencing that ".db" file
> >> > for that SQLite database?
>
> >> here is a test that passes for me, however even if I don't close the

> >> connection or dispose the engine the file still allows deletion. � Your

> >> first step is to ensure this program works on your platform.
>
> >> from sqlalchemy import *
>
> >> engine = create_engine("sqlite:///foo.db")
>
> >> conn = engine.connect()
> >> conn.execute("create table foo (bar string)")
> >> conn.execute("insert into foo values('test')")
> >> conn.close()
> >> engine.dispose()
>
> >> import os
> >> os.remove('foo.db')
>
> >> > Thanks for the help
>
> >> > Lynton
>
> >> > --
> >> > You received this message because you are subscribed to the Google
> >> Groups
> >> > "sqlalchemy" group.
> >> > To post to this group, send email to sqlal...@googlegroups.com.
> >> > To unsubscribe from this group, send email to
> >> > sqlalchemy+...@googlegroups.com.
> >> > For more options, visit this group at

> >> >http://groups.google.com/group/sqlalchemy?hl=en.-Hide quoted text -