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 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 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 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 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 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 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類型
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 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 insertECB(line = ecbLine, bin = "1076262"):
a = line.split('|')
count = 0
for example in a:
count = count+1
ins1 = Bisapp_Ecb(Bin_id = 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
insertECB()
'''
line = ecbLine
a = line.split('|')
bin = "1076262"
ins1 = Bisapp_Ecb(Bin_id = 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()
'''
