Resetting the database

[Archibald Linx]

Dear Web2py users,

How can I reset the database of my Web2py project ?

Thanks,
Archibald

[Bruno Rocha]

which database engine?

http://zerp.ly/rochacbruno

[Archibald Linx]

Thank you Bruno.

I am using SQLite.

I want to reset all the tables of my project.

Thanks,
Archibald

On 14 nov, 01:44, Bruno Rocha <rochacbr...@gmail.com> wrote:
> which database engine?
>
> http://zerp.ly/rochacbruno

[DenesL]

Hi Archibald,

if you want to clear all records from a table do:

db.table.truncate()

To clear all just loop thru db.tables .

db.commit() to finish.

It should work for all DBs.

[~redShadow~]

On Mon, 2011-11-14 at 06:02 -0800, DenesL wrote:
> Hi Archibald,
>
> if you want to clear all records from a table do:
>
> db.table.truncate()
>
> To clear all just loop thru db.tables .
>
> db.commit() to finish.
>
> It should work for all DBs.
>
>
> On Nov 14, 7:56 am, Archibald Linx <archibaldl...@gmail.com> wrote:
> > Thank you Bruno.
> >
> > I am using SQLite.
> >
> > I want to reset all the tables of my project.

* If you want to delete *content* of some specific table, use
``db.table.truncate()``, as DenesL says.
* If you want the DAL to recreate the whole table (with SQLite, the
allowed alters are limited, sometimes table drop is needed in order to
clean up): ``db.table.truncate()``
* If you want to completely destroy and recreate the whole database,
just empty the ``databases`` folder inside your application directory.

--
Samuele ~redShadow~ Santi
----------------------------------------------------------------
redshadow[at]hackzine.org - redshadowhack[at]gmail.com

Blog: http://hackzine.org

GPG Key signature:
050D 3E9F 6E0B 44CE C008 D1FC 166C 3C7E EB26 4933
----------------------------------------------------------------
/me recommends:
Squadra Informatica - http://www.squadrainformatica.com
----------------------------------------------------------------
- Proud ThinkPad T-Series owner
- Registered Linux-User: #440008
* GENTOO User since 1199142000 (2008-01-01)
* former DEBIAN SID user
----------------------------------------------------------------
"Software is like sex: it's better when it's free!"
-- Linus Torvalds

[apple]

Web2py tries to redefine tables in line with changes in the model file
but sometimes this is not possible with sqlite. Therefore you may want
to delete the table definitions as well. If you do want a clean sheet
of paper then do this:

exclude=[]
import os
import glob
import sys
sys.path.append("c:/web2py")
from gluon import *
db = DAL('sqlite://storage.sqlite', folder='c:/web2py/applications/app/
databases', auto_import=True)

def delete():
for tablename in db.tables[:]:
if tablename in exclude or tablename[0:4]=="auth":
print("keep "+tablename)
else:
table=db[tablename]
try:
print("delete "+tablename)
filelist=glob.glob("c:/web2py/applications/app/
databases/*"+tablename+".table")
if len(filelist)>0: os.remove(filelist[0])
table.drop()
except:
print("failed to delete "+tablename)
print(sys.exc_info())
db.commit()
delete()

[Bruno Rocha]

using sqlite. just go to /databases folder and delete everything there.

web2py will create it all again.

http://zerp.ly/rochacbruno

[Vinicius Assef]

SQLite? Just delete your database file.

Web2py will recreate it next time you execute your app.

If it doesn't help, try something like that in web2py shell:
http://pastebin.com/w0q4yKkK

--
Vinicius Assef.

[Archibald Linx]

Thanks to everybody.

Archibald

On 14 nov, 21:33, Vinicius Assef <vinicius...@gmail.com> wrote:
> SQLite? Just delete yourdatabasefile.

>
> Web2py will recreate it next time you execute your app.
>
> If it doesn't help, try something like that in web2py shell:http://pastebin.com/w0q4yKkK
>
> --
> Vinicius Assef.
>
> On Mon, Nov 14, 2011 at 10:56 AM, Archibald Linx
>
>
>
>
>
>
>
> <archibaldl...@gmail.com> wrote:
> > Thank you Bruno.
>
> > I am using SQLite.
>
> > I want to reset all the tables of my project.
>
> > Thanks,
> > Archibald
>
> > On 14 nov, 01:44, Bruno Rocha <rochacbr...@gmail.com> wrote:
> >> whichdatabaseengine?
>
> >>http://zerp.ly/rochacbruno
> >> Em 13/11/2011 22:38, "Archibald Linx" <archibaldl...@gmail.com> escreveu:
>
> >> > Dear Web2py users,
>

> >> > How can I reset thedatabaseof my Web2py project ?
>
> >> > Thanks,
> >> > Archibald

[Matthew J Watts]

I had to log out of my webapp before deleting the database data, otherwise i would get an error message

cheers

[Massimo Di Pierro]

In the case of sqlite it is easy enough to also delete the content of the databases/ folder.

[Yoel Benitez Fonseca]

I wan't to clean up my database trough an script with i want to use as a data generator for demo purposes it work's fine wth SQLite but with Mysql got a lot of error's:

def limpiar_db(db, models):
    for m in models:
        l("Limpiando tabla: {}".format(m))
        db[m].truncate()
    db.commit()

a_limpiar = [
    'almacenamiento', 'perfil',
    'movimiento_inventario', 'componente_modelo_producto',
    'componente', 'modelo_producto',
    'auth_permission', 'auth_membership', 'auth_user', 'auth_group',
]
limpiar_db(db, a_limpiar)

With mysql:

DEBUG:web2py.demo102017:Limpiando tabla: movimiento_inventario
DEBUG:web2py.demo102017:Limpiando tabla: componente_modelo_producto
DEBUG:web2py.demo102017:Limpiando tabla: componente
Traceback (most recent call last):
  File "/opt/web-apps/web2py/gluon/shell.py", line 274, in run
    execfile(startfile, _env)
  File "applications/demo102017/private/generadordatos.py", line 150, in <module>
    limpiar_db(db, a_limpiar)
  File "applications/demo102017/private/generadordatos.py", line 137, in limpiar_db
    db[m].truncate()
  File "/opt/web-apps/web2py/gluon/packages/dal/pydal/objects.py", line 869, in truncate
    return self._db._adapter.truncate(self, mode)
  File "/opt/web-apps/web2py/gluon/packages/dal/pydal/adapters/base.py", line 824, in truncate
    self.execute(query)
  File "/opt/web-apps/web2py/gluon/packages/dal/pydal/adapters/__init__.py", line 67, in wrap
    return f(*args, **kwargs)
  File "/opt/web-apps/web2py/gluon/packages/dal/pydal/adapters/base.py", line 412, in execute
    rv = self.cursor.execute(command, *args[1:], **kwargs)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1701, 'Cannot truncate a table referenced in a foreign key constraint (`demo102017`.`componente_modelo_producto`, CONSTRAINT `componente_modelo_producto_ibfk_1` FOREIGN KEY (`componente`) REFERENCES `demo102017`.`componente` (`id`))')

[Alex Beskopilny]

truncate with foreign-key depends on DB.

for postgresql   :
def clear_tbls():
    str1 = ','.join(  [ t  for t in db.tables() if "auth_" not in t]   )
    if len(str1):
         db.executesql( 'truncate {} RESTART IDENTITY'.format(str1)  )


понедельник, 14 ноября 2011 г., 3:38:15 UTC+3 пользователь Archibald Linx написал: