Migrate web2py sqlite application to mssql

[Andrea Fae']

Hello, can anyone tell me how to migrate a web2py application with simple sqlite database to a MSSQL 2016 database?

Only change the DAL? the system create itself all the tables and database? 

Thank you very much to who can answer me!

[villas]

Here are some notes which you may find helpful...

The basic idea is this:

• Create a new DB using your preferred MSSQL management tool.
• Change your DAL connection string and run the app.
• Check that the new DB has all the tables.
• Migrate your data
  

Please read the following resources before you start.

See this section in the book: 

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Copy-data-from-one-db-into-another

The utility script is here:  web2py/scripts/cpdb.py

Check out this thread: 

https://groups.google.com/forum/#!topic/web2py/mQk2hoRf7gw

Appadmin

The appadmin allows you to export your data from your old DB.

Be aware of this method:
db.import_from_csv_file()

[Andrea Fae']

Wonderful! I will test. Thank you so much

[Andrea Fae']

Hello, I tried and I set in the appconfig.ini this URI

uri       = mssql4://sa:password@TS-SQL2016R2\SQLEXPRESS/itassetdb

TS-SQL2016R2 is the hostname

SQLEXPRESS is the instance

itassetdb is the db created empty

when I try to execute this command

c:\web2py>python web2py.py -S ITAsset -M -P

No handlers could be found for logger "web2py"

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2020

Version 2.14.6-stable+timestamp.2016.05.10.00.21.47

Database drivers available: sqlite3, imaplib, pyodbc, pymysql, pg8000

Traceback (most recent call last):

  File "c:\web2py\gluon\restricted.py", line 227, in restricted

    exec ccode in environment

  File "applications\ITAsset\models\db.py", line 34, in <module>

    check_reserved=['all'])

  File "c:\web2py\gluon\packages\dal\pydal\base.py", line 174, in __call__

    obj = super(MetaDAL, cls).__call__(*args, **kwargs)

  File "c:\web2py\gluon\packages\dal\pydal\base.py", line 473, in __init__

    "Failure to connect, tried %d times:\n%s" % (attempts, tb)

RuntimeError: Failure to connect, tried 5 times:

Traceback (most recent call last):

  File "c:\web2py\gluon\packages\dal\pydal\base.py", line 446, in __init__

    self._adapter = ADAPTERS[self._dbname](**kwargs)

  File "c:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 60, in __call__

    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)

  File "c:\web2py\gluon\packages\dal\pydal\adapters\mssql.py", line 159, in __init__

    if do_connect: self.reconnect()

  File "c:\web2py\gluon\packages\dal\pydal\connection.py", line 125, in reconnect

    self.connection = f()

  File "c:\web2py\gluon\packages\dal\pydal\adapters\mssql.py", line 157, in connector

    return self.driver.connect(cnxn, **driver_args)

  File "c:\web2py\gluon\contrib\pypyodbc.py", line 2434, in __init__

    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)

  File "c:\web2py\gluon\contrib\pypyodbc.py", line 2483, in connect

    check_success(self, ret)

  File "c:\web2py\gluon\contrib\pypyodbc.py", line 988, in check_success

    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)

  File "c:\web2py\gluon\contrib\pypyodbc.py", line 966, in ctrl_err

    raise DatabaseError(state,err_text)

DatabaseError: (u'08001', u'[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Server SQL inesistente o accesso negato.')

I have the error regarding Server SLQ non existent or access denied? What can I do?

thank you

Il giorno mercoledì 12 agosto 2020 17:17:17 UTC+2, villas ha scritto:

[villas]

Hi Andrea

I have no knowledge about mssql server.

When I have difficulties with this kind of thing,  it is usually resolved by playing around with the connection string.

This link may help you test pyodbc:  https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver15

I also suggest you search this group for 'mssql' - but I'm sure you would have already thought of that.

Hopefully a mssql user will respond to the other thread you opened.