forked from bgschiller/postgres_kernel
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathkernel.py
More file actions
226 lines (195 loc) · 7.73 KB
/
Copy pathkernel.py
File metadata and controls
226 lines (195 loc) · 7.73 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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
from ipykernel.kernelbase import Kernel
import psycopg2
from psycopg2 import Error, ProgrammingError, OperationalError
from psycopg2.extensions import (
QueryCanceledError, POLL_OK, POLL_READ, POLL_WRITE, STATUS_BEGIN,
)
import re
import os
from select import select
from .version import __version__
from tabulate import tabulate
version_pat = re.compile(r'^PostgreSQL (\d+(\.\d+)+)')
def log(val):
return # comment out line for debug
with open('kernel.log', 'a') as f:
f.write(str(val) + '\n')
return val
def wait_select_inter(conn):
while 1:
try:
state = conn.poll()
if state == POLL_OK:
break
elif state == POLL_READ:
select([conn.fileno()], [], [])
elif state == POLL_WRITE:
select([], [conn.fileno()], [])
else:
raise conn.OperationalError(
"bad state from poll: %s" % state)
except KeyboardInterrupt:
conn.cancel()
# the loop will be broken by a server error
continue
class PostgresKernel(Kernel):
implementation = 'postgres_kernel'
implementation_version = __version__
language_info = {'name': 'PostgreSQL',
'codemirror_mode': 'sql',
'mimetype': 'text/x-postgresql',
'file_extension': '.sql'}
def __init__(self, **kwargs):
Kernel.__init__(self, **kwargs)
log('inside init')
# Catch KeyboardInterrupt, cancel query, raise QueryCancelledError
psycopg2.extensions.set_wait_callback(wait_select_inter)
self._conn_string = os.getenv('DATABASE_URL', '')
self._autocommit = True
self._conn = None
self._start_connection()
@property
def language_version(self):
m = version_pat.search(self.banner)
return m.group(1)
_banner = None
@property
def banner(self):
if self._banner is None:
if self._conn is None:
return 'not yet connected to a database'
self._banner = self.fetchone('SELECT VERSION();')[0]
return self._banner
def _start_connection(self):
log('starting connection')
try:
self._conn = psycopg2.connect(self._conn_string)
self._conn.autocommit = self._autocommit
except OperationalError:
log('failed to connect to {}'.format(self._conn_string))
message = '''Failed to connect to a database at {}'''.format(self._conn_string)
self.send_response(self.iopub_socket, 'stream',
{'name': 'stderr', 'text': message})
def fetchone(self, query):
log('fetching one from: \n' + query)
with self._conn.cursor() as c:
c.execute(query)
one = c.fetchone()
log(one)
return one
def fetchall(self, query):
log('fetching all from: \n' + query)
with self._conn.cursor() as c:
c.execute(query)
desc = c.description
if c.description:
keys = [col[0] for col in c.description]
return keys, c.fetchall()
return None, None
CONN_STRING_COMMENT = re.compile(r'--\s*connection:\s*(.*)\s*$')
AUTOCOMMIT_SWITCH_COMMENT = re.compile(r'--\s*autocommit:\s*(\w+)\s*$')
def change_connection(self, conn_string):
self._conn_string = conn_string
self._start_connection()
def switch_autocommit(self, switch_to):
self._autocommit = switch_to
committed = False
if self._conn:
if self._conn.get_transaction_status() == STATUS_BEGIN:
committed = True
self._conn.commit()
self._conn.autocommit = switch_to
else:
self._start_connection()
return committed
def change_autocommit_mode(self, switch):
"""
Strip and make a string case insensitive and ensure it is either 'true' or 'false'.
If neither, prompt user for either value.
When 'true', return True, and when 'false' return False.
"""
parsed_switch = switch.strip().lower()
if not parsed_switch in ['true', 'false']:
self.send_response(
self.iopub_socket, 'stream', {
'name': 'stderr',
'text': 'autocommit must be true or false.\n\n'
}
)
switch_bool = (parsed_switch == 'true')
committed = self.switch_autocommit(switch_bool)
message = (
'committed current transaction & ' if committed else '' +
'switched autocommit mode to ' +
str(self._autocommit)
)
self.send_response(
self.iopub_socket, 'stream', {
'name': 'stderr',
'text': message,
}
)
def do_execute(self, code, silent, store_history=True,
user_expressions=None, allow_stdin=False):
print(code)
connection_string = self.CONN_STRING_COMMENT.findall(code)
autocommit_switch = self.AUTOCOMMIT_SWITCH_COMMENT.findall(code)
if autocommit_switch:
self.change_autocommit_mode(autocommit_switch[0])
if connection_string:
self.change_connection(connection_string[0])
code = self.AUTOCOMMIT_SWITCH_COMMENT.sub('', self.CONN_STRING_COMMENT.sub('', code))
if not code.strip():
return {'status': 'ok', 'execution_count': self.execution_count,
'payload': [], 'user_expressions': {}}
if self._conn is None:
self.send_response(
self.iopub_socket, 'stream', {
'name': 'stderr',
'text': '''\
Error: Unable to connect to a database at "{}".
Perhaps you need to set a connection string with
-- connection: <connection string here>'''.format(self._conn_string)
})
return {'status': 'error', 'execution_count': self.execution_count,
'ename': 'MissingConnection'}
try:
header, rows = self.fetchall(code)
except QueryCanceledError:
self._conn.rollback()
return {'status': 'abort', 'execution_count': self.execution_count}
except Error as e:
self.send_response(self.iopub_socket, 'stream',
{'name': 'stderr', 'text': str(e)})
self._conn.rollback()
return {'status': 'error', 'execution_count': self.execution_count,
'ename': 'ProgrammingError', 'evalue': str(e),
'traceback': []}
else:
if rows is not None:
self.send_response(
self.iopub_socket, 'stream', {
'name': 'stdout',
'text': str(len(rows)) + " row(s) returned.\n"
})
for notice in self._conn.notices:
self.send_response(
self.iopub_socket, 'stream', {
'name': 'stdout',
'text': str(notice)
})
self._conn.notices = []
if header is not None and len(rows) > 0:
self.send_response(self.iopub_socket, 'display_data', display_data(header, rows))
return {'status': 'ok', 'execution_count': self.execution_count,
'payload': [], 'user_expressions': {}}
def display_data(header, rows):
d = {
'data': {
'text/latex': tabulate(rows, header, tablefmt='latex_booktabs'),
'text/plain': tabulate(rows, header, tablefmt='simple'),
'text/html': tabulate(rows, header, tablefmt='html'),
},
'metadata': {}
}
return d