def deleteAllCategories(self):
session = self.session()
for cat in session.query(Category):
session.delete(cat) #tasks get deleted cascade
session.commit()
def install_chat(session, PORT):
"""
This is a procedure for first setup chat application.
It use the command line for setup first user - root user, whom will has fool permissions.
"""
print "It's first run of the CHAT SERVER.\nSo you need to answer few questions..."
login = raw_input("Please type the nick-name (Login) of the root user: "******"\nPlease type your fool name (Screen Name):")
password = make_server_password()
root_perm = Perm("root", 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
admin_perm = Perm("admin", 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0)
authorised_perm = Perm("auth_user", 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0)
guest_perm = Perm("guest", 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0)
default_room = Room("default")
a = Associations()
a.user = User(login, name, password, 1)
a.perm = root_perm
default_room.user.append(a)
session.add(a)
session.add(root_perm)
session.add(admin_perm)
session.add(authorised_perm)
session.add(guest_perm)
session.add(default_room)
session.commit()
session.close()
print "\nThanks."
print "\nChat server started on port " + str(PORT)
logging.info("chat was installed")
def auth_user(login, password, type):
"""
@rtype : object
"""
session = Session()
users = session.query(User).order_by(User.id)
users_logins = []
for instance in users:
users_logins.append(instance.login)
for instance in users:
if login in users_logins and login == instance.login:
user = instance
if not check_pass(user, password):
return False
else:
return start_sys(user, session)
elif not login in users_logins:
if type == "guest":
default_room = session.query(Room).filter(Room.name == "default").one()
guest_perm = session.query(Perm).filter(Perm.name == "guest").one()
a = Associations()
a.user = User(login, login, None, 0)
u = a.user
a.perm = guest_perm
default_room.user.append(a)
session.add(a)
session.commit()
return start_sys(u, session)
else:
continue
session.commit()
session.close()
def session_scope(versioned=True, ignore_soft_deletes=True, namespace_id=None):
""" Provide a transactional scope around a series of operations.
Takes care of rolling back failed transactions and closing the session
when it goes out of scope.
Note that sqlalchemy automatically starts a new database transaction when
the session is created, and restarts a new transaction after every commit()
on the session. Your database backend's transaction semantics are important
here when reasoning about concurrency.
Parameters
----------
versioned : bool
Do you want to enable the transaction log?
ignore_soft_deletes : bool
Whether or not to ignore soft-deleted objects in query results.
namespace_id : int
Namespace to limit query results with.
Yields
------
InboxSession
The created session.
"""
session = InboxSession(versioned, ignore_soft_deletes, namespace_id)
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()
def addTask(self, p_TaskText, p_CategoryId):
task = Task(p_TaskText, p_CategoryId)
session = self.session()
session.save(task)
session.commit()
return task.id
def addCategory(self, p_CategoryTitle):
category = Category(p_CategoryTitle)
session = self.session()
session.save(category)
session.commit()
return category.id
def deleteCategory(self, p_CategoryId):
session = self.session()
query = session.query(Category).filter(Category.id==p_CategoryId)
category = query.first()
session.delete(category)
session.commit()
def deleteTask(self, p_TaskId):
session = self.session()
query = session.query(Task).filter(Task.id==p_TaskId)
task = query.first()
session.delete(task)
session.commit()
def upgrade():
session = sa.orm.session.Session(bind=op.get_bind())
if session.query(TagCategory).count() == 0:
category = TagCategory()
category.name = 'default'
category.color = 'default'
category.version = 1
category.default = True
session.add(category)
session.commit()
def editTask(self, p_TaskId, p_TaskText):
session = self.session()
query = session.query(Task).filter(Task.id==p_TaskId)
task = query.first()
task.tasktext = p_TaskText
session.update(task)
session.commit()
def editCategory(self, p_CategoryId, p_CategoryText):
session = self.session()
#this is probably not needed for an update...see SA docs again
query = session.query(Category).filter(Category.id==p_CategoryId)
cat = query.first()
cat.categorytitle = p_CategoryText
session.update(cat)
session.commit()
def get_or_create(session, model, **kwargs):
instance = session.query(model).filter_by(**kwargs).first()
if instance:
return instance
else:
params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
instance = model(**params)
session.add(instance)
session.flush()
session.commit()
return instance
def insertBIN(line=binLine, bin="1076262"):
a = line.split('|')
count = 0
for example in a:
count = count + 1
#print str(example)
print count
print a[0] + " " + a[1] + " " + str(bin)
insBIN = Bisapp_Building(bin=bin, houseno=a[1], street=a[2], borough=a[3])
session.merge(insBIN)
session.commit()
return
def downgrade():
session = sa.orm.session.Session(bind=op.get_bind())
default_category = (
session
.query(TagCategory)
.filter(TagCategory.name == 'default')
.filter(TagCategory.color == 'default')
.filter(TagCategory.version == 1)
.filter(TagCategory.default == 1)
.one_or_none())
if default_category:
session.delete(default_category)
session.commit()
def insertECB(line=ecbLine, bin="1076262"):
a = line.split('|')
count = 0
for example in a:
count = count + 1
ins1 = Bisapp_Ecb(bin=bin, ExtEcbViolNo=a[1], ExtActiveFlag=a[2],
ExtRespName=a[3], ExtBobViolNo=a[4], ExtLicenseNo=a[5],
ExtCompStatusExp=a[6], ExtHearingStatusExp=a[7],
ExtUnitCodeExp=a[8], ExtViolTypeExp=a[9], ExtBalanceDue=a[10],
ExtViolIssueDate=a[10], CompSeverity=a[11], InfrLine=a[12], )
session.merge(ins1)
session.commit()
return
def insertPermit(line = permitsLine, bin = 1076262):
a = line.split('|')
count = 0
for example in a:
count = count + 1
ins1 = Bisapp_Permits ( PrJobNo = a[1] , PrSubType = a[2], PrSubsubtype = a[3] , PrSeqNo = a[4],
PrIssdt = a[5], PrStatusDsc = a[6], PrApplicant = a[7], PrFilingDate= a[8], JobType = a[9],
Job01Isn1x03 = a[10], PermitIsn1x03 = a[11], PtExpDate = a[12])
print a[1]
print a[10]
session.merge(ins1)
session.commit()
return
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
user = session.query(User).get(1) # 根據ID查詢
user = User(uid=4, name="沐言童趣的技術總監-李興華", age=3) # 瞬時態
session.merge(user) # 由瞬時態進入到了預備態
session.commit() # 由預備態進入到持久態
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def transactional(auto_close=True):
"""Provide a transactional scope around a series of operations."""
using_scope = CONF.portal.using_scope
session = get_session()
try:
yield session
if not using_scope: session.commit()
except:
if not using_scope: session.rollback()
raise
finally:
if auto_close and not using_scope:
session.close()
def funcion_create(funcion):
query = """INSERT INTO `car_db`.`funcion`
(`fecha_creacion`,`fecha_modificacion`,`nombre`,`fechaInicio`,`horaInicio`,`id_pelicula`,`id_formato`,`id_sala`)
VALUES(CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'{nombre}','{fechaInicio}','{horaInicio}',{peliculaId},{formatoId},{salaId})
""".format(
nombre=funcion['nombre'],
fechaInicio=
f"{funcion['fechaAnio']}-{funcion['fechaMes']}-{funcion['fechaDia']}",
horaInicio=f"{funcion['hora']}:{funcion['minuto']}:00",
peliculaId=funcion['peliculaId'],
formatoId=funcion['formatoId'],
salaId=funcion['salaId'])
session.execute(query)
session.commit()
def insertJobWT(line=jobWorkType, Pra3Isn="0002004251"):
a = line.split('|')
wds = a[2]
if wds == '':
datew = None
else:
datew = parser.parse(a[2], dayfirst=False, yearfirst=False)
insJobWorkType = Bisapp_JobWorkType(CompKey=a[0], Pra3Isn=Pra3Isn, WJobDocumentNumber=a[1], WStatusDate=datew, WWorkStatusDef=a[3], WWorkSuffixExpanded=a[4],
SignoffLinkFlag=a[5], WBtu=a[6], WFuelType=a[7], WBox5Exp=a[8])
session.merge(insJobWorkType)
session.commit()
return
def addjob(session,jlist):
for j in jlist :
session.add(j)
session.commit()
class Jobs(Base):
_tablename_ = 'Jobs'
id = column(Integer,primary_key = True)
jen_id = Column(Integer)
name = Column(String)
timeStamp = Column(DateTime)
result = Column(String)
building = Column(String)
estimatedDuration = Column(String)
def write_stock(stock):
session = Session()
for c in stock:
print(c)
stock_filling = Stock(
stock_name=(stock[0]),
stock_ticker=(stock[1]),
stockable=True,
mark_cap=stock[3],
)
print('printing...', stock_filling)
session.add(stock_filling)
session.commit()
session.close()
def precio_create():
precio = session.query(Precio).first()
tipo = session.query(TipoPrecio).filter(
TipoPrecio.id == precio.tipoPrecio.id).first()
session.query(Precio).filter(Precio.tipoPrecio == precio.tipoPrecio
and Precio.activo == True).update(
dict(activo=False))
session.add(
Precio(nombre="A",
codigo="A",
valor=333,
id_tipoPrecio=tipo.id,
activo=True))
session.add(precio)
session.commit()
def add_u_to_the_r(uname, room_name, perm_name):
session = Session()
new_u = Associations()
user = session.query(User.id).filter(User.name == uname).scalar()
room = session.query(Room.id).filter(Room.name == room_name).scalar()
perm = session.query(Perm.id).filter(Perm.name == perm_name).scalar()
new_u.user_id = user
new_u.perm_id = perm
new_u.room_id = room
session.add(new_u)
session.commit()
room_obj = session.query(Room).get(room)
room_users = []
the_room = {}
for r_user in room_obj.user:
room_users.append(session.query(User.name).filter(User.id == r_user.user_id).one()[0])
for r_user in room_obj.user:
if r_user.user_id == user:
the_room = dict(
room_name=room_obj.name,
perm=session.query(
Perm.id,
Perm.name,
Perm.add_user,
Perm.create_room,
Perm.create_vote,
Perm.delete_room,
Perm.delete_user,
Perm.delete_vote,
Perm.make_secure,
Perm.make_unsecure,
Perm.voting,
Perm.edit_perm,
Perm.edit_perm_def,
)
.filter(Perm.id == r_user.perm_id)
.one()
.__dict__,
users=room_users,
secure=room_obj.secure,
auth=room_obj.auth,
)
add_user_obj = {"user_name": uname, "room": the_room}
session.close()
return add_user_obj
def deletarPessoa(nome_):
session = DBSession()
p = getPessoa(nome_)
if p != None:
telefones = session.query(Telefone).filter_by(pessoa_id=p.id)
for x in telefones:
session.delete(x)
enderecos = session.query(Endereco).filter_by(pessoa_id=p.id)
for x in enderecos:
session.delete(x)
session.delete(p)
session.commit()
else:
print("Contato não encontrado.\n")
session.close()
def main():
engine = sqlalchemy.create_engine(MYSQL_URL, encoding='UTF8', echo=True)
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 创建sesstion类型
session = sqlalchemy.orm.session.Session()
#user = User(uid='python', name='小李老师', roles=roles)
user = session.query(User).get("yootk")
print(user)
for role in user.roles:
print("\t%s" % role)
#session.add(user)
session.commit()
session.close()
def create_post(title: str, content: str, author: str):
post = Post()
post.title = title
post.content = content
post.author = author
post.pub_status = 'private'
session = db_session.create_session()
try:
session.add(post)
session.commit()
finally:
session.close()
return post.id
def wrapper(self, *args, **kwargs):
for arg in args:
if isinstance(arg, sqlalchemy.orm.session.Session):
kwargs['session'] = arg
args = list(args)
args.remove(arg)
args = tuple(args)
break
if ('session' in kwargs) and (kwargs['session'] != None):
return method(self, *args, **kwargs)
else:
with new_session() as session:
kwargs['session'] = session
result = method(self, *args, **kwargs)
session.commit()
return result
def wrapper(self, *args, **kwargs):
for arg in args:
if isinstance(arg, sqlalchemy.orm.session.Session):
kwargs['session'] = arg
args = list(args)
args.remove(arg)
args = tuple(args)
break
if ('session' in kwargs) and (kwargs['session'] != None):
return method(self, *args, **kwargs)
else:
with new_session() as session:
kwargs['session'] = session
result = method(self, *args, **kwargs)
session.commit()
return result
def main():
engine = sqlalchemy.create_engine(MYSQL_URL, encoding='UTF8', echo=True)
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) #创建sesstion类型
session = sqlalchemy.orm.session.Session()
bir_date = datetime.datetime.strptime("2015-11-6", "%Y-%m-%d")
user = User(name='小李',
age=15,
birthday=bir_date,
salary=2400,
note='aaaaaa')
#user.name=''xiao白 可以更改数据
session.add(user) #sql处理(orm）转换
session.commit()
print('数据保存成功,当前ID为%s' % user.uid)
session.close()
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
roles = session.query(Role).filter(Role.rid.in_(["admin",
"audit"])).all()
user = User(uid="python", name="小李老師", roles=roles)
session.add(user)
session.commit()
session.close() # 持久態轉為游離態
except Exception as e:
logging.info(traceback.format_exc())
def create_instance(
schema: BaseSchema,
data: typing.Dict,
session: sqlalchemy.orm.session.Session = None,
commit: bool = True,
) -> flask_sqlalchemy.Model:
"""Validata and create model instance."""
schema.validate(data, session=session) # base schema
result = schema.load(data, session=session) # custom schema (with params)
session = session or schema.sql_session # check with line
if commit:
"""Save deserialize object-data on db."""
session.add(result.data)
session.commit()
return result.data
def addMenuItem(
name=None, id=None, course=None, description=None, price=None, restaurant_id=None
):
"""
Adds a new Menu Item to the Database,
each item is associated with a restaurant.
"""
temp = MenuItem(
name=name,
description=description,
course=course,
price=price,
restaurant_id=restaurant_id,
)
session.add(temp)
session.commit()
def main():
engine = sqlalchemy.create_engine(MYSQL_URL, encoding='UTF8', echo=True)
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 创建sesstion类型
session = sqlalchemy.orm.session.Session()
bir_date = datetime.datetime.strptime("2015-10-6", "%Y-%m-%d")
user = User(uid=2,
name='小白',
age=999,
birthday=bir_date,
salary=1.0,
note='aaaaaa')
session.merge(user)
session.commit()
print(user)
session.close()
def pelicula_update(pelicula):
print(pelicula.id)
currentPelicula = session.query(Pelicula).filter(Pelicula.id == pelicula.id).first()
print(currentPelicula.genero)
currentPelicula.formatos = pelicula.formatos
currentPelicula.genero = pelicula.genero
currentPelicula.fechaEstreno = pelicula.fechaEstreno
currentPelicula.duracion = pelicula.duracion
currentPelicula.sinopsis = pelicula.sinopsis
currentPelicula.clasificacion = pelicula.clasificacion
currentPelicula.pais = pelicula.pais
currentPelicula.tituloOriginal = pelicula.tituloOriginal
currentPelicula.tituloPais = pelicula.tituloPais
print("pelicula imagen",pelicula.imagen)
currentPelicula.imagen = None if pelicula.imagen == None else pelicula.imagen
session.add(currentPelicula)
session.commit()
session.flush()
def fill_one_financialv2(ticker: str, year: int = 1900):
stock0: Stock = session.query(Stock).filter_by(
stock_ticker=ticker).one_or_none()
print('current financials:', stock0)
pe = randrange(1, 100)
pb = randrange(1, 100)
try:
fin = Financials(price_book=pb,
price_earning=pe,
year=year,
refer_stock_ticker=stock0.stock_ticker)
except sqlite3.IntegrityError:
session.rollback()
year += 1
raise
else:
session.add(fin)
session.commit()
print('financials after', stock0.financials)
session.close()
def __init__(self, url):
first_page = 1
# page=0 и page=1 на сайте дублируются, поэтому начинаем с 1.
last_page = self.get_data(url, 1).get('icos').get('last_page')
for i in range(first_page, last_page + 1):
time.sleep(1)
ico_data = self.get_data(url, i)
for item in ico_data.get('icos').get('data'):
self.icosdata.append(DbProduct(**item))
for key, value in ico_data.items():
setattr(self, key, value)
session = db_session()
session.add_all(self.icosdata)
session.commit()
session.close()
def save_new_student(self):
try:
name = str(self.lineEdit_fname.text() + '_' + self.lineEdit_sname.text())
group = int(self.lineEdit_group.text())
username = str(self.lineEdit_username.text())
password = str(self.lineEdit_pass.text())
new_post = Users(name=name, group=group, nick=username, psw=password)
session.add(new_post)
session.commit()
# cursor.execute("INSERT INTO users(username, name, password, group) VALUES (%s, %s, %s, %s)", (username, name, password, group))
# conn.commit()
# QMessageBox.information(win,"Success!", 'Account created successfully.')
self.clear1()
except:
QMessageBox.critical(win, "Error", 'Make sure no filed is empty.')
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
bir_date = datetime.datetime.strptime("2016-11-30",
"%Y-%m-%d") # 字符串轉為日期
user = User(name="沐言優拓-小李",
age=16,
birthday=bir_date,
salary=2400.00,
note="www.example.com")
session.add(user) # 對象操作= SQL 處理 (ORM 引擎轉換)
session.commit() # 事務提交
logging.info(f"數據保存成功，當前ID為:{user.uid}") # uid可以自動回填
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def insertJOB(line=jobLine2, bin="1027193"):
a = line.split('|')
fds = a[2]
bs = a[11]
cs = a[21]
if fds == '':
datea = None
else:
montha = fds[0:2]
daya = fds[2:4]
yeara = fds[4:]
datea = parser.parse(montha + ' ' + daya + ' ' + yeara, dayfirst=False, yearfirst=False)
if bs == '':
dateb = None
else:
monthb = bs[0:2]
dayb = bs[2:4]
yearb = bs[4:]
dateb = parser.parse(monthb + ' ' + dayb + ' ' + yearb, dayfirst=False, yearfirst=False)
if cs == '':
datec = None
else:
monthc = cs[0:2]
dayc = cs[2:4]
yearc = cs[4:]
datec = parser.parse(monthc + ' ' + dayc + ' ' + yearc, dayfirst=False, yearfirst=False)
print a[0] + " " + a[1] + " date " + a[2] + " " + str(bin)
insJOB = Bisapp_Jobs(Pra3Isn=a[1], Fd=datea, Job=a[3], Ap=a[4], JobType=a[5],
Demo=a[6], FlrInjq=a[7], Gas=a[8], StatusCode=a[9], Dt=dateb,
Applicant=a[12], Rep=a[13], Jobdes=a[14], JAppLicNumberDisp=a[15],
JAuditCodeFlag=a[16], DiagramFlag=a[17], ZoningDiagramStatus=a[18],
ZoningDiagramRecDate=a[19], DocType=a[20], FoundationAppDate=datec, bin=bin)
session.merge(insJOB)
session.commit()
return
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
dept_list = [Dept(dname="軟件部"),
Dept(dname="信息部"),
Dept(dname="客服部")] # 部門列表
company = Company(cid="C-002",
cname="沐言優拓",
site="www.school.net",
depts=dept_list) # 構建Company對象
session = sqlalchemy.orm.session.Session() # 實例化Session對象
session.add(company) # 公司數據的保存
session.commit() # 持久態
for dept in dept_list:
logging.info(f"[新增部冊編號]did = {dept.did}")
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
bir_date = datetime.datetime.strptime("2000-01-01",
"%Y-%m-%d") # 字符串轉為日期
user = User(uid=3,
name="李沐言-吉祥物",
age=999,
birthday=bir_date,
salary=0.0,
note="www.example.com")
session.merge(user) # 對象操作= SQL 處理 (ORM 引擎轉換)
session.commit() # 事務提交
logging.info(f"數據修變成功，當前user為:{user}")
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def __init__(self, url):
while True:
if self.next:
data = self.get_next_data(self.next)
else:
data = self.get_next_data(url)
for item in data.get('results'):
self.icos.append(DbIco(**item))
for key, value in data.items():
setattr(self, key, value)
if not data['next']:
break
session = db_session()
session.add_all(self.icos)
session.commit()
session.close()
def compra(funcionId, tickets, email, nombre):
print(tickets)
query = """INSERT INTO `car_db`.`compra`
(`fecha_creacion`,`fecha_modificacion`,`email`,`id_funcion`,`nombre`)
VALUES(CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'{email}',{funcionId},'{nombre}')
""".format(email=email, funcionId=funcionId, nombre=nombre)
session.execute(query)
compra = session.query(Compra).order_by(
Compra.fecha_creacion.desc()).filter(~Compra.tickets.any()).filter(
Compra.email == email).filter(
Compra.id_funcion == funcionId).first()
for t in tickets:
session.execute("""INSERT INTO `car_db`.`compra_ticket`
(`id_compra`,`id_ticket`)
VALUES({idCompra},{idTicket})
""".format(idCompra=compra.id, idTicket=t.id))
session.commit()
session.flush()
return compra.id
session.expire(compra)
session.refresh(compra)
session.expire_all()
def main():
mode = 0
while mode != "3":
session = connect_db()
mode = input("""Выбери режим:
1 - Зарегистрировать нового пользователя;
2 - Найти зарегистрированного пользователя;
3 - Закончить регистрацию.\n""")
if mode == "1":
user = request_data(session)
session.add(user)
session.commit()
print("\nПользователь %s зарегистрирован\n" % user.first_name)
elif mode == "2":
name = input("\nВведи имя пользователя с заглавной буквы: ")
user_list = find(name, session)
users_print(user_list, name)
elif mode == "3":
print("\nБлагодарю за сотрудничество!")
StopIteration
else:
print("\nНекорректный режим!\n")
def set_friendship():
data = request.get_json()
user_id0 = None
try:
user_id0 = data['user_id']
except:
print("Received data with invalid format!")
user_id1 = None
try:
user_id1 = request.headers['Account-Id']
except:
print(
"User did not specify an Account Id when performing the request!")
print(user_id0)
print(user_id1)
if user_id0 is not None and user_id1 is not None:
status_left = session.query(DBFriend.status).filter_by(
user_id1=user_id0, user_id2=user_id1).all()
status_right = session.query(DBFriend.status).filter_by(
user_id1=user_id1, user_id2=user_id0).all()
status = []
status.extend(status_left)
status.extend(status_right)
if status == []:
friendship = DBFriend(user_id1=user_id0,
user_id2=user_id1,
status=1)
session.add(friendship)
else:
session.query(DBFriend).filter(
(DBFriend.user_id1 == user_id0
and DBFriend.user_id2 == user_id1)
or (DBFriend.user_id1 == user_id1
and DBFriend.user_id2 == user_id0)).update({'status': 1})
session.commit()
return make_response('200')
def insertJobExt(line=JobExtLine2, Pra3Isn="0002339478"):
a = line.split('|')
print a[0]
print a[1]
print a[2]
print a[3]
print a[4]
print a[5]
print a[6]
print a[7]
print "length statusdef " + str(len(a[8]))
print a[8]
print a[17]
insJobExt = Bisapp_JobExt(Pra3Isn=Pra3Isn, JPaaDocNo=a[1], JJobDescrip1=a[2], JJobDescrip2=a[3], JJobDescrip3=a[4],
JJobDescrip4=a[5], JJobDocumentNumber=a[6], JJobNumber=a[7], JJobStatusDef=a[8],
JJobStatusDate=a[9], JJobType=a[10], JOtDescription=a[11], JSpecialActionStatus=a[12],
JPreFilingDate=a[13], JDirective14=a[14], JAppLastName=a[15], JAppFirstName=a[16],
JPlansPageCount=a[17])
session.merge(insJobExt)
session.commit()
return
def insert_roles():
role = {
'User': [Permission.FOLLOW, Permission.COMMENT, Permission.WRITE],
'Moderator': [
Permission.FOLLOW, Permission.COMMENT, Permission.WRITE,
Permission.MODERATE
],
'Administrator': [
Permission.FOLLOW, Permission.COMMENT, Permission.WRITE,
Permission.MODERATE, Permission.ADMIN
],
}
default_role = 'User'
for r in role:
role = Role.query.filter_by(name=r).first()
if role is None:
role = Role(name=r)
role.reset_permissions()
for perm in role[r]:
role.add_permission(perm)
role.default = (role.rolename == default_role)
session.add(role)
session.commit()
def post(self, request):
dict = json.loads(request.body.decode())
cid = dict.get('cid')
score = dict.get('score')
#参数校验
if not all([
re.match(r'^\d+$', cid),
re.match(r'[1-9]\d{0,6}$|10000000', score)
]):
return http.HttpResponseForbidden("客户端编号和分数为数字,分数范围为1...10000000")
score = int(score)
#查询当前客户端有没有该用户
try:
rank = session.query(user_ranking).filter(
user_ranking.client_code == cid).first()
#没有就创建,有就修改
if not rank:
rank = user_ranking(client_code=cid, score=score)
session.add(rank)
session.commit()
else:
rank.score = score
session.commit()
except exc.DatabaseError as e:
logger.error(e)
return http.JsonResponse({
'code': RETCODE.DBERR,
'errmsg': '数据库操失败'
})
return JsonResponse({'code': RETCODE.OK, 'errmsg': 'ok'})
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
# result = session.execute(
# User.__table__.insert(), # 內部生成的SQL語句
# [{"name": "[YA]沐言優拓 -%s" % random.randint(1, 999),
# "note": "www.example.com"} for item in range(100000)]
# ) # 執行SQL處理
result = session.execute(
User.__table__.insert(), # 內部生成的SQL語句
[{
"name": f"[YA]沐言優拓 -{random.randint(1, 999)}",
"note": "www.example.com"
} for item in range(100000)]) # 執行SQL處理
session.commit() # 提交更新
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def create_DB():
data = pd.read_csv('IMDB_Movie_index.csv')
engine = create_engine('sqlite:///movies.db', echo=True)
Movie.drop(engine)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
# movies = Table('movies', meta,
# Column('id', Integer, primary_key=True),
# Column('movie_name', String, unique=True),
# Column('movie_link', String, unique=True),
# Column('poster_link', String, unique=True))
# engine = create_engine('sqlite:///movies.db', echo=True)
# meta.create_all(engine)
#engine.execute(tbl.delete())
# # movie = Movies()
#
# conn = engine.connect()
# stmt = insert(movies)
# dict = data.to_dict('records')
# result = conn.execute(stmt, dict)
for index, row in data.iterrows():
c = Movie(id=index,
name=row['movie_name'],
link=row['link_to_detail'],
poster_link=row['poster_link'])
session.add(c)
session.commit()
# result = conn.execute(stmt)
for row in result:
print(row)
session.close()
password = Column(String(70))
def __repr__(self):
return "<User(name='%s', fullname='%s', password='******')>" % (
self.name, self.fullname, self.password)
# In order to connect to the database use 'mysql+pymysql'
Session = sessionmaker()
engine = create_engine('mysql+pymysql://root:[email protected]/wtg_bis')
Session.configure(bind=engine)
session = Session()
# metadata = MetaData()
#Base.metadata.drop_all(engine, checkfirst=False)
Base.metadata.create_all(engine)
print "starting"
ed_user = User( name='ed', fullname='Ed Jones', password='******')
print ed_user.name
print ed_user.password
print str(ed_user.id)
session.add(ed_user)
session.add_all([
User(name='wendy', fullname='Wendy Williams', password='******'),
User(name='mary', fullname='Mary Contrary', password='******'),
User(name='fred', fullname='Fred Flinstone', password='******')])
session.commit()