My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions

Issue 374 attachment: test_pyodbc_connection.py (4.7 KB)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
import pyodbc
import sys
import platform
#install localy for local testing outsite BN
#http://code.google.com/p/pyodbc/downloads/list

"""
oher used querys (works directly in oracle)
update BN_SEQUENCE.ENTRYTABLE set LEVELID = 0, ENDTMODIF = '2014-06-25 15:49:03', OBJACTIONID = -1, OBJLCK = 0, OBJOWNER = '_DefaultUser_', OBJSHARED = 1, USI = NULL, GENE = '16S rRNA', STRAIN = 'JCM9731', TYPE = NULL, ALIAS = NULL, GENUS = 'Bacillus', SPECIES = '', COL_TYPE = NULL, DEPOSITOR = 'EMBL IMPORT', RESPONSIBLE = 'import_from_EMBL_py', PROJECT = NULL, REMARKS = NULL, ACCESSION = 'AB021182', PERSONAL_ID = NULL, PRIMERS = NULL, QUALITY = NULL, PRIMERS_CUT = 'no', IN_FRAME = 'NA', BATCH = NULL, LENGTH_SEQ = '1504', STATUS_QC = NULL where KEY = 'BN_SEQUENCE_OLIFANT_UGentbaverhey_A_0000014';





insert into BN_SEQUENCE.ENTRYTABLE ( KEY, LEVELID, ENDTCREATED, ENDTMODIF, OBJACTIONID, OBJLCK, OBJOWNER, OBJSHARED, USI, GENE, STRAIN, TYPE, ALIAS, GENUS, SPECIES, COL_TYPE, DEPOSITOR, RESPONSIBLE, PROJECT, REMARKS, ACCESSION, PERSONAL_ID, PRIMERS, QUALITY, PRIMERS_CUT, IN_FRAME, BATCH, LENGTH_SEQ, STATUS_QC)
values ('BN_SEQUENCE_OLIFANT_UGentbaverheytestt_A_01234', 0, '2014-06-25 09:39:37', '2014-06-25 09:39:37', -1, 0, '_DefaultUser_', 1, NULL, '16S rRNA', 'JCM9731', NULL, NULL, 'Bacillus', '', NULL, 'EMBL-IMPORT', 'import_from_EMBL_py', NULL, NULL, 'AB021182', NULL, NULL, NULL, 'no', 'NA', NULL, '1504', NULL);

"""

print( platform.machine())
print( platform.version())
print( platform.platform())

print( platform.uname())
print( platform.system())
print( platform.processor())
print (sys.version)



#insert query works when in 1 line string
#update query works not when in 1 line string or multi line but works on oracle server direcly

for i in range(1,1000):
try:
cnxn = pyodbc.connect('DSN=MALTHUS;UID=xx;PWD=xx')
cursor = cnxn.cursor()




#key_nr = "{0:0 >7}".format(i)
sql_upsert = "insert into BN_SEQUENCE.ENTRYTABLE ( KEY, LEVELID, ENDTCREATED, ENDTMODIF, OBJACTIONID, OBJLCK, OBJOWNER, OBJSHARED, USI, GENE, STRAIN, TYPE, ALIAS, GENUS, SPECIES, COL_TYPE, DEPOSITOR, RESPONSIBLE, PROJECT, REMARKS, ACCESSION, PERSONAL_ID, PRIMERS, QUALITY, PRIMERS_CUT, IN_FRAME, BATCH, LENGTH_SEQ, STATUS_QC) values ('BN_SEQUENCE_OLIFANT_UGentbaverheytestt_A_01234', 0, '2014-06-25 09:39:37', '2014-06-25 09:39:37', -1, 0, '_DefaultUser_', 1, NULL, '16S rRNA', 'JCM9731', NULL, NULL, 'Bacillus', '', NULL, 'EMBL-IMPORT', 'import_from_EMBL_py', NULL, NULL, 'AB021182', NULL, NULL, NULL, 'no', 'NA', NULL, '1504', NULL)"
#sql_upsert = "update BN_SEQUENCE.ENTRYTABLE set LEVELID = 0, ENDTMODIF = '2014-06-25 15:49:03', OBJACTIONID = -1, OBJLCK = 0, OBJOWNER = '_DefaultUser_', OBJSHARED = 1, USI = NULL, GENE = '16S rRNA', STRAIN = 'JCM9731', TYPE = NULL, ALIAS = NULL, GENUS = 'Bacillus', SPECIES = '', COL_TYPE = NULL, DEPOSITOR = 'EMBL IMPORT', RESPONSIBLE = 'import_from_EMBL_py', PROJECT = NULL, REMARKS = NULL, ACCESSION = 'AB021182', PERSONAL_ID = NULL, PRIMERS = NULL, QUALITY = NULL, PRIMERS_CUT = 'no', IN_FRAME = 'NA', BATCH = NULL, LENGTH_SEQ = '1504', STATUS_QC = NULL where KEY = 'BN_SEQUENCE_OLIFANT_UGentbaverhey_A_0000014';"

# sql_upsert = (
# "UPDATE BN_SEQUENCE.ENTRYTABLE"
# " SET LEVELID = 0, ENDTMODIF = '2014-06-25 15:49:03',"
# " OBJACTIONID = -1, OBJLCK = 0,"
# " OBJOWNER = '_DefaultUser_', OBJSHARED = 1,"
# " USI = NULL, GENE = '16S rRNA',"
# " STRAIN = 'JCM9731', TYPE = NULL,"
# " ALIAS = NULL, GENUS = 'Bacillus',"
# " SPECIES = '', COL_TYPE = NULL,"
# " DEPOSITOR = 'EMBL IMPORT',"
# " RESPONSIBLE = 'import_from_EMBL_py',"
# " PROJECT = NULL, REMARKS = NULL,"
# " ACCESSION = 'AB021182', PERSONAL_ID = NULL,"
# " PRIMERS = NULL, QUALITY = NULL,"
# " PRIMERS_CUT = 'no', IN_FRAME = 'NA',"
# " BATCH = NULL, LENGTH_SEQ = '1504',"
# " STATUS_QC = NULL"
# " where KEY = 'BN_SEQUENCE_OLIFANT_UGentbaverhey_A_0000014';"
# )
#sql_upsert = sql_upsert.format(ii=key_nr)
#execute querey and commit
cursor.execute(sql_upsert)
cnxn.commit()
cnxn.close()
print ("\ndone row: "+str(i) )
except :
print ("Error for :" + str(i))
print (sys.exc_info()[0])

Powered by Google Project Hosting