unable to create a new database in web2py...Syntax Error

Question

I created a new SQL web2py database that is mentioned below...But when I try to save it, it gives syntax error. How do i save it on web2py and use it for my webpages..

dbOBJECT = SQLDB("sqlite://dbOBJECT.db")

"""
Table definition
"""
dbOBJECT.define_table("USER DETAILS",
      SQLField("UID_GET ID", dbOBJECT.GET ID),
      SQLField("USERNAME_USER", dbOBJECT.USER),
      SQLField("ROLE ID_ROLES", dbOBJECT.ROLES),
      SQLField("FIRST NAME", "VARCHAR", length=100, notnull=True, default=None),
      SQLField("LAST NAME", "VARCHAR", length=100, notnull=True, default=None),
      SQLField("DATE OF BIRTH", "DATE", notnull=True, default=None),
      SQLField("GID_GENDER", dbOBJECT.GENDER),
      SQLField("BRANCH ID_BRANCH", dbOBJECT.BRANCH),
      SQLField("COUNTRY ID_COUNTRY", dbOBJECT.COUNTRY),
      SQLField("WORKING ID_WORKING", dbOBJECT.WORKING),
      SQLField("WORKED ID_WORKED ", dbOBJECT.WORKED ))


"""
Table definition
"""
dbOBJECT.define_table("GET ID",
      SQLField("UID", "INTEGER", notnull=True, default=None),
      SQLField("USERNAME_USER", dbOBJECT.USER),
      SQLField("new field", "INTEGER", notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("USER",
      SQLField("USERNAME", "VARCHAR", length=100, notnull=True, default=None),
      SQLField("EMAIL", "VARCHAR", length=100, default=None),
      SQLField("PASSWORD", "VARCHAR", length=20, notnull=True, default=None),
      SQLField("ROLE ID_ROLES", dbOBJECT.ROLES))


"""
Table definition
"""
dbOBJECT.define_table("COUNTRY",
      SQLField("COUNTRY ID", "VARCHAR", length=50, notnull=True, default=None),
      SQLField("COUNTRY", "VARCHAR", length=50, notnull=True, default=None),
      SQLField("CITY ID_CITY", dbOBJECT.CITY))


"""
Table definition
"""
dbOBJECT.define_table("ROLES",
      SQLField("ROLE ID", "INTEGER", notnull=True, default=None),
      SQLField("ROLE NAME", "VARCHAR", length=100, notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("WORKED ",
      SQLField("WORKED ID", "INTEGER", notnull=True, default=None),
      SQLField("PLACE ID_COMPANY", dbOBJECT.COMPANY),
      SQLField("DESIGNATION ID_DESIGNATION", dbOBJECT.DESIGNATION))


"""
Table definition
"""
dbOBJECT.define_table("CITY",
      SQLField("CITY ID", "INTEGER", notnull=True, default=None),
      SQLField("CITY", "VARCHAR", length=50, notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("GENDER",
      SQLField("GID", "INTEGER", notnull=True, default=None),
      SQLField("GENDER", "VARCHAR", length=10, notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("BRANCH",
      SQLField("BRANCH ID", "INTEGER", notnull=True, default=None),
      SQLField("BRANCH", "VARCHAR", length=50, notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("WORKING",
      SQLField("WORKING ID", "INTEGER", notnull=True, default=None),
      SQLField("PLACE ID_COMPANY", dbOBJECT.COMPANY),
      SQLField("DESIGNATION ID_DESIGNATION", dbOBJECT.DESIGNATION))


"""
Table definition
"""
dbOBJECT.define_table("USER CONTACT",
      SQLField("MOBILE NUMBER", "INTEGER", notnull=True, default=None),
      SQLField("FACEBOOK LINK", "VARCHAR", length=50, default=None),
      SQLField("TWITTER LINK", "VARCHAR", length=50, default=None),
      SQLField("LINKEDIN", "VARCHAR", length=50, default=None),
      SQLField("UID_GET ID", dbOBJECT.GET ID))


"""
Table definition
"""
dbOBJECT.define_table("COMPANY",
      SQLField("PLACE ID", "INTEGER", notnull=True, default=None),
      SQLField("PLACE", "VARCHAR", length=100, notnull=True, default=None))


"""
Table definition
"""
dbOBJECT.define_table("DESIGNATION",
      SQLField("DESIGNATION ID", "INTEGER", notnull=True, default=None),
      SQLField("DESIGNATION", "VARCHAR", length=50, notnull=True, default=None))


"""
Relations between tables (remove fields you don't need from requires)
"""
dbOBJECT.USER DETAILS.UID_GET ID.requires=IS_IN_DB(dbOBJECT, 'GET ID.id','GET ID.UID','GET ID.USERNAME_USER','GET ID.new field')
dbOBJECT.USER DETAILS.USERNAME_USER.requires=IS_IN_DB(dbOBJECT, 'USER.id','USER.USERNAME','USER.EMAIL','USER.PASSWORD','USER.ROLE ID_ROLES')
dbOBJECT.USER DETAILS.ROLE ID_ROLES.requires=IS_IN_DB(dbOBJECT, 'ROLES.id','ROLES.ROLE ID','ROLES.ROLE NAME')
dbOBJECT.USER DETAILS.GID_GENDER.requires=IS_IN_DB(dbOBJECT, 'GENDER.id','GENDER.GID','GENDER.GENDER')
dbOBJECT.USER DETAILS.BRANCH ID_BRANCH.requires=IS_IN_DB(dbOBJECT, 'BRANCH.id','BRANCH.BRANCH ID','BRANCH.BRANCH')
dbOBJECT.USER DETAILS.COUNTRY ID_COUNTRY.requires=IS_IN_DB(dbOBJECT, 'COUNTRY.id','COUNTRY.COUNTRY ID','COUNTRY.COUNTRY','COUNTRY.CITY ID_CITY')
dbOBJECT.USER DETAILS.WORKING ID_WORKING.requires=IS_IN_DB(dbOBJECT, 'WORKING.id','WORKING.WORKING ID','WORKING.PLACE ID_COMPANY','WORKING.DESIGNATION ID_DESIGNATION')
dbOBJECT.USER DETAILS.WORKED ID_WORKED .requires=IS_IN_DB(dbOBJECT, 'WORKED .id','WORKED .WORKED ID','WORKED .PLACE ID_COMPANY','WORKED .DESIGNATION ID_DESIGNATION')
dbOBJECT.GET ID.USERNAME_USER.requires=IS_IN_DB(dbOBJECT, 'USER.id','USER.USERNAME','USER.EMAIL','USER.PASSWORD','USER.ROLE ID_ROLES')
dbOBJECT.USER.ROLE ID_ROLES.requires=IS_IN_DB(dbOBJECT, 'ROLES.id','ROLES.ROLE ID','ROLES.ROLE NAME')
dbOBJECT.COUNTRY.CITY ID_CITY.requires=IS_IN_DB(dbOBJECT, 'CITY.id','CITY.CITY ID','CITY.CITY')
dbOBJECT.WORKED .PLACE ID_COMPANY.requires=IS_IN_DB(dbOBJECT, 'COMPANY.id','COMPANY.PLACE ID','COMPANY.PLACE')
dbOBJECT.WORKED .DESIGNATION ID_DESIGNATION.requires=IS_IN_DB(dbOBJECT, 'DESIGNATION.id','DESIGNATION.DESIGNATION ID','DESIGNATION.DESIGNATION')
dbOBJECT.WORKING.PLACE ID_COMPANY.requires=IS_IN_DB(dbOBJECT, 'COMPANY.id','COMPANY.PLACE ID','COMPANY.PLACE')
dbOBJECT.WORKING.DESIGNATION ID_DESIGNATION.requires=IS_IN_DB(dbOBJECT, 'DESIGNATION.id','DESIGNATION.DESIGNATION ID','DESIGNATION.DESIGNATION')
dbOBJECT.USER CONTACT.UID_GET ID.requires=IS_IN_DB(dbOBJECT, 'GET ID.id','GET ID.UID','GET ID.USERNAME_USER','GET ID.new field')

Any help is welcome... P.S. just a beginner..

Note, if you're getting an error, it helps to show the traceback to make it easier to pinpoint the location of the problem (especially with such a long code sample). — Anthony, Aug 08 '14 at 16:27
the error is at the first line containing SQLField....and it shows syntax error.. — codefreak, Aug 08 '14 at 16:41

Anthony · Answer 1 · 2014-08-08T21:08:36.637

1

First, table names and field names must be valid Python identifiers, so they cannot contain spaces. Instead, if you use underscores, they will automatically be converted to spaces in SQLFORM labels. You can also specify a separate "label" argument to the Field() constructor to create labels for forms and grids if you don't like the default labels.

Second, the field types should be lowercase, and there is no "varchar" field type -- instead it should be "string" (though "string" is the default, so not really necessary).

Finally, SQLDB has been deprecated in favor of DAL, and SQLField has been deprecated in favor of Field.

I strongly suggest reading the DAL documentation before proceeding.

edited Aug 08 '14 at 21:08

answered Aug 08 '14 at 16:26

Anthony

25,466
3
28
57

how do we access the database we have created? do we need to make changes to the original db.py – codefreak Aug 08 '14 at 16:38
Since none of the tables would have been created, it is probably easiest to just delete all the files in the app's /databases folder and start over with corrected code. – Anthony Aug 08 '14 at 21:09

unable to create a new database in web2py...Syntax Error

1 Answers1