annotate SMART/Java/Python/mySql/MySqlConnection.py @ 18:94ab73e8a190

Uploaded
author m-zytnicki
date Mon, 29 Apr 2013 03:20:15 -0400
parents 769e306b7933
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
1 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
2 # Copyright INRA-URGI 2009-2010
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
3 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
4 # This software is governed by the CeCILL license under French law and
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
5 # abiding by the rules of distribution of free software. You can use,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
6 # modify and/ or redistribute the software under the terms of the CeCILL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
7 # license as circulated by CEA, CNRS and INRIA at the following URL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
8 # "http://www.cecill.info".
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
9 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
10 # As a counterpart to the access to the source code and rights to copy,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
11 # modify and redistribute granted by the license, users are provided only
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
12 # with a limited warranty and the software's author, the holder of the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
13 # economic rights, and the successive licensors have only limited
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
14 # liability.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
15 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
16 # In this respect, the user's attention is drawn to the risks associated
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
17 # with loading, using, modifying and/or developing or reproducing the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
18 # software by the user in light of its specific status of free software,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
19 # that may mean that it is complicated to manipulate, and that also
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
20 # therefore means that it is reserved for developers and experienced
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
21 # professionals having in-depth computer knowledge. Users are therefore
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
22 # encouraged to load and test the software's suitability as regards their
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
23 # requirements in conditions enabling the security of their systems and/or
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
24 # data to be ensured and, more generally, to use and operate it in the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
25 # same conditions as regards security.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
26 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
27 # The fact that you are presently reading this means that you have had
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
28 # knowledge of the CeCILL license and that you accept its terms.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
29 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
30 #! /usr/bin/env python
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
31 import os
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
32 import random
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
33 import sqlite3
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
34 from SMART.Java.Python.mySql.MySqlQuery import MySqlQuery
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
35
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
36
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
37 class MySqlConnection(object):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
38 """Connection to a database"""
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
39
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
40 def __init__(self, verbosity = 0):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
41 self.verbosity = verbosity
18
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
42 self.databaseName = os.path.join(os.environ.get("SMARTTMPPATH", "."), "smartdb%d" % random.randint(0, 100000))
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
43 self.connection = sqlite3.connect(self.databaseName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
44 self.executeQuery("PRAGMA journal_mode = OFF")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
45 self.executeQuery("PRAGMA synchronous = 0")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
46 self.executeQuery("PRAGMA locking_mode = EXCLUSIVE")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
47 self.executeQuery("PRAGMA count_change = OFF")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
48 self.executeQuery("PRAGMA temp_store = 2")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
49
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
50 def __del__(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
51 self.connection.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
52
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
53
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
54 def createDatabase(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
55 pass
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
56
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
57
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
58 def deleteDatabase(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
59 if os.path.exists(self.databaseName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
60 os.remove(self.databaseName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
61
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
62
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
63 def executeQuery(self, command, insertion = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
64 cursor = self.connection.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
65 query = MySqlQuery(cursor, self.verbosity)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
66 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
67 result = query.execute(command, insertion)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
68 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
69 except:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
70 result = query.execute(command, insertion)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
71 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
72 if insertion:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
73 return result
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
74 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
75 return query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
76
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
77
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
78 def executeManyQueries(self, commands):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
79 cursor = self.connection.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
80 query = MySqlQuery(cursor, self.verbosity)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
81 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
82 for cpt, command in enumerate(commands):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
83 query.execute(command)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
84 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
85 except:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
86 for cpt, command in enumerate(commands):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
87 query.execute(command)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
88 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
89
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
90
18
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
91 def executeManyFormattedQueries(self, command, lines, insertion = False):
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
92 cursor = self.connection.cursor()
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
93 query = MySqlQuery(cursor, self.verbosity)
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
94 for line in lines:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
95 result = query.executeFormat(command, line)
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
96 self.connection.commit()
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
97 if insertion:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
98 return result
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
99 else:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
100 return query
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
101
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
102
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
103 def executeManyQueriesIterator(self, table):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
104 cursor = self.connection.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
105 query = MySqlQuery(cursor, self.verbosity)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
106 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
107 for command in table.getIterator():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
108 query.execute(command)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
109 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
110 except:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
111 for command in table.getIterator():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
112 query.execute(command)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
113 self.connection.commit()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
114
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
115
18
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
116 def executeManyFormattedQueriesIterator(self, table):
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
117 cursor = self.connection.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
118 query = MySqlQuery(cursor, self.verbosity)
18
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
119 try:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
120 for command, values in table.getIterator():
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
121 query.executeFormat(command, values)
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
122 self.connection.commit()
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
123 except:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
124 for command, values in table.getIterator():
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
125 query.execute(command, values)
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
126 self.connection.commit()
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
127
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
128
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
129 def executeFormattedQuery(self, command, parameters, insertion = False):
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
130 cursor = self.connection.cursor()
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
131 query = MySqlQuery(cursor, self.verbosity)
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
132 result = query.executeFormat(command, parameters)
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
133 self.connection.commit()
18
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
134 if insertion:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
135 return result
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
136 else:
94ab73e8a190 Uploaded
m-zytnicki
parents: 6
diff changeset
137 return query