def get_db(self):
"""
Return the connected db
"""
if not os.path.exists(self.get_option('dbmetadata')):
os.makedirs(self.get_option('dbmetadata'))
db=DAL(self.get_option('database'),lazy_tables=True,folder=self.get_option("dbmetadata"))
db.define_table('storages',
Field('storagename','string'),
Field('creation_ts','datetime',
default=datetime.datetime.now()),
Field('modified_ts','datetime',
default=datetime.datetime.now(),
update=datetime.datetime.now()),
)
db.define_table('files',
Field('storages_id',db.storages),
Field('parent_id','reference files'),
Field('filename','string'),
Field('description','string'),
Field('mime','string'),
Field('ftype','string'),
Field('mode','integer'),
Field('inode','integer'),
Field('dev','string'),
Field('nlink','integer'),
Field('uid','integer'),
Field('gid','integer'),
Field('size','double'),
Field('ctime','datetime'),
Field('mtime','datetime'),
Field('atime','datetime'),
)
return db
def copyDB(self):
other_db = DAL("%s://%s" % (
self.targetdbType, self.targetdbName), folder=self.targetFolder)
print 'creating tables...'
for table in self.db:
other_db.define_table(
table._tablename, *[field for field in table])
'''
should there be an option to truncAte target DB?
if yes, then change args to allow for choice
and set self.trancate to the art value
if self.truncate==True:
other_db[table._tablename].truncate()
'''
print 'exporting data...'
self.db.export_to_csv_file(open('tmp.sql', 'wb'))
print 'importing data...'
other_db.import_from_csv_file(open('tmp.sql', 'rb'))
other_db.commit()
print 'done!'
print 'Attention: do not run this program again or you end up with duplicate records'
def copyDB(self):
other_db = DAL("{0}://{1}".format(self.targetdbType,
self.targetdbName),
folder=self.targetFolder)
print 'creating tables...'
for table in self.db:
other_db.define_table(table._tablename,
*[field for field in table])
'''
should there be an option to truncAte target DB?
if yes, then change args to allow for choice
and set self.trancate to the art value
if self.truncate==True:
other_db[table._tablename].truncate()
'''
print 'exporting data...'
self.db.export_to_csv_file(open('tmp.sql', 'wb'))
print 'importing data...'
other_db.import_from_csv_file(open('tmp.sql', 'rb'))
other_db.commit()
print 'done!'
print 'Attention: do not run this program again or you end up with duplicate records'
def GetDocMeta( self, params = None ):
if params is None:
params = self.params
searchText = params["searchText"]
searchLimit = params["searchLimit"]
searchOffset = params["searchOffset"]
print "web2py"
start = time.clock()
# get data using web2py application layer
db = DAL('sqlite://doc-meta.sqlite', migrate_enabled=False)
db.define_table('DocMeta', Field('Key'), Field('DocContent'), Field('DocID'), primarykey=['Key'])
# build query using params
q = db.DocMeta.DocContent.like('%' + searchText + '%')
matches = db(q).count();
limitb = searchOffset + searchLimit
count = 0
pyResults = {}
queryResults = db(q).select(orderby=db.DocMeta.DocID, orderby_on_limitby = False, limitby=(searchOffset, limitb));
for row in queryResults:
count += 1
pyResults[row.DocID] = dict(row)
elapsed = (time.clock() -start)
print "elapsed = ", elapsed
print "exhaustive"
start = time.clock()
# get data using exhaustive search of JSON
filename = os.path.join( self.request.folder, 'data/corpus', 'doc-meta.json' )
with open( filename ) as f:
content = json.load( f, encoding = 'utf-8' )
results = {}
matchCount = 0
discardCount = 0
keys = sorted(content.keys())
for index in range(len(keys)):
obj = content[keys[index]]
docContent = obj["DocContent"]
if searchText in docContent:
matchCount += 1
if len(results.keys()) < searchLimit and discardCount >= searchOffset:
results[obj["DocID"]] = obj
elif discardCount < searchOffset:
discardCount += 1
elapsed = (time.clock() - start)
print "elapsed = ", elapsed
return {
"Documents" : pyResults,
"docCount" : len(pyResults),
"docMaxCount" : matches
}
def append():
db=DAL('sqlite://storage.sqlite', folder='/home/www-data/web2py/applications/tgmonitor/databases')
db.define_table('tg_load',
Field('check_date','datetime'),
Field('tg_number','integer', notnull=True),
Field('busy', 'integer'),
Field('installed', 'integer')
)
db.tg_load.insert(check_date='',tg_number=2, busy=45, installed=60)
db.commit()
def backup(self):
"""
Backup the database to a local SQLite database
@ToDo: Option to use a temporary DB in Postgres/MySQL as this takes
too long for a large DB
"""
import os
db = self.db
folder = "%s/databases/backup" % current.request.folder
# Create clean folder for the backup
if os.path.exists(folder):
import shutil
shutil.rmtree(folder)
import time
time.sleep(1)
os.mkdir(folder)
# Setup backup database
db_bak = DAL("sqlite://backup.db", folder=folder)
# Copy Table structure
for tablename in db.tables:
if tablename == "gis_location":
table = db[tablename]
fields = [
table[field] for field in table.fields
if field != "the_geom"
]
db_bak.define_table(tablename, *fields)
else:
db_bak.define_table(tablename, db[tablename])
# Copy Data
import csv
csv.field_size_limit(2**20 * 100) # 100 megs
filename = "%s/data.csv" % folder
file = open(filename, "w")
db.export_to_csv_file(file)
file.close()
file = open(filename, "r")
db_bak.import_from_csv_file(file, unique="uuid2") # designed to fail
file.close()
db_bak.commit()
# Pass handle back to other functions
self.db_bak = db_bak
def adding_new_fields(new_unique_field,changed_table):
"""
This function adds a new_uniwue_field into the changed_table , while keeping all the rest of
the properties of the table ubchanged
"""
database_string = "sqlite://storage.db"
old_database_folder = "%s/applications/%s/databases" % (WEB2PY_PATH, APP)
temp_db = DAL( database_string, folder = old_database_folder, migrate_enabled=True ,migrate = True)
new_field = Field(new_unique_field,"integer")
try:
changed_table_primary_key = db[changed_table]._primarykey
except KeyError:
changed_table_primary_key = None
temp_db.define_table(changed_table ,db[changed_table],new_field,primarykey = changed_table_primary_key)
return temp_db
def backup(self):
"""
Backup the database to a local SQLite database
@ToDo: Option to use a temporary DB in Postgres/MySQL as this takes
too long for a large DB
"""
import os
db = self.db
folder = "%s/databases/backup" % current.request.folder
# Create clean folder for the backup
if os.path.exists(folder):
import shutil
shutil.rmtree(folder)
import time
time.sleep(1)
os.mkdir(folder)
# Setup backup database
db_bak = DAL("sqlite://backup.db", folder=folder)
# Copy Table structure
for tablename in db.tables:
if tablename == "gis_location":
table = db[tablename]
fields = [table[field] for field in table.fields if field != "the_geom"]
db_bak.define_table(tablename, *fields)
else:
db_bak.define_table(tablename, db[tablename])
# Copy Data
import csv
csv.field_size_limit(2**20 * 100) # 100 megs
filename = "%s/data.csv" % folder
file = open(filename, "w")
db.export_to_csv_file(file)
file.close()
file = open(filename, "r")
db_bak.import_from_csv_file(file, unique="uuid2") # designed to fail
file.close()
db_bak.commit()
# Pass handle back to other functions
self.db_bak = db_bak
def adding_renamed_fields(table_name,old_field_name,new_field_name,attributes_to_copy):
"""
This function is used add a field in table mentioned while
renaming a field . The renamed field is added separately to the table with the
same properties as the original field.
"""
database_string = "sqlite://storage.db"
old_database_folder = "%s/applications/%s/databases" % (WEB2PY_PATH, APP)
temp_db = DAL( database_string, folder = old_database_folder, migrate_enabled=True ,migrate = True)
new_field = Field(new_field_name)
try:
table_primary_key = db[table_name]._primarykey
except KeyError:
table_primary_key = None
for attribute in attributes_to_copy:
exec_str = "new_field.%(attribute)s = db[table_name][old_field_name].%(attribute)s" % {"attribute":attribute}
exec exec_str in globals() , locals()
temp_db.define_table(table_name ,db[table_name],new_field,primarykey = table_primary_key)
return temp_db
class Config(object):
"""Build and read config on GAE or config.cfg file"""
def __init__(self, autogetconfig=True):
from gluon import current
self.request = current.request
self.ongae = self.request.env.web2py_runtime_gae
self.T = current.T
self.cache = current.cache
from gluon import DAL
if self.ongae:
self._db = DAL("google:datastore")
else:
self._db = DAL("sqlite://config_movuca.sqlite")
self.define_tables()
if autogetconfig:
self.get_config()
def define_tables(self):
from datamodel.setup import Setup
setup = Setup()
self.tables = filter(lambda item: not item.startswith("_"), dir(setup))
for table in self.tables:
entity = self._db.define_table(table,
*setup.__getattribute__(table),
**dict(migrate="config_%s.table" % table)
)
self.__setattr__(table, entity)
def set_default_config(self):
now = self.request.now
for table in self.tables:
self.__getattribute__(table).insert(setuptime=now)
self._db.commit()
def get_config(self, expire=300):
for table in self.tables:
result = self._db(self._db[table]).select(cache=(self.cache.ram, expire)).last()
self.__setattr__(table.split("_")[0],
result)
def get_list(self, table, option):
options = self.__getattribute__(table)[option]
assert isinstance(options, list)
if not "mailhide" in option:
return [(option.split(":")[0], str(self.T(option.split(":")[1]))) for option in options]
else:
return [(option.split(":")[0], str(option.split(":")[1])) for option in options]
def get_ticket_storage(app):
private_folder = apath('%s/private' % app, r=request)
db_string = open(os.path.join(private_folder, 'ticket_storage.txt')).read().replace('\r','').replace('\n','').strip()
tickets_table = 'web2py_ticket'
tablename = tickets_table + '_' + app
db_path = apath('%s/databases' % app, r=request)
from gluon import DAL
ticketsdb = DAL(db_string, folder=db_path, auto_import=True)
if not ticketsdb.get(tablename):
table = ticketsdb.define_table(
tablename,
Field('ticket_id', length=100),
Field('ticket_data', 'text'),
Field('created_datetime', 'datetime'),
)
return ticketsdb , ticketsdb.get(tablename)
def get_ticket_storage(app):
private_folder = apath('%s/private' % app, r=request)
db_string = open(os.path.join(private_folder, 'ticket_storage.txt')).read().replace('\r','').replace('\n','').strip()
tickets_table = 'web2py_ticket'
tablename = tickets_table + '_' + app
db_path = apath('%s/databases' % app, r=request)
from gluon import DAL
ticketsdb = DAL(db_string, folder=db_path, auto_import=True)
if not ticketsdb.get(tablename):
table = ticketsdb.define_table(
tablename,
Field('ticket_id', length=100),
Field('ticket_data', 'text'),
Field('created_datetime', 'datetime'),
)
return ticketsdb , ticketsdb.get(tablename)
def get_ticket_storage(app):
private_folder = apath("%s/private" % app, r=request)
db_string = (
open(os.path.join(private_folder, "ticket_storage.txt")).read().replace("\r", "").replace("\n", "").strip()
)
tickets_table = "web2py_ticket"
tablename = tickets_table + "_" + app
db_path = apath("%s/databases" % app, r=request)
from gluon import DAL
ticketsdb = DAL(db_string, folder=db_path, auto_import=True)
if not ticketsdb.get(tablename):
table = ticketsdb.define_table(
tablename,
Field("ticket_id", length=100),
Field("ticket_data", "text"),
Field("created_datetime", "datetime"),
)
return ticketsdb, ticketsdb.get(tablename)
class Application(Tkinter.Frame):
def __init__(self, master=None):
Tkinter.Frame.__init__(self, master)
self.grid()
self.db = DAL("sqlite://teste.sqlite")
ne = IS_NOT_EMPTY()
self.table = self.db.define_table("produto",
Field("nome", requires=ne),
Field("marca", requires=ne),
Field("peso", "double", requires=ne),
Field("valor", "double", requires=ne),
Field("quantidade", "integer", requires=ne),
)
self.create_form()
self.show_result()
def create_form(self):
self.form = Tkinter.LabelFrame(self, text="Cadastro", padx="30", pady="15")
self.form.pack(side=Tkinter.TOP)
self.vars = {}
# self.nome_label = Tkinter.Label(self.form, text="nome do produto")
# self.nome_label.pack()
# self.nome = Tkinter.Entry(self.form)
# self.nome.pack()
self.SQLTKFORM(self.table)
def show_result(self):
self.result = Tkinter.LabelFrame(self, text="Results", padx="30", pady="15")
self.result.pack(side=Tkinter.BOTTOM)
# query db for records
records = self.db(self.table).select()
if records:
self.records = Tkinter.Text(self.result)
lines = [",".join(self.table.fields), "_" * 80]
for record in records:
lines.append(",".join([str(record[field]) for field in self.table.fields]))
self.records.insert(1.0, "\n".join(lines))
else:
self.records = Tkinter.Label(self.result, text="Nothing found")
self.records.pack()
def SQLTKFORM(self, table, hidden="id"):
fields = table.fields
for field in fields:
if field not in hidden:
self.create_input(field, table[field].label)
self.save_button = Tkinter.Button(self.form,
text="Salvar",
command=self.save_to_db).pack()
def save_to_db(self):
try:
values = [(name, value()) for name, value in self.vars.items()]
record = self.table.validate_and_insert(**dict(values))
if record.errors:
self.db.rollback()
for error, error_message in record.errors.items():
label_text = getattr(self, error + "_label_text")
actual_text = label_text.get()
if not ":" in actual_text:
label_text.set(actual_text + ": " + error_message)
getattr(self, error).configure(bg="red")
return
except Exception:
raise Exception("something wrong....")
else:
self.db.commit()
self.reset_form()
def reset_form(self):
self.form.destroy()
self.create_form()
self.result.destroy()
self.show_result()
def create_input(self, name, text):
# LABEL TEXT IS VARIABLE
setattr(self, name + "_label_text", Tkinter.StringVar(self.form))
label_text = getattr(self, name + "_label_text")
label_text.set(text)
# LABEL
setattr(self, name + "_label", Tkinter.Label(self.form, textvariable=label_text))
# INPUT
setattr(self, name, Tkinter.Entry(self.form))
# PACK
getattr(self, name + "_label").pack()
getattr(self, name).pack()
# VARS
self.vars[name] = getattr(self, name).get
# coding: utf8
from gluon import *
from gluon.tools import Auth
from gluon import request, DAL, response, session
#from plugin_lazy_options_widget import lazy_options_widget
from plugin_suggest_widget import suggest_widget
from cascadedrop import CascadingSelect
from gluon.contrib.populate import populate
db = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])
auth = Auth(db)
db.define_table('school',
Field('name', requires=IS_NOT_EMPTY()),
auth.signature,
format='%(name)s'
)
db.define_table('department',
Field('school', db.school), #, requires=IS_IN_DB(db, db.school.id, '%(name)s')),
Field('name', requires=IS_NOT_EMPTY()),
Field('HOD', 'reference auth_user'),
auth.signature,
format='%(name)s'
)
db.define_table('program',
Field('department', 'reference department'),
Field('classification', requires=IS_IN_SET(['ND', 'HND', 'PRE-ND', 'PGD','NON-ND'])),
Field('the_option', label='Option'),
import sys, os, telnetlib, time, datetime
def get_circuits(s='',find_par=''):
pos = s.find(find_par)+35
return s[pos:pos+6].strip()
sys.path.append('/home/www-data/web2py')
from gluon import DAL, Field
db=DAL('sqlite://storage.sqlite', folder='/home/www-data/web2py/applications/tgmonitor/databases')
db.define_table('tg_load',
Field('check_date','datetime'),
Field('tg_number',length=17),
Field('busy', 'integer'),
Field('installed', 'integer')
)
host = '10.200.66.70'
port = '6000'
tn = telnetlib.Telnet(host,port)
tn.write('LGI:op="monitor",PWD ="dspoftk",SER="10.100.100.104---O&M System";')
ans = tn.read_until('END')
tn.write('DSP OFTK: LT=TG, TG=44, DT=AT;')
ans = tn.read_until('END')
#01.当py文件中中文时一定要使用上面的一个编码注释
from gluon import DAL, Field
import pyodbc
#02.设置字符串缺省的编码，如果不这样的话str无法使用
import sys
import json
from datetime import datetime
reload(sys)
sys.setdefaultencoding('utf-8')
str_db = u'mssql4://sa:1@localhost/BIDDING'
#03.连接需要用utf8字符集，这样返回的中文结果可直接解码
db = DAL(str_db,migrate_enabled=False)
print db._uri
print db._dbname
db.define_table('BankRecord',Field('Account'),Field('CompanyName'),Field('CreateDate'),Field('Money'),Field('Note'),Field('TradingTime'))
db.define_table('Customer',Field('Address1'),Field('Address2'),Field('CompanyName'),Field('CompanyPhone'),Field('ContactName'),Field('CreationDate'),Field('Email'),Field('EmployeeId'),Field('Fax'),Field('IsDelete'),Field('MobilePhone'),Field('Note'),Field('PassWord'),Field('Position'),Field('Type'),Field('UserName'),Field('ZipCode'))
db.define_table('Employee',Field('Address'),Field('Age'),Field('Code'),Field('CompanyPhone'),Field('CreationDate'),Field('DateOfBirth'),Field('Department'),Field('Email'),Field('EmergencyContactName'),Field('EmergencyContactPhone'),Field('EmergencyContactRelationship'),Field('HomePhone'),Field('IsDelete'),Field('MobilePhone'),Field('Name'),Field('Note'),Field('PassWord'),Field('Position'),Field('SexId'),Field('Type'),Field('UserName'))
db.define_table('Finance',Field('Activity'),Field('CreationDate'),Field('EmployeeId'),Field('Income'),Field('IsDelete'),Field('Note'),Field('ProjectCodeId'),Field('ProtocolCodeId'),Field('Spending'),Field('TargetId'),Field('TitleId'))
db.define_table('Log',Field('Agent'),Field('CreationDate'),Field('Ip'),Field('Kind'),Field('Note'),Field('UserId'))
db.define_table('Management',Field('Code'),Field('CreationDate'),Field('IsDelete'),Field('Name'))
db.define_table('MoneyType',Field('CreationTime'),Field('IsDelete'),Field('Name'))
db.define_table('ProjectCode',Field('CreationTime'),Field('EmployeeId'),Field('IsDelete'),Field('Option1'),Field('Option2'),Field('Option3'),Field('ProjectNumber'),Field('ProjectTypeId'),Field('ProtocolId'))
db.define_table('ProjectResource',Field('CreationTime'),Field('IsDelete'),Field('Name'))
db.define_table('Project',Field('Assistant'),Field('BuyerId'),Field('ChargeRate'),Field('CreationDate'),Field('EmployeeId'),Field('EntrustMoney'),Field('IsDelete'),Field('MakeOutDate'),Field('ManagementStyleId'),Field('Note'),Field('Package'),Field('ProjectCodeId'),Field('ProjectName'),Field('ProjectSourceId'),Field('ProjectTypeId'),Field('ProtocolCodeId'),Field('SigningDate'),Field('SourcesOfFundingId'),Field('StateId'),Field('WinningCompany'),Field('WinningMoney'))
db.define_table('ProjectStatus',Field('CreationTime'),Field('IsDelete'),Field('Name'))
db.define_table('ProtocolCode',Field('CreationTime'),Field('EmployeeId'),Field('IsDelete'),Field('ProtocolNumber'),Field('TypeId'))
db.define_table('ProtocolCodeType',Field('TypeCode'),Field('TypeId'),Field('TypeName'))
db.define_table('Suggest',Field('Content'),Field('CreationTime'),Field('IsDelete'),Field('UserId'))
db.define_table('Task',Field('CreationDate'),Field('Deadline'),Field('EmployeeId'),Field('IsDelete'),Field('Note'),Field('PlaceId'),Field('ProjectId'),Field('StateId'),Field('TitleId'))
db.define_table('TaskLocation',Field('CreationTime'),Field('IsDelete'),Field('Name'))
#db = DAL('sqlite://storage.sqlite', folder='../databases')
db = DAL('sqlite://storage.sqlite')
migrate=True
db.define_table('patient',
#Field('mrn', type='string', length=10, required=True, unique=True, label=T('Medical Rec #')),
Field('mrn', type='string', length=10, required=True, unique=True),
Field('date_of_birth', type='datetime'),
Field('fname', type='string', length=25),
Field('mname', type='string', length=10),
Field('lname', type='string', length=25),
Field('ethnicity', type='string', length=8),
Field('primary_tx', type='string', length=8),
Field('marital_status', type='string', length=8),
Field('review_status', type='string', length=8),
Field('review_date', type='datetime'),
Field('review_uid', type='string', length=12),
Field('memo', type='string', length=255),
Field('create_date', type='datetime'),
Field('create_uid', type='string', length=12),
Field('modify_date', type='datetime'),
Field('modify_uid', type='string', length=12),
migrate=migrate)
#
# Family History for patient - references patient tbl
# define parent reference here
#
db.define_table('fhist',
msg['To'] = send_email
me = 'Emilius<*****@*****.**>'
to = '*****@*****.**'
mailSrv = smtplib.SMTP("mail.ru", 25)
mailSrv.ehlo()
mailSrv.login('*****@*****.**', 'passsswordd')
mailSrv.sendmail(me, to, msg.as_string())
mailSrv.close()
sys.path.append('/home/www-data/web2py')
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',
folder='/home/www-data/web2py/applications/tgmonitor/databases')
db.define_table('tg_load', Field('check_date', 'datetime'),
Field('tg_number', length=17), Field('busy', 'integer'),
Field('installed', 'integer'))
db.define_table(
'tg_list',
Field('tg_group', length=12, default='group1'),
Field('tg_number', length=17, notnull=True),
Field('tg_descr', length=17),
Field('tg_type', length=3, default='sip'),
Field('color', length=16, default='black'),
Field('alarm', 'integer', default=90),
Field('alarm_email', length=20),
Field('alarm_call_number', length=32),
)
h = height - 20
old_database_folder = "%s/applications/%s/databases" % (WEB2PY_PATH, APP)
temp_db = DAL( database_string, folder = old_database_folder, migrate_enabled=True ,migrate = True)
#MIGRATION SCRIPT
list_of_fields = []
list_of_fields.append(Field(new_field,"integer"))
list_of_new_table_fields = []
list_of_new_table_fields.append(Field(new_table_field,"integer"))
try:
db[changed_table]._primarykey
except KeyError:
db[changed_table]._primarykey = None
temp_db.define_table(changed_table ,db[changed_table],*list_of_fields,primarykey = db[changed_table]._primarykey)
temp_db.define_table(new_table , *list_of_new_table_fields)
temp_db.commit()
#Adding a new field of int type in a class
for old_row in temp_db().select(temp_db[changed_table][old_field]):
if (len(temp_db(temp_db[new_table][new_table_field] == old_row[old_field]).select()) == 0):
row = temp_db[new_table].insert(name = old_row[old_field])
new_id = int(row["id"])
temp_db(temp_db[changed_table][old_field] == old_row[old_field]).update(type_id = new_id)
temp_db.commit()
def backup(self):
"""
Backup the database to a local SQLite database
@ToDo: Option to use a temporary DB in Postgres/MySQL as this takes
too long for a large DB
"""
moves = self.moves
news = self.news
strints = self.strints
strbools = self.strbools
if not moves and not news and not strbools and not strints:
# Nothing to backup
return
import os
db = self.db
folder = "%s/databases/backup" % current.request.folder
# Create clean folder for the backup
if os.path.exists(folder):
shutil.rmtree(folder)
import time
time.sleep(1)
os.mkdir(folder)
# Setup backup database
db_bak = DAL("sqlite://backup.db", folder=folder, adapter_args={"foreign_keys": False})
# Copy Table structure
skip = []
for tablename in db.tables:
if tablename == "gis_location":
table = db[tablename]
fields = [table[field] for field in table.fields if field != "the_geom"]
try:
db_bak.define_table(tablename, *fields)
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
skip.append(tablename)
else:
try:
db_bak.define_table(tablename, db[tablename])
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
skip.append(tablename)
while skip:
_skip = []
for tablename in skip:
if tablename == "gis_location":
table = db[tablename]
fields = [table[field] for field in table.fields if field != "the_geom"]
try:
db_bak.define_table(tablename, *fields)
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
_skip.append(tablename)
except:
import sys
print "Skipping %s: %s" % (tablename, sys.exc_info()[1])
else:
try:
db_bak.define_table(tablename, db[tablename])
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
_skip.append(tablename)
except:
import sys
print "Skipping %s: %s" % (tablename, sys.exc_info()[1])
skip = _skip
# Which tables do we need to backup?
tables = []
if moves:
for tablename in moves:
tables.append(tablename)
if news:
for tablename in news:
new = news[tablename]
for t in new["tables"]:
tables.append(t)
for s in new["supers"]:
tables.append(s)
stable = db[s]
rows = db(stable._id > 0).select(stable.instance_type)
instance_types = set([r.instance_type for r in rows])
for t in instance_types:
tables.append(t)
if strbools:
for tablename, fieldname in strints:
tables.append(tablename)
if strints:
for tablename, fieldname in strints:
tables.append(tablename)
# Remove duplicates
tables = set(tables)
# Copy Data
import csv
csv.field_size_limit(2**20 * 100) # 100 megs
for tablename in tables:
filename = "%s/%s.csv" % (folder, tablename)
file = open(filename, "w")
rows = db(db[tablename].id > 0).select()
rows.export_to_csv_file(file)
file.close()
file = open(filename, "r")
db_bak[tablename].import_from_csv_file(file, unique="uuid2") # uuid2 designed to not hit!
file.close()
db_bak.commit()
# Pass handle back to other functions
self.db_bak = db_bak
import sys
sys.path.append('/home/www-data/web2py')
from gluon import DAL, Field
db4=DAL('postgres://*****:*****@localhost:5433/vpak',migrate_enabled=False, pool_size=20, lazy_tables=True, fake_migrate_all=False)
db4.define_table('geocode',
Field('zip', 'string'),
Field('city', 'string'),
Field('state','string'),
Field('longitude','string'),
Field('latitude','string'))
flat = '88.10'
flon = '47.1'
fradius = 50
rows = db4.executesql("select city,longitude,latitude, radians(cast(latitude as float)),radians({0}), radians({1}) from geocode where city like 'Bolingbrook'".format(flat, flon))
print rows[1]
# -*- coding: utf-8 -*-
import sys, os, telnetlib, time, datetime
def get_circuits(s='', find_par=''):
pos = s.find(find_par) + 35
return s[pos:pos + 6].strip()
sys.path.append('/home/www-data/web2py')
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',
folder='/home/www-data/web2py/applications/tgmonitor/databases')
db.define_table('tg_load', Field('check_date', 'datetime'),
Field('tg_number', length=17), Field('busy', 'integer'),
Field('installed', 'integer'))
host = '10.200.66.70'
port = '6000'
tn = telnetlib.Telnet(host, port)
tn.write('LGI:op="monitor",PWD ="dspoftk",SER="10.100.100.104---O&M System";')
ans = tn.read_until('END')
tn.write('DSP OFTK: LT=TG, TG=44, DT=AT;')
ans = tn.read_until('END')
_busy = get_circuits(ans, 'Busy')
_ins_num = get_circuits(ans, 'Installation number')
last = db.tg_load.check_date.max()
CREATE TABLE Category (
IdCategory INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name VARCHAR( 60 ) NOT NULL,
Url VARCHAR( 60 ) NOT NULL
UNIQUE
);
CREATE INDEX idx_CategoryUrl ON Category (
Url COLLATE NOCASE ASC
);
"""
db.define_table('category',
Field('idcategory', type='id'),
Field('name'),
Field('url'))
"""
User
Fields: IdUser (integer) PRIMARY KEY AUTO INCREMENT, RealName (string), DateOfBirth (date), UserName (string(120)) UNIQUE INDEX
Computed: Age (integer)
IdUser is set to be hidden from the forms (writable=False, readable=False). Setting default=None allows the database to
generate a unique primary key rather than relying on web2py to generate this.
Age is as the number of years from request.now and the birthdate stored in the record.
migrate=True)
# Migration Script
list_of_fields = []
list_of_fields.append(Field(new_field, "integer"))
list_of_new_table_fields = []
list_of_new_table_fields.append(Field(new_table_field, "integer"))
try:
db[changed_table]._primarykey
except KeyError:
db[changed_table]._primarykey = None
temp_db.define_table(changed_table,
db[changed_table],
*list_of_fields,
primarykey=db[changed_table]._primarykey)
temp_db.define_table(new_table, *list_of_new_table_fields)
temp_db.commit()
# Add a new field of int type in a class
for old_row in temp_db().select(temp_db[changed_table][old_field]):
if (len(
temp_db(temp_db[new_table][new_table_field] ==
old_row[old_field]).select()) == 0):
row = temp_db[new_table].insert(name=old_row[old_field])
new_id = int(row["id"])
temp_db(
temp_db[changed_table][old_field] == old_row[old_field]).update(
type_id=new_id)
pass # directory already created
for img in listdir(repo_img_dir):
if splitext(img)[1] == '.jpg':
copy(join(repo_img_dir, img), wa_img_dir)
# Connect to database
db_dir = abspath(join(dirname(__file__), pardir, 'databases'))
try:
makedirs(db_dir)
except os.error:
pass
db = DAL('sqlite://../databases/storage.db', folder=db_dir)
# Create images table.
db.define_table('images',
Field('represent', type='string', length=100, required=True),
Field('file_name', type='string', length=100, required=True))
# Insert image file names and meanings.
img_pattern = re.compile(r'(\D+?)\d*\.')
for img in listdir(wa_img_dir):
match = img_pattern.match(img)
if match is not None:
db.images.insert(represent=match.group(1).capitalize(), file_name=img)
# Create scores table.
db.define_table('scores', Field('mode', type='string', length=6,
required=True),
Field('difficulty', type='string', length=6, required=True),
Field('name', type='string', length=100, required=True),
Field('score', type='integer', required=True),
msg['To'] = send_email
me='Emilius<*****@*****.**>'
to='*****@*****.**'
mailSrv=smtplib.SMTP("mail.ru",25)
mailSrv.ehlo()
mailSrv.login('*****@*****.**','passsswordd')
mailSrv.sendmail(me, to, msg.as_string())
mailSrv.close()
sys.path.append('/home/www-data/web2py')
from gluon import DAL, Field
db=DAL('sqlite://storage.sqlite', folder='/home/www-data/web2py/applications/tgmonitor/databases')
db.define_table('tg_load',
Field('check_date','datetime'),
Field('tg_number',length=17),
Field('busy', 'integer'),
Field('installed', 'integer')
)
db.define_table('tg_list',
Field('tg_group', length=12, default='group1'),
Field('tg_number', length=17, notnull=True),
Field('tg_descr', length=17),
Field('tg_type', length=3, default='sip'),
Field('color', length=16, default='black'),
Field('alarm', 'integer', default=90),
Field('alarm_email', length=20),
Field('alarm_call_number', length=32),
)
class Application(Tkinter.Frame):
def __init__(self, master=None):
Tkinter.Frame.__init__(self, master)
self.grid()
self.define_table()
self.create_form()
def define_table(self):
self.db = DAL("sqlite://produtos.sqlite")
self.table = self.db.define_table("produto",
Field("nome"),
Field("marca"),
Field("peso", "double", label="Peso do produto"),
Field("valor", "double"),
Field("quant", "integer"),
)
def create_form(self):
self.vars = {}
self.form = Tkinter.LabelFrame(self, text="Cadastro de produtos", padx="30", pady="15")
self.form.pack(side=Tkinter.TOP)
self.SQLTKFORM(self.table, self.form)
def SQLTKFORM(self, table, master, hidden=['id']):
fields = table.fields
for field in fields:
if field not in hidden:
self.create_input(field, table[field].label, master)
master.save_button = Tkinter.Button(master,
text="Salvar",
command=self.save_to_db).pack()
def save_to_db(self):
try:
values = [(field, value()) for field, value in self.vars.items()]
self.table.insert(**dict(values))
except Exception:
self.db.rollback()
else:
self.db.commit()
self.reset_form()
def reset_form(self):
self.form.destroy()
self.create_form()
def create_input(self, field, label, master):
# self.label = Tkinter.Label(master, text=label)
# label.pack()
setattr(self, field+"_label", Tkinter.Label(master, text=label))
getattr(self, field+"_label").pack()
setattr(self, field, Tkinter.Entry(master))
getattr(self, field).pack()
self.vars[field] = getattr(self, field).get
def backup(self):
"""
Backup the database to a local SQLite database
@ToDo: Option to use a temporary DB in Postgres/MySQL as this takes
too long for a large DB
"""
moves = self.moves
news = self.news
strints = self.strints
strbools = self.strbools
if not moves and not news and not strbools and not strints:
# Nothing to backup
return
import os
db = self.db
folder = "%s/databases/backup" % current.request.folder
# Create clean folder for the backup
if os.path.exists(folder):
import shutil
shutil.rmtree(folder)
import time
time.sleep(1)
os.mkdir(folder)
# Setup backup database
db_bak = DAL("sqlite://backup.db", folder=folder, adapter_args={"foreign_keys": False})
# Copy Table structure
skip = []
for tablename in db.tables:
if tablename == "gis_location":
table = db[tablename]
fields = [table[field] for field in table.fields if field != "the_geom"]
try:
db_bak.define_table(tablename, *fields)
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
skip.append(tablename)
else:
try:
db_bak.define_table(tablename, db[tablename])
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
skip.append(tablename)
while skip:
_skip = []
for tablename in skip:
if tablename == "gis_location":
table = db[tablename]
fields = [table[field] for field in table.fields if field != "the_geom"]
try:
db_bak.define_table(tablename, *fields)
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
_skip.append(tablename)
except:
import sys
print "Skipping %s: %s" % (tablename, sys.exc_info()[1])
else:
try:
db_bak.define_table(tablename, db[tablename])
except KeyError:
# Can't resolve reference yet
# Cleanup
del db_bak[tablename]
# Try later
_skip.append(tablename)
except:
import sys
print "Skipping %s: %s" % (tablename, sys.exc_info()[1])
skip = _skip
# Which tables do we need to backup?
tables = []
if moves:
for tablename in moves:
tables.append(tablename)
if news:
for tablename in news:
new = news[tablename]
for t in new["tables"]:
tables.append(t)
for s in new["supers"]:
tables.append(s)
stable = db[s]
rows = db(stable._id > 0).select(stable.instance_type)
instance_types = set([r.instance_type for r in rows])
for t in instance_types:
tables.append(t)
if strbools:
for tablename, fieldname in strints:
tables.append(tablename)
if strints:
for tablename, fieldname in strints:
tables.append(tablename)
# Remove duplicates
tables = set(tables)
# Copy Data
import csv
csv.field_size_limit(2**20 * 100) # 100 megs
for tablename in tables:
filename = "%s/%s.csv" % (folder, tablename)
file = open(filename, "w")
rows = db(db[tablename].id > 0).select()
rows.export_to_csv_file(file)
file.close()
file = open(filename, "r")
db_bak[tablename].import_from_csv_file(file, unique="uuid2") # uuid2 designed to not hit!
file.close()
db_bak.commit()
# Pass handle back to other functions
self.db_bak = db_bak
from gluon import DAL, Field
#db = DAL('sqlite://storage.sqlite')
db = DAL('postgres://*****:*****@db/postgres')
db.define_table('ipligence2', Field('ip_from', 'integer', 10, '0000000000'),
Field('ip_to', 'integer', 10, '0000000000'),
Field('country_code', 'string', 10),
Field('country_name', 'string', 255),
Field('continent_code', 'string', 10),
Field('continent_name', 'string', 255),
Field('time_zone', 'string', 10),
Field('region_code', 'string', 10),
Field('region_name', 'string', 255),
Field('the_owner', 'string', 255),
Field('city_name', 'string', 255),
Field('county_name', 'string', 255),
Field('latitude', 'double'), Field('longitude', 'double'))
db.ipligence2.import_from_csv_file(
open(
'/home/www-data/web2py/applications/TrackR/private/ipligence-max.mysqldump.sql'
), 'rb')
db.commit()
pass # directory already created
for img in listdir(repo_img_dir):
if splitext(img)[1] == '.jpg':
copy(join(repo_img_dir, img), wa_img_dir)
# Connect to database
db_dir = abspath(join(dirname(__file__), pardir, 'databases'))
try:
makedirs(db_dir)
except os.error:
pass
db = DAL('sqlite://../databases/storage.db', folder=db_dir)
# Create images table.
db.define_table('images',
Field('represent', type='string', length=100, required=True),
Field('file_name', type='string', length=100, required=True))
# Insert image file names and meanings.
img_pattern = re.compile(r'(\D+?)\d*\.')
for img in listdir(wa_img_dir):
match = img_pattern.match(img)
if match is not None:
db.images.insert(
represent=match.group(1).capitalize(),
file_name=img)
# Create scores table.
db.define_table('scores',
Field('mode', type='string', length=6, required=True),
Field('difficulty', type='string', length=6, required=True),
from gluon.tools import *
# reload(sys)
# sys.setdefaultencoding('utf-8')
str_db = u'mssql4://sa:sohunj123@localhost/BIDDING'
#03.连接需要用utf8字符集，这样返回的中文结果可直接解码
db = DAL(str_db,migrate_enabled=False)
dbu = DAL(str_db,migrate_enabled=False)
auth = Auth(dbu)
auth.settings.extra_fields['auth_user']= [Field('chinesename'), Field('code')]
auth.define_tables(username=True)
crud = Crud(dbu)
print db._uri
db.define_table('BankRecord',Field('Account'),Field('CompanyName'),Field('CreateDate'),Field('Money'),Field('Note'),Field('TradingTime'))
db.define_table('BiddingCountType',Field('BiddingCountTypeCode'),Field('BiddingCountTypeId'),Field('BiddingCountTypeName'))
db.define_table('BiddingSiteStatisticType',Field('BiddingSiteStatisticTypeCode'),Field('BiddingSiteStatisticTypeId'),Field('BiddingSiteStatisticTypeName'))
db.define_table('Columns_ProtocolCode',Field('columnlabel'),Field('columnname'),Field('typename'))
db.define_table('Customer',Field('Address1'),Field('Address2'),Field('CompanyName'),Field('CompanyPhone'),Field('ContactName'),Field('CreationDate'),Field('Email'),Field('EmployeeId'),Field('Fax'),Field('IsDelete'),Field('MobilePhone'),Field('Note'),Field('PassWord'),Field('Position'),Field('Type'),Field('UserName'),Field('ZipCode'))
db.define_table('Employee',Field('Address'),Field('Age'),Field('Code'),Field('CompanyPhone'),Field('CreationDate'),Field('DateOfBirth'),Field('Department'),Field('Email'),Field('EmergencyContactName'),Field('EmergencyContactPhone'),Field('EmergencyContactRelationship'),Field('HomePhone'),Field('IsDelete'),Field('MobilePhone'),Field('Name'),Field('Note'),Field('PassWord'),Field('Position'),Field('SexId'),Field('Type'),Field('UserName'))
db.define_table('Finance',Field('Activity'),Field('CreationDate'),Field('EmployeeId'),Field('Income'),Field('IsDelete'),Field('Note'),Field('ProjectCodeId'),Field('ProtocolCodeId'),Field('Spending'),Field('TargetId'),Field('TitleId'))
db.define_table('Log',Field('Agent'),Field('CreationDate'),Field('Ip'),Field('Kind'),Field('Note'),Field('UserId'))
db.define_table('Management',Field('Code'),Field('CreationDate'),Field('IsDelete'),Field('Name'))
db.define_table('ManagementStyle',Field('ManagementStyleCode'),Field('ManagementStyleId'),Field('ManagementStyleName'))
db.define_table('FundingSource',Field('CreationTime'),Field('IsDelete'),Field('Name'))
db.define_table('OperationType',Field('OperationTypeCode'),Field('OperationTypeId'),Field('OperationTypeName'))
db.define_table('ProjectProperty',Field('ProjectPropertyCode'),Field('ProjectPropertyId'),Field('ProjectPropertyName'))
db.define_table('Project',Field('Assistant'),Field('ContactorNameId'),Field('CustomerId'),Field('CreationDate',type='datetime',default=request.now),Field('EmployeeId',default=auth.user_id),Field('IsDelete'),Field('ManagementStyleId'),Field('PurchaseStyleId'),Field('Note'),Field('ProjectCode'),Field('ProjectName'),Field('ProjectSourceId'),Field('ProjectTypeId'),Field('ProtocolCodeId'),Field('FundingSourceId'),Field('ProjectStatusId'), Field('ProjectPropertyId'))
db.define_table('ProjectCode',Field('CreationTime'),Field('EmployeeId'),Field('IsDelete'),Field('Option1'),Field('Option2'),Field('Option3'),Field('ProjectNumber'),Field('ProjectTypeId'),Field('ProtocolId'))
db.define_table('ProjectPackage',Field('NoticeDate'),Field('ChargeRate'),Field('OpenDate'),Field('ReviewDate'),Field('PublicDate'),Field('CreationDate',type='datetime',default=request.now),Field('EntrustMoney'),Field('IsDelete'),Field('MakeOutDate'),Field('Note'),Field('PackageName'),Field('PackageNumber'),Field('ProjectId'),Field('SigningDate'),Field('StateId'),Field('WinningCompany'),Field('WinningMoney'), Field('SecondPublicDate'), Field('comment'), Field('WinningDollar'))
import sys
import datetime
sys.path.append("../../")
from gluon import DAL, Field
db = DAL('mysql://*****:*****@localhost/test',migrate_enabled=True)
db.define_table('mytable', Field('temperature','double'),Field('pressure','double'),Field('dates','date'),\
Field('time','time'),format='%(content)s',migrate=True)
print db._uri
print db._dbname
print db.mytable.fields
#print db.field
print db.tables
# insert data
json_string = "{ \
'first': 1,\
'second': 2,\
'date': '2016-2-6',\
'temperature': '37.5',\
'unit': 'km'\
}";
#auth.define_tables(migrate=False)
#db.mytable.insert(temperature = 36.5,pressure = 90)
db.mytable.insert(temperature = 36.5,pressure = 90,dates = datetime.datetime.now().date(), \
time= datetime.datetime.now().time())
#db.mytable.insert()
db.commit()
#select
