Thanks Ben, I did what you suggested and somehow I think I moved on. Still I got a weird error: InternalError: (1060, u"Duplicate column name 'fen__tmp'")
import sys sys.path.append(r'C:\Users\Jon\Downloads\web2py_src\web2py\gluon') from gluon import DAL,Field from gluon import * dbpath = r'C:\Users\Jon\Downloads\web2py_src\web2py\applications\xakemate_scratch\databases' problemsuri = 'mysql://root:web2py@localhost/xakemate' posFEN = 'rnb2rk1/pp4pp/1qpbp3/3p4/4P3/3P1NP1/PPPN1RBP/R1BQ3K b - - 0 13' db = DAL(problemsuri)#,folder=dbpath)#,auto_import=True) db.define_table( "problem", Field("question_hint",type="string"), Field("fen",type="string"),#,requires=(IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'problem.fen'))), Field("solutionSAN",type="list:string",requires=IS_NOT_EMPTY()), Field("solutionUCI",type="list:string",requires=IS_NOT_EMPTY()), Field("solordered",type="boolean",default=True), Field("pcomment",type="string"), Field("previousSAN",type="string",default=''), Field("previousUCI",type="string",default=''), Field("previousFEN",type="string",default=''), Field("game_control",type="string",default=''), Field("ply_control",type="integer",default=0) ) query = (db.problem.fen == posFEN) problem = db(query).select(orderby=db.problem.id).first() No handlers could be found for logger "web2py" WARNING:root:Unable to import plural rules: No module named plural_rules Traceback (most recent call last): File "C:\Python27\withoutdefine.py", line 27, in <module> Field("ply_control",type="integer",default=0) File "C:\Python27\lib\site-packages\gluon\dal.py", line 7085, in define_table table = self.lazy_define_table(tablename,*fields,**args) File "C:\Python27\lib\site-packages\gluon\dal.py", line 7117, in lazy_define_table polymodel=polymodel) File "C:\Python27\lib\site-packages\gluon\dal.py", line 948, in create_table fake_migrate=fake_migrate) File "C:\Python27\lib\site-packages\gluon\dal.py", line 1054, in migrate_table self.execute(sub_query) File "C:\Python27\lib\site-packages\gluon\dal.py", line 1693, in execute return self.log_execute(*a, **b) File "C:\Python27\lib\site-packages\gluon\dal.py", line 1687, in log_execute ret = self.cursor.execute(*a, **b) File "C:\Python27\lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "C:\Python27\lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "C:\Python27\lib\site-packages\pymysql\connections.py", line 517, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "C:\Python27\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result result.read() File "C:\Python27\lib\site-packages\pymysql\connections.py", line 1075, in read first_packet = self.connection._read_packet() File "C:\Python27\lib\site-packages\pymysql\connections.py", line 684, in _read_packet packet.check_error() File "C:\Python27\lib\site-packages\pymysql\protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "C:\Python27\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) InternalError: (1060, u"Duplicate column name 'fen__tmp'") I'm not aware of defining the 'fen' field twice, so I do not understande where did this come from. What does this mean? Regards, Jon. On Mon, Feb 11, 2019 at 6:08 PM Ben Duncan <linux...@gmail.com> wrote: > No , all you need is the db.define for the table you are going to access, > unless there is foreign keyes involved. > Then include the PARENT table if these are child tables. > > *Ben Duncan* > DBA / Chief Software Architect > Mississippi State Supreme Court > Electronic Filing Division > > > On Mon, Feb 11, 2019 at 9:27 AM Jon Subscripted < > jonsubscripti...@gmail.com> wrote: > >> Thanks Ben, >> Should I include just the define_table for that very table (there are >> more, but will not be accessed)? >> Regards, Jon. >> >> On Mon, Feb 11, 2019 at 4:26 PM Ben Duncan <linux...@gmail.com> wrote: >> >>> If this script is being run OUTSIDE the web2py environment then you will >>> need to include the define inside the python program itself. >>> >>> *Ben Duncan* >>> DBA / Chief Software Architect >>> Mississippi State Supreme Court >>> Electronic Filing Division >>> >>> >>> On Mon, Feb 11, 2019 at 9:01 AM Jon Subscripted < >>> jonsubscripti...@gmail.com> wrote: >>> >>>> Thanks Ben, >>>> Honestly I do not know if I understood what you suggest, sorry... it's >>>> actually my fault as I'm still trying to learn web2py (reading the manual, >>>> etc.). >>>> >>>> My web2py-app is actually working, but I still have lost of data to >>>> load in the DB. Most of the data to populate de DB is created outside the >>>> app itself (this may change in the future, but unfortunatelly it is like >>>> that now). >>>> >>>> As this data creation is done outside my web2py-app I wanted the >>>> scripts creating this data to check which data has already been uploaded to >>>> the web2py-app database. In order to do so I wrote a python script which is >>>> meant to be run apart from the web2py-app to check if the data is already >>>> in the DB (calling the snippet below), if it's not create it and then load >>>> it. >>>> >>>> problemsuri = 'mysql://root:web2py@localhost/xakemate' >>>> dbpath = >>>> r'C:\Users\Jon\Downloads\web2py_src\web2py\applications\xakemate_scratch\databases' >>>> >>>> def checkproblem(posFEN): >>>> print "checking",posFEN >>>> dbproblems = DAL(problemsuri,folder=dbpath,auto_import=True) >>>> >>>> query = (dbproblems.problem.fen == posFEN) >>>> problem = dbproblems(query).select(orderby=dbproblems.problem.id >>>> ).first() >>>> print "DUPLICATED problem",problem >>>> return problem >>>> >>>> But when I do run the code I get an error telling me "AttributeError: >>>> 'DAL' object has no attribute 'problem'". >>>> >>>> Do I need to define the table I'm trying to access again ('cause it's >>>> already defined in models folder, but not in this script)? >>>> If I do need to define the table again, How should I do it? >>>> Regards, Jon. >>>> >>>> >>>> >>>> On Mon, Feb 11, 2019 at 3:43 PM Ben Duncan <linux...@gmail.com> wrote: >>>> >>>>> Oh yeah. One thing I missed explaining is that I spend the end of last >>>>> year creating a python library that would take file layouts I've gleaned >>>>> from a 4GL, old cobol systems and an Informix database, and some Oracle >>>>> Financials. >>>>> >>>>> I've create a "layout template" that the library will generate the >>>>> create table, primary keyes, secondary keyes, foreign keyes, grants and >>>>> views. >>>>> As part of the library it will generate the pydal "define" statements >>>>> as well. >>>>> >>>>> >>>>> *Ben Duncan* >>>>> DBA / Chief Software Architect >>>>> Mississippi State Supreme Court >>>>> Electronic Filing Division >>>>> >>>>> >>>>> On Mon, Feb 11, 2019 at 8:36 AM Ben Duncan <linux...@gmail.com> wrote: >>>>> >>>>>> I'm not sure what your statement means, however i will give it my >>>>>> best shot to explain> I'm new to web2py myself. >>>>>> Her goes: >>>>>> >>>>>> In my models folder i have several different sub folders: >>>>>> ar, ap ,gl ...etc... >>>>>> >>>>>> Each has the definition. For example under ar there is the >>>>>> dbf_arfiles_dal.py which starts likes this: >>>>>> >>>>>> db.define_table('ar_cusmas_billing_info', >>>>>> Field('company_number', type='reference client.company_number', >>>>>> ondelete='CASCADE'), >>>>>> Field('client_number', type='reference client.client_number', >>>>>> ondelete='CASCADE'), >>>>>> Field('bank_name', type='string', length=30), >>>>>> Field('bank_route', type='integer'), >>>>>> Field('bank_acct', type='integer'), >>>>>> Field('cc', type='string', length=2), >>>>>> Field('ccno', type='integer'), >>>>>> Field('exp1', type='date'), >>>>>> Field('holder1', type='string', length=50), >>>>>> Field('cc2', type='string', length=2), >>>>>> Field('ccno2', type='integer'), >>>>>> Field('exp2', type='date'), >>>>>> Field('holder2', type='string', length=50), >>>>>> primarykey=['company_number','client_number'], >>>>>> migrate=False) >>>>>> >>>>>> db.define_table('ar_cusmas_pclass', >>>>>> Field('company_number', type='reference client.company_number', >>>>>> ondelete='CASCADE'), >>>>>> ......... and so one. >>>>>> >>>>>> In 0.py , I start it off as: >>>>>> >>>>>> -------------------------------------------------------------------------------------------------------------------------------------------------------------- >>>>>> >>>>>> # -*- coding: utf-8 -*- >>>>>> >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> # AppConfig configuration made easy. Look inside private/appconfig.ini >>>>>> # Auth is for authenticaiton and access control >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> import datetime >>>>>> from gluon.contrib.appconfig import AppConfig >>>>>> from gluon.settings import global_settings >>>>>> from gluon import current >>>>>> from gluon.tools import Crud, Service >>>>>> # from gluon.tools import Auth >>>>>> >>>>>> from gluon.storage import * >>>>>> from pydal.contrib import portalocker >>>>>> from pydal._compat import basestring, StringIO, integer_types, >>>>>> xrange, BytesIO, to_bytes, long >>>>>> from gluon.storage import Messages, Settings, Storage >>>>>> from gluon.utils import web2py_uuid >>>>>> >>>>>> from gluon.validators import CLEANUP, CRYPT, IS_ALPHANUMERIC, >>>>>> IS_DATE_IN_RANGE, IS_DATE, \ >>>>>> IS_DATETIME_IN_RANGE, IS_DATETIME, IS_DECIMAL_IN_RANGE, \ >>>>>> IS_EMAIL, IS_EMPTY_OR, IS_EXPR, IS_FLOAT_IN_RANGE, IS_IMAGE, \ >>>>>> IS_IN_DB, IS_IN_SET, IS_INT_IN_RANGE, IS_IPV4, IS_LENGTH, \ >>>>>> IS_LIST_OF, IS_LOWER, IS_MATCH, IS_EQUAL_TO, IS_NOT_EMPTY, \ >>>>>> IS_NOT_IN_DB, IS_NULL_OR, IS_SLUG, IS_STRONG, IS_TIME, \ >>>>>> IS_UPLOAD_FILENAME, IS_UPPER, IS_URL >>>>>> >>>>>> # from gluon.validators import CRYPT, IS_EMAIL, IS_EQUAL_TO, >>>>>> IS_INT_IN_RANGE, IS_LOWER, IS_MATCH, IS_NOT_EMPTY, \ >>>>>> # IS_NOT_IN_DB >>>>>> from pydal.objects import Table, Field, Row >>>>>> >>>>>> ........ (and so on till I get to the bottom) >>>>>> >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> # Local application settings >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> >>>>>> settings = Storage() >>>>>> >>>>>> settings.migrate = False >>>>>> settings.title = request.application >>>>>> settings.subtitle = T('Ben Duncan') >>>>>> settings.author = 'Ben Duncan' >>>>>> settings.author_email = 'y...@example.com' >>>>>> settings.keywords = 'Ben Duncan' >>>>>> settings.description = 'Ben Duncan' >>>>>> settings.layout_theme = 'Default' >>>>>> settings.database_uri = 'postgres:// >>>>>> web2py:web2py@10.13.70.47:7103/ac03303_live' >>>>>> settings.security_key = 'c4d5c7ec-97b4-474b-98d3-9c1582dad510' >>>>>> settings.email_server = 'localhost' >>>>>> settings.email_sender = 'y...@example.com' >>>>>> settings.email_login = '' >>>>>> settings.login_method = 'local' >>>>>> settings.login_config = '' >>>>>> settings.plugins = [] >>>>>> session.is_logged_in = 'NO' >>>>>> >>>>>> -------------------------------------------------------------------------------------------------------------------------------------------------------------- >>>>>> >>>>>> Then in db.py I have: >>>>>> >>>>>> # -*- coding: utf-8 -*- >>>>>> >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> # Database model >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> >>>>>> db = DAL(settings.database_uri, pool_size=10, migrate_enabled=False, >>>>>> fake_migrate_all=True ) >>>>>> >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> # Here is sample code if you need for >>>>>> # - email capabilities >>>>>> # - authentication (registration, login, logout, ... ) >>>>>> # - authorization (role based authorization) >>>>>> # - services (xml, csv, json, xmlrpc, jsonrpc, amf, rss) >>>>>> # - old style crud actions >>>>>> # (more options discussed in gluon/tools.py) >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> # host names must be a list of allowed host names (glob syntax >>>>>> allowed) >>>>>> # auth = Auth(db, host_names=configuration.get('host.names')) >>>>>> # auth = Auth(db, secure=True) >>>>>> # >>>>>> ------------------------------------------------------------------------- >>>>>> #auth = Auth(db,signature=False) >>>>>> crud = Crud(db) >>>>>> >>>>>> ...... (every thing else is commented out as well. this is because I >>>>>> am doing my own custome authentication and other >>>>>> things - which MAYBE the grief I'm causing myself. ..) >>>>>> >>>>>> -------------------------------------------------------------------------------------------------------------------------------------------------------------- >>>>>> >>>>>> >>>>>> Hopefully this should get you going >>>>>> >>>>>> If I've missed anything, Anthony can you fill in ? >>>>>> >>>>>> Good Luck ... >>>>>> >>>>>> >>>>>> *Ben Duncan* >>>>>> DBA / Chief Software Architect >>>>>> Mississippi State Supreme Court >>>>>> Electronic Filing Division >>>>>> >>>>>> >>>>>> On Sat, Feb 9, 2019 at 10:56 AM Jon Subscripted < >>>>>> jonsubscripti...@gmail.com> wrote: >>>>>> >>>>>>> Hi Ben, >>>>>>> Inside my app folder I do have the table defined, but not in my >>>>>>> script (accessing the db outside web2py). >>>>>>> I thought that the folder path reference solved this (*). >>>>>>> Should i add the define table in my script? >>>>>>> Thanks and regards, Jon. >>>>>>> >>>>>>> (*) I guess that’s why .table files are kept even when SQLite is no >>>>>>> longer usted. >>>>>>> >>>>>>> El El vie, 8 feb 2019 a las 14:28, Ben Duncan <linux...@gmail.com> >>>>>>> escribió: >>>>>>> >>>>>>>> Did you set up the db_define table for the database in models. >>>>>>>> >>>>>>>> For example, the accounting package I am working on for our Supreme >>>>>>>> Court has and external postgres database. >>>>>>>> From the DDL, I've created, for example, the "ship to code" file >>>>>>>> (we reuse for something else) >>>>>>>> >>>>>>>> --------------------------------------------------------------------------------------------------------------------- >>>>>>>> db.define_table('ar_ship_code', >>>>>>>> Field('company_number', type='reference >>>>>>>> company.company_number', ondelete='CASCADE'), >>>>>>>> Field('code', type='string', length=10), >>>>>>>> Field('short_desc', type='string', length=20), >>>>>>>> Field('description', type='string', length=255), >>>>>>>> Field('notes', type='text'), >>>>>>>> primarykey=['company_number','code'], >>>>>>>> migrate=False) >>>>>>>> >>>>>>>> --------------------------------------------------------------------------------------------------------------------------- >>>>>>>> >>>>>>>> For each table you want to access, you will need a >>>>>>>> "db.define_table" entry in the model directory. >>>>>>>> >>>>>>>> Hope this helps >>>>>>>> *Ben Duncan* >>>>>>>> DBA / Chief Software Architect >>>>>>>> Mississippi State Supreme Court >>>>>>>> Electronic Filing Division >>>>>>>> >>>>>>>> >>>>>>>> On Fri, Feb 8, 2019 at 7:17 AM Jon Subscripted < >>>>>>>> jonsubscripti...@gmail.com> wrote: >>>>>>>> >>>>>>>>> Hi everyone, >>>>>>>>> I'm trying to access the data stored in my app db from a different >>>>>>>>> piece of software (apart from my web2py app). But I get an error >>>>>>>>> AttributeError. >>>>>>>>> >>>>>>>>> Based on the manual: >>>>>>>>> >>>>>>>>> >>>>>>>>> http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Using-DAL-without-define-tables >>>>>>>>> >>>>>>>>> I used the code below (from IDLE): >>>>>>>>> >>>>>>>>> import sys >>>>>>>>> >>>>>>>>> sys.path.append(r'C:\Users\Jon\Downloads\web2py_src\web2py\gluon') >>>>>>>>> >>>>>>>>> from gluon import DAL >>>>>>>>> >>>>>>>>> dbpath = >>>>>>>>> r'C:\Users\Jon\Downloads\web2py_src\web2py\applications\xakemate_scratch\databases' >>>>>>>>> problemsuri = 'mysql://root:web2py@localhost/xakemate' >>>>>>>>> pos = 'test_position' >>>>>>>>> >>>>>>>>> db = DAL(problemsuri,folder=dbpath,auto_import=True) >>>>>>>>> query = (db.problem.fen == pos) >>>>>>>>> problem = db(query).select(orderby=db.problem.id).first() >>>>>>>>> >>>>>>>>> I get the following error: >>>>>>>>> >>>>>>>>> Traceback (most recent call last): >>>>>>>>> File "<pyshell#33>", line 1, in <module> >>>>>>>>> query = (dbproblems.problem.fen == posFEN) >>>>>>>>> File "C:\Python27\lib\site-packages\gluon\dal.py", line 7150, in >>>>>>>>> __getattr__ >>>>>>>>> return ogetattr(self, key) >>>>>>>>> AttributeError: 'DAL' object has no attribute 'problem' >>>>>>>>> >>>>>>>>> From what I understood the tables should be auto-imported and thus >>>>>>>>> known to my code. Why is it not finding the table? >>>>>>>>> >>>>>>>>> In addition to that I've got a couple of questions: >>>>>>>>> >>>>>>>>> 1st Should I import PYDAL (from pydal import DAL) or GLUON.DAL >>>>>>>>> (as I did)? >>>>>>>>> >>>>>>>>> 2nd Even when using the db different from SQLite should we point >>>>>>>>> to "applications/myapp/databases"? >>>>>>>>> >>>>>>>>> Thanks in advance, Jon. >>>>>>>>> >>>>>>>>> >>>>>>>>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> >>>>>>>>> Libre >>>>>>>>> de virus. www.avast.com >>>>>>>>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> >>>>>>>>> <#m_-8707854383032120839_m_6783791525782628683_m_3277063011711644748_m_1193084186208716646_m_538018860577941037_m_-719463918673358794_m_6847217380560436693_m_-8586355468110605202_m_-6215750885323799383_m_5787271047422858639_m_-5898545075541849496_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> 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+unsubscr...@googlegroups.com. >>>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>>> >>>>>>>> -- >>>>>>>> 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+unsubscr...@googlegroups.com. >>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>> >>>>>>> -- >>>>>>> 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+unsubscr...@googlegroups.com. >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>> -- >>>>> 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+unsubscr...@googlegroups.com. >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> -- >>>> 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+unsubscr...@googlegroups.com. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >>> 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+unsubscr...@googlegroups.com. >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- >> 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+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.