Unable to create tables in MySQL using PyDAL

[raghavender goud]

Hi.

We want to use MySQL DB for an application, have installed both py4web and MySQL in an ubuntu virtual machine. Using PyDAL we are unable to create tables in MySQL DB, we see no issues with SQLite. 

Here is my connection string for MySQL DB:

"mysql://username:mypassword@localhost/mysqllocaldb"

any suggestions?

[klaus z]

With some minor changes should work. Possibly you need to make some changes in the settings.py

A short extract from a Jupyter Notebook file (what I like to test):

#Jupyter CELL 1

from py4web import DAL, Field #that includes all databases 

#Jupyter CELL 2

# create db infrastructure

#even when the db is not there, there is no error message; to create tables in db deactivate: , auto_import = True , migrate = False

db_avm = DAL('sqlite://avm.db'

         , folder = '/Applications/py4web/apps/avm/databases'

#         , auto_import = True #for import and exploration

#         , migrate = False #for import and exploration

         , auto_import = False # needed for table creation

         , migrate = True # needed for table creation

        )

#Jupyter CELL 3

db_avm.define_table('fritzbox'

                , Field('infolist_detail_name', type='string', length=60)

                , Field('date_valid', type='datetime')

                , Field('infolist_value', type='decimal(10,2)')

                , Field('date_ins_upd', type='datetime', default=datetime.datetime.now(), update=datetime.datetime.now())

                )

#Jupyter CELL 4

db_avm.commit() #this commit creates the db file. 

Make sure you get this settings through py4web (without jupyter) and then it should be fine.  

~Klaus

[raghavender goud]

Thank you. This resolved my issue :)

-Raghavender

[klaus z]

You are welcome. 

Luckily that was easy to answer for me as I had made some tests some time ago.