Migrate from sqlite to mysql

[Andrea Fae']

Hello everybody

I have some questions about an appllication I want to migrate from SQLite db to MySql in windows:

- what version of mysql do I have to install to my PC where I have 2.17 version of web2py? Where can I find?

- is it necessary to install drivers or it is still included in my web2py installed by windows installation method?

- what is the procedure to migrate the db of my application?

- after migrating, how to install the application using pyrhonanywhere site? (I'm just using the app made with SQLite)

thank you for youe answers, even if you don't answer to all questions...

[黄祥]

- what version of mysql do I have to install to my PC where I have 2.17 version of web2py? Where can I find?

 

in windows env there are mysql and mariadb

ref:
https://downloads.mariadb.org

https://dev.mysql.com/downloads/mysql/

- is it necessary to install drivers or it is still included in my web2py installed by windows installation method?

web2py already ship the mysql driver  

- what is the procedure to migrate the db of my application?

better to export and import in csv file

step:

- create database mysql

- when web2py using sqlite use export_to_csv_file()

- change db uri in private/appconfig.ini from sqlite to mysql 

- load web2py app, ensure migrate is true, so that will create the tables defined in web2py

- when web2py using mysql use import_from_csv_file() 

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Exporting-and-importing-data 

another way around is web2py have a scripts/cpdb.py

ref:

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

- after migrating, how to install the application using pyrhonanywhere site? (I'm just using the app made with SQLite)

local

- export your database using mysqldump

- pack your app from web2py admin

pythonanywhere

- login to pythonanywhere, use tab console to import your mysql database

- then in web2py admin just Upload and install packed application

best regards,

stifan

[Ben Lawrence]

And turn off lazy-tables until mysql has built all the tables.

[Andrea Fae']

Thank you guys!

I will do a test and let you known.

Thank you for now.

[Andrea Fae']

Hello, if I trye to install mysql 8 on my windows pc it tells me I have a problem about requirements. I don't have installed python 3.7. But could I have problem installing python 3.7 on my web2py based on python 2.17? See attachment. thank you in advance for your gently answers!

[黄祥]

pardon, not using windows atm, so cant test it

perhaps you have 2 options:

- try mariadb (not sure mariadb required python3 or not)

or

- use conda (miniconda (light version) or anaconda) to install python3 and python2 in 1 machine

ref:

https://www.anaconda.com/distribution/

https://conda.io/en/latest/miniconda.html

best regards,

stifan

[Andrea Fae']

but, what happen if I install python 3.7 on windows? Does web2py still use 2.7?

Or installinh mysql without this connector?

What does you think? Other suggestions? I don't like to use different things because I need to publish my application to pythonanywhere...

thanks

[黄祥]

but, what happen if I install python 3.7 on windows? Does web2py still use 2.7?

this question is vague
not sure what do you have in web2py? win version or source version?
if use source version, then web2py run on python is depending on the command you use

e.g. in ubuntu environment

python web2py.py # for python 2
python3 web2py.py # for python 3

 

Or installinh mysql without this connector?

not sure, not tested, perhaps can try it first in virtual machine first e.g. virtualbox or vmware

 

What does you think? Other suggestions? I don't like to use different things because I need to publish my application to pythonanywhere...

think mysql and mariadb is not different things, it's same :

In 2010, when Oracle acquired Sun, Widenius forked the open-source MySQL project to create MariaDB.

ref:

https://en.wikipedia.org/wiki/MySQL

for conda, is python package manager just think about it's like a versioning for python installation

ref:

https://en.wikipedia.org/wiki/Conda_(package_manager)

best regards,

stifan

[Jon Subscripted]

Hi Andrea,

Is this " problem about requirements " a notification that MySQL installation wizard showed? 

If it is, in my experience you can ignore it and move on with the installation. Python3.X is optional for MySQL, it just suggests to install it.

Regards, Jon.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[黄祥]

another way around you can use bundled software (xampp, mamp, wamp, ampps, bitnami etc) or docker (mysql or python container)

best regards,

stifan

[Andrea Fae']

Hello!

I followed the instruction you wrote me.

But at that point:" load web2py app, ensure migrate is true, so that will create the tables defined in web2py"

I have this error:

 

Error ticket for "ga"

Ticket ID

127.0.0.1.2019-02-13.20-49-28.fcc1a494-3aa4-42c2-b1a9-ab14a466e72e

<type 'exceptions.RuntimeError'> Failure to connect, tried 5 times: Traceback (most recent call last): File "C:\web2py\gluon\packages\dal\pydal\base.py", line 454, in __init__ self._adapter = adapter(**kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\__init__.py", line 40, in __call__ obj = super(AdapterMeta, cls).__call__(*args, **kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 368, in __init__ super(SQLAdapter, self).__init__(*args, **kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 52, in __init__ self.reconnect() File "C:\web2py\gluon\packages\dal\pydal\connection.py", line 172, in reconnect self.connection = self.connector() File "C:\web2py\gluon\packages\dal\pydal\adapters\mysql.py", line 52, in connector return self.driver.connect(**self.driver_args) File "C:\web2py\gluon\contrib\pymysql\__init__.py", line 90, in Connect return Connection(*args, **kwargs) File "C:\web2py\gluon\contrib\pymysql\connections.py", line 688, in __init__ self.connect() File "C:\web2py\gluon\contrib\pymysql\connections.py", line 905, in connect self._get_server_information() File "C:\web2py\gluon\contrib\pymysql\connections.py", line 1231, in _get_server_information self.server_charset = charset_by_id(lang).name File "C:\web2py\gluon\contrib\pymysql\charset.py", line 38, in by_id return self._by_id[id] KeyError: 255

Versione

web2py™	Version 2.16.1-stable+timestamp.2017.11.14.05.54.25

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36.

Traceback (most recent call last): File "C:\web2py\gluon\restricted.py", line 219, in restricted exec(ccode, environment) File "C:/web2py/applications/ga/models/db.py", line 34, in <module> check_reserved=['all']) File "C:\web2py\gluon\packages\dal\pydal\base.py", line 169, in __call__ obj = super(MetaDAL, cls).__call__(*args, **kwargs) File "C:\web2py\gluon\packages\dal\pydal\base.py", line 474, 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 454, in __init__ self._adapter = adapter(**kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\__init__.py", line 40, in __call__ obj = super(AdapterMeta, cls).__call__(*args, **kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 368, in __init__ super(SQLAdapter, self).__init__(*args, **kwargs) File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 52, in __init__ self.reconnect() File "C:\web2py\gluon\packages\dal\pydal\connection.py", line 172, in reconnect self.connection = self.connector() File "C:\web2py\gluon\packages\dal\pydal\adapters\mysql.py", line 52, in connector return self.driver.connect(**self.driver_args) File "C:\web2py\gluon\contrib\pymysql\__init__.py", line 90, in Connect return Connection(*args, **kwargs) File "C:\web2py\gluon\contrib\pymysql\connections.py", line 688, in __init__ self.connect() File "C:\web2py\gluon\contrib\pymysql\connections.py", line 905, in connect self._get_server_information() File "C:\web2py\gluon\contrib\pymysql\connections.py", line 1231, in _get_server_information self.server_charset = charset_by_id(lang).name File "C:\web2py\gluon\contrib\pymysql\charset.py", line 38, in by_id return self._by_id[id] KeyError: 255

In file: C:\web2py\applications\ga\models\db.py

1.	<code object <module> at 000000000AC93830, file "C:\web2py\applications\ga\models\db.py", line 8

This is my appconfig.ini:

; App configuration [app] name = Welcome author = Your Name <you@example.com> description = a cool new app keywords = web2py, python, framework generator = Web2py Web Framework ; Host configuration [host] names = localhost:*, 127.0.0.1:*, *:*, * ; db configuration [db] uri = mysql://root:passwordroot@localhost/gadb?set_encoding=utf8mb4 migrate = true pool_size = 10 ; ignored for sqlite ; smtp address and credentials [smtp] server = smtp.gmail.com:587 sender = y...@gmail.com login = username:password tls = true ssl = true ; form styling [forms] formstyle = bootstrap3_inline separator =

I just created the db "gadb" in mysql.exe. You can see the db, but I only created the db with the command

mysql> create database gadb; Query OK, 1 row affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | gadb | | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 7 rows in set (0.00 sec)

Can you help me what is my mistake?

THANK YOU

[Ben Duncan]

Yes it will create the db.define.

Ben Duncan

DBA / Chief Software Architect

Mississippi State Supreme Court

Electronic Filing Division

[黄祥]

RuntimeError: Failure to connect, tried 5 times:

seems have a connection problem between python and mysql 

perhaps you can test the connection first (via command prompt)

or perhaps an uri definition is wrong, had you try:

private/appconfig.ini

uri = mysql://root:passwordroot@localhost/gadb

?

best regards,

stifan

[andfae]

Yes I tried this usi too  uri = mysql://root:passwordroot@localhost/gadb

I don't know what is the problem...

Thank you

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/w0aSCaf7W5o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[andfae]

How to test via command prompt the connection between python and mysql?

[Ben Duncan]

Ok, is the python AND the mysql on the same windows BOX ?

If not, could be a firewall issue.

For the record, my python stand alone are truly stand alone (But I run LINUX , no Windowns here).

So all my script are not thru the web2py but use the pydal library ...

Ben Duncan

DBA / Chief Software Architect

Mississippi State Supreme Court

Electronic Filing Division

You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[andfae]

Yes

[Dave S]

On Thursday, February 14, 2019 at 7:29:07 AM UTC-8, Andrea Fae' wrote:

Yes

Il giorno gio 14 feb 2019, 16:12 Ben Duncan <linu...@gmail.com> ha scritto:

Ok, is the python AND the mysql on the same windows BOX ?

If not, could be a firewall issue.

[...]

On Thu, Feb 14, 2019 at 7:48 AM andfae <and...@gmail.com> wrote:

How to test via command prompt the connection between python and mysql?

can you do

web2py.exe -S myapp -M

 If that fails to open a python shell, try adding -D 0 to that command line.

/dps

[黄祥]

on the stackoverflow ben's sent seems like problem with mysql ver 8 running on windows

perhaps you can use docker to run mysql or mariadb or percona container (which running on top of linux os)

best regards,

stifan

[Andrea Fae']

Yesterday night I solved the problem!

It was a problem about python driver...with pip I updated and now I juste re-create the database in myslq

But trying to import the csv I have now this problem

>>> db.import_from_csv_file(open('c:\web2py\ga_export.csv','rb'))

Traceback (most recent call last):

  File "<console>", line 1, in <module>

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

    *args, **kwargs)

  File "c:\web2py\gluon\packages\dal\pydal\objects.py", line 1001, in import_from_csv_file

    curr_id = inserting(**ditems)

  File "c:\web2py\gluon\packages\dal\pydal\objects.py", line 753, in insert

    ret = self._db._adapter.insert(self, row.op_values())

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

    raise e

IntegrityError: (1452, u'Cannot add or update a child row: a foreign key constraint fails (`gadb`.`auth_user`, CONSTRAINT `auth_user_ibfk_2` FOREIGN KEY (`modified_by`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE)')

>>>

Sigh....!! What can I do?

Thanks

[Web2_3py]

Hi,

I am having the same error (Failure to connect).

I checked the connection to mysql using python command line

1 2	>>> import mysql.connector >>> mysql.connector.connect(host='localhost',database='mysql',user='root',password='')

and it is success.

What could be the reason of that error?

Thanks.

[Andrea Fae']

Hello, I deleted a foreign key and I was able to import. But I have a strange problem. When I export all data is ok, when I import I don't have problems to do now but in mysql database some information chenged!

Could it depend on the charset umtf....?

Thank you in advance who will answer.

[Ben Duncan]

You mean character set UTF?

Make sure environment variable set to UTF.

Have had problems with Windows being NON UTF before.

Ben Duncan

DBA / Chief Software Architect

Mississippi State Supreme Court

Electronic Filing Division