1
2
3 """
4 Base classes for enhanced DB drivers.
5 """
6 from __future__ import absolute_import
7
8 __docformat__ = "restructuredtext en"
9
10
11
12
13
14 import re
15 import time
16 import os
17 import sys
18 from datetime import date, datetime
19 from collections import namedtuple
20
21 from grizzled.exception import ExceptionWithMessage
22 from grizzled.decorators import abstract
23
24
25
26
27
28 __all__ = ['DBDriver', 'DB', 'Cursor', 'DBError', 'Error', 'Warning',
29 'TableMetadata', 'IndexMetadata', 'RDBMSMetadata']
30
31
32
33
34
35
36
37
38
39 -class DBError(ExceptionWithMessage):
40 """
41 Base class for all DB exceptions.
42 """
43 pass
44
46 """Thrown to indicate an error in the ``db`` module."""
47 pass
48
50 """Thrown to indicate an error in the ``db`` module."""
51 pass
52
53 TableMetadata = namedtuple('TableMetadata', ['column_name',
54 'type_string',
55 'max_char_size',
56 'precision',
57 'scale',
58 'nullable'])
59
60 IndexMetadata = namedtuple('IndexMetadata', ['index_name',
61 'index_columns',
62 'description'])
63
64 RDBMSMetadata = namedtuple('RDBMSMetadata', ['vendor', 'product', 'version'])
67 """
68 Class for DB cursors returned by the ``DB.cursor()`` method. This class
69 conforms to the Python DB cursor interface, including the following
70 attributes.
71
72 :IVariables:
73 description : tuple
74 A read-only attribute that is a sequence of 7-item tuples, one per
75 column, from the last query executed. The tuple values are:
76 *(name, typecode, displaysize, internalsize, precision, scale)*
77 rowcount : int
78 A read-only attribute that specifies the number of rows
79 fetched in the last query, or -1 if unknown. *Note*: It's best
80 not to rely on the row count, because some database drivers
81 (such as SQLite) don't report valid row counts.
82 """
83
85 """
86 Create a new Cursor object, wrapping the underlying real DB API
87 cursor.
88
89 :Parameters:
90 cursor
91 the real DB API cursor object
92 driver
93 the driver that is creating this object
94 """
95 self.__cursor = cursor
96 self.__driver = driver
97 self.__description = None
98 self.__rowcount = -1
99
101 return self.__description
102
103 description = property(__get_description,
104 doc='The description field. See class docs.')
105
107 return self.__rowcount
108
109 rowcount = property(__get_rowcount,
110 doc='Number of rows from last query, or -1')
111
113 """
114 Close the cursor.
115
116 :raise Warning: Non-fatal warning
117 :raise Error: Error; unable to close
118 """
119 dbi = self.__driver.get_import()
120 try:
121 return self.__cursor.close()
122 except dbi.Warning, val:
123 raise Warning(val)
124 except dbi.Error, val:
125 raise Error(val)
126
127 - def execute(self, statement, parameters=None):
128 """
129 Execute a SQL statement string with the given parameters.
130 'parameters' is a sequence when the parameter style is
131 'format', 'numeric' or 'qmark', and a dictionary when the
132 style is 'pyformat' or 'named'. See ``DB.paramstyle()``.
133
134 :Parameters:
135 statement : str
136 the SQL statement to execute
137 parameters : list
138 parameters to use, if the statement is parameterized
139
140 :raise Warning: Non-fatal warning
141 :raise Error: Error
142 """
143 dbi = self.__driver.get_import()
144 try:
145 if parameters:
146 result = self.__cursor.execute(statement, parameters)
147 else:
148 result = self.__cursor.execute(statement)
149
150 try:
151 self.__rowcount = self.__cursor.rowcount
152 except AttributeError:
153 self.__rowcount = -1
154 self.__description = self.__cursor.description
155 return result
156 except dbi.Warning, val:
157 raise Warning(val)
158 except dbi.Error, val:
159 raise Error(val)
160 except:
161 raise Error(sys.exc_info()[1])
162
164 """
165 Execute a SQL statement once for each item in the given parameters.
166
167 :Parameters:
168 statement : str
169 the SQL statement to execute
170 parameters : sequence
171 a sequence of sequences when the parameter style
172 is 'format', 'numeric' or 'qmark', and a sequence
173 of dictionaries when the style is 'pyformat' or
174 'named'.
175
176 :raise Warning: Non-fatal warning
177 :raise Error: Error
178 """
179 dbi = self.__driver.get_import()
180 try:
181 result = self.__cursor.executemany(statement, *parameters)
182 self.__rowcount = self.__cursor.rowcount
183 self.__description = self.__cursor.description
184 return result
185 except dbi.Warning, val:
186 raise Warning(val)
187 except dbi.Error, val:
188 raise Error(val)
189
190 executeMany = executemany
191
193 """
194 Returns the next result set row from the last query, as a sequence
195 of tuples. Raises an exception if the last statement was not a query.
196
197 :rtype: tuple
198 :return: Next result set row
199
200 :raise Warning: Non-fatal warning
201 :raise Error: Error
202 """
203 dbi = self.__driver.get_import()
204 try:
205 return self.__cursor.fetchone()
206 except dbi.Warning, val:
207 raise Warning(val)
208 except dbi.Error, val:
209 raise Error(val)
210
212 """
213 Returns all remaining result rows from the last query, as a sequence
214 of tuples. Raises an exception if the last statement was not a query.
215
216 :rtype: list of tuples
217 :return: List of rows, each represented as a tuple
218
219 :raise Warning: Non-fatal warning
220 :raise Error: Error
221 """
222 dbi = self.__driver.get_import()
223 try:
224 return self.__cursor.fetchall()
225 except dbi.Warning, val:
226 raise Warning(val)
227 except dbi.Error, val:
228 raise Error(val)
229
230 fetchAll = fetchall
231
233 """
234 Returns up to n remaining result rows from the last query, as a
235 sequence of tuples. Raises an exception if the last statement was
236 not a query.
237
238 :Parameters:
239 n : int
240 maximum number of result rows to get
241
242 :rtype: list of tuples
243 :return: List of rows, each represented as a tuple
244
245 :raise Warning: Non-fatal warning
246 :raise Error: Error
247 """
248 dbi = self.__driver.get_import()
249 try:
250 self.__cursor.fetchmany(n)
251 except dbi.Warning, val:
252 raise Warning(val)
253 except dbi.Error, val:
254 raise Error(val)
255
256 fetchMany = fetchmany
257
285
329
363
365 """
366 Get the list of tables in the database to which this cursor is
367 connected.
368
369 :rtype: list
370 :return: List of table names. The list will be empty if the database
371 contains no tables.
372
373 :raise NotImplementedError: Capability not supported by database driver
374 :raise Warning: Non-fatal warning
375 :raise Error: Error
376 """
377 dbi = self.__driver.get_import()
378 try:
379 return self.__driver.get_tables(self.__cursor)
380 except dbi.Warning, val:
381 raise Warning(val)
382 except dbi.Error, val:
383 raise Error(val)
384
386 """
387 The object returned by a call to ``DBDriver.connect()``. ``db`` wraps the
388 real database object returned by the underlying Python DB API module's
389 ``connect()`` method.
390 """
392 """
393 Create a new DB object.
394
395 :Parameters:
396 db
397 the underlying Python DB API database object
398 driver : DBDriver
399 the driver (i.e., the subclass of ``DBDriver``) that
400 created the ``db`` object
401 """
402 self.__db = db
403 self.__driver = driver
404 dbi = driver.get_import()
405 for attr in ['BINARY', 'NUMBER', 'STRING', 'DATETIME', 'ROWID']:
406 try:
407 exec 'self.%s = dbi.%s' % (attr, attr)
408 except AttributeError:
409 exec 'self.%s = 0' % attr
410
412 """
413 Get the parameter style for the underlying DB API module. The
414 result of this method call corresponds exactly to the underlying
415 DB API module's 'paramstyle' attribute. It will have one of the
416 following values:
417
418 +----------+-----------------------------------------------------------+
419 | format | The parameter marker is '%s', as in string |
420 | | formatting. A query looks like this:: |
421 | | |
422 | | c.execute('SELECT * FROM Foo WHERE Bar=%s', [x]) |
423 +----------+-----------------------------------------------------------+
424 | named | The parameter marker is ``:name``, and parameters |
425 | | are named. A query looks like this:: |
426 | | |
427 | | c.execute('SELECT * FROM Foo WHERE Bar=:x', {'x':x}) |
428 +----------+-----------------------------------------------------------+
429 | numeric | The parameter marker is ``:n``, giving the parameter's |
430 | | number (starting at 1). A query looks like this:: |
431 | | |
432 | | c.execute('SELECT * FROM Foo WHERE Bar=:1', [x]) |
433 +----------+-----------------------------------------------------------+
434 | pyformat | The parameter marker is ``:name``, and parameters |
435 | | are named. A query looks like this:: |
436 | | |
437 | | c.execute('SELECT * FROM Foo WHERE Bar=%(x)s', {'x':x}) |
438 +----------+-----------------------------------------------------------+
439 | qmark | The parameter marker is "?", and parameters are |
440 | | substituted in order. A query looks like this:: |
441 | | |
442 | | c.execute('SELECT * FROM Foo WHERE Bar=?', [x]) |
443 +----------+-----------------------------------------------------------+
444 """
445 return self.__driver.get_import().paramstyle
446
448 """
449 Returns an object representing the given string of bytes as a BLOB.
450
451 This method is equivalent to the module-level ``Binary()`` method in
452 an underlying DB API-compliant module.
453
454 :Parameters:
455 string : str
456 the string to convert to a BLOB
457
458 :rtype: object
459 :return: the corresponding BLOB
460 """
461 return self.__driver.get_import().Binary(string)
462
463 - def Date(self, year, month, day):
464 """
465 Returns an object representing the specified date.
466
467 This method is equivalent to the module-level ``Date()`` method in
468 an underlying DB API-compliant module.
469
470 :Parameters:
471 year
472 the year
473 month
474 the month
475 day
476 the day of the month
477
478 :return: an object containing the date
479 """
480 return self.__driver.get_import().Date(year, month, day)
481
483 """
484 Returns an object representing the date *secs* seconds after the
485 epoch. For example:
486
487 .. python::
488
489 import time
490
491 d = db.DateFromTicks(time.time())
492
493 This method is equivalent to the module-level ``DateFromTicks()``
494 method in an underlying DB API-compliant module.
495
496 :Parameters:
497 secs : int
498 the seconds from the epoch
499
500 :return: an object containing the date
501 """
502 date = date.fromtimestamp(secs)
503 return self.__driver.get_import().Date(date.year, date.month, date.day)
504
505 - def Time(self, hour, minute, second):
506 """
507 Returns an object representing the specified time.
508
509 This method is equivalent to the module-level ``Time()`` method in an
510 underlying DB API-compliant module.
511
512 :Parameters:
513 hour
514 the hour of the day
515 minute
516 the minute within the hour. 0 <= *minute* <= 59
517 second
518 the second within the minute. 0 <= *second* <= 59
519
520 :return: an object containing the time
521 """
522 dt = datetime.fromtimestamp(secs)
523 return self.__driver.get_import().Time(dt.hour, dt.minute, dt.second)
524
526 """
527 Returns an object representing the time 'secs' seconds after the
528 epoch. For example:
529
530 .. python::
531
532 import time
533
534 d = db.TimeFromTicks(time.time())
535
536 This method is equivalent to the module-level ``TimeFromTicks()``
537 method in an underlying DB API-compliant module.
538
539 :Parameters:
540 secs : int
541 the seconds from the epoch
542
543 :return: an object containing the time
544 """
545 dt = datetime.fromtimestamp(secs)
546 return self.__driver.get_import().Time(dt.hour, dt.minute, dt.second)
547
548 - def Timestamp(self, year, month, day, hour, minute, second):
549 """
550 Returns an object representing the specified time.
551
552 This method is equivalent to the module-level ``Timestamp()`` method
553 in an underlying DB API-compliant module.
554
555 :Parameters:
556 year
557 the year
558 month
559 the month
560 day
561 the day of the month
562 hour
563 the hour of the day
564 minute
565 the minute within the hour. 0 <= *minute* <= 59
566 second
567 the second within the minute. 0 <= *second* <= 59
568
569 :return: an object containing the timestamp
570 """
571 return self.__driver.get_import().Timestamp(year, month, day,
572 hour, minute, second)
573
575 """
576 Returns an object representing the date and time ``secs`` seconds
577 after the epoch. For example:
578
579 .. python::
580
581 import time
582
583 d = db.TimestampFromTicks(time.time())
584
585 This method is equivalent to the module-level ``TimestampFromTicks()``
586 method in an underlying DB API-compliant module.
587
588 :Parameters:
589 secs : int
590 the seconds from the epoch
591
592 :return: an object containing the timestamp
593 """
594 dt = datetime.now()
595 return self.__driver.get_import().Timestamp(dt.year, dt.month, dt.day,
596 dt.hour, dt.minute, dt.second)
597
599 """
600 Get a cursor suitable for accessing the database. The returned object
601 conforms to the Python DB API cursor interface.
602
603 :return: the cursor
604
605 :raise Warning: Non-fatal warning
606 :raise Error: Error
607 """
608 dbi = self.__driver.get_import()
609 try:
610 return Cursor(self.__db.cursor(), self.__driver)
611 except dbi.Warning, val:
612 raise Warning(val)
613 except dbi.Error, val:
614 raise Error(val)
615
617 """
618 Commit the current transaction.
619
620 :raise Warning: Non-fatal warning
621 :raise Error: Error
622 """
623 dbi = self.__driver.get_import()
624 try:
625 self.__db.commit()
626 except dbi.Warning, val:
627 raise Warning(val)
628 except dbi.Error, val:
629 raise Error(val)
630
632 """
633 Roll the current transaction back.
634
635 :raise Warning: Non-fatal warning
636 :raise Error: Error
637 """
638 dbi = self.__driver.get_import()
639 try:
640 self.__db.rollback()
641 except dbi.Warning, val:
642 raise Warning(val)
643 except dbi.Error, val:
644 raise Error(val)
645
647 """
648 Close the database connection.
649
650 :raise Warning: Non-fatal warning
651 :raise Error: Error
652 """
653 dbi = self.__driver.get_import()
654 try:
655 self.__db.close()
656 except dbi.Warning, val:
657 raise Warning(val)
658 except dbi.Error, val:
659 raise Error(val)
660
662 """
663 Base class for all DB drivers.
664 """
665
666 @abstract
668 """
669 Get a bound import for the underlying DB API module. All subclasses
670 must provide an implementation of this method. Here's an example,
671 assuming the real underlying Python DB API module is 'foosql':
672
673 .. python::
674
675 def get_import(self):
676 import foosql
677 return foosql
678
679 :return: a bound module
680 """
681 pass
682
685
686 @abstract
688 """
689 Get the driver's name, for display. The returned name ought to be
690 a reasonable identifier for the database (e.g., 'SQL Server',
691 'MySQL'). All subclasses must provide an implementation of this
692 method.
693
694 :rtype: str
695 :return: the driver's displayable name
696 """
697 pass
698
699 display_name = property(__display_name,
700 doc='get a displayable name for the driver')
701 - def connect(self,
702 host='localhost',
703 port=None,
704 user=None,
705 password='',
706 database=None):
707 """
708 Connect to the underlying database. Subclasses should *not*
709 override this method. Instead, a subclass should override the
710 ``do_connect()`` method.
711
712 :Parameters:
713 host : str
714 the host where the database lives
715 port : int
716 the TCP port to use when connecting, or ``None``
717 user : str
718 the user to use when connecting, or ``None``
719 password : str
720 the password to use when connecting, or ``None``
721 database : str
722 the name of the database to which to connect
723
724 :rtype: ``db``
725 :return: a ``db`` object representing the open database
726
727 :raise Warning: Non-fatal warning
728 :raise Error: Error
729 """
730 dbi = self.get_import()
731 try:
732 self.__db = self.do_connect(host=host,
733 port=port,
734 user=user,
735 password=password,
736 database=database)
737 return DB(self.__db, self)
738 except dbi.Warning, val:
739 raise Warning(val)
740 except dbi.Error, val:
741 raise Error(val)
742
743 @abstract
744 - def do_connect(self,
745 host='localhost',
746 port=None,
747 user='',
748 password='',
749 database='default'):
750 """
751 Connect to the actual underlying database, using the driver.
752 Subclasses must provide an implementation of this method. The
753 method must return the result of the real DB API implementation's
754 ``connect()`` method. For instance:
755
756 .. python::
757
758 def do_connect():
759 dbi = self.get_import()
760 return dbi.connect(host=host, user=user, passwd=password,
761 database=database)
762
763 There is no need to catch exceptions; the ``DBDriver`` class's
764 ``connect()`` method handles that.
765
766 :Parameters:
767 host : str
768 the host where the database lives
769 port : int
770 the TCP port to use when connecting
771 user : str
772 the user to use when connecting
773 password : str
774 the password to use when connecting
775 database : str
776 the name of the database to which to connect
777
778 :rtype: object
779 :return: a DB API-compliant object representing the open database
780
781 :raise Warning: Non-fatal warning
782 :raise Error: Error
783 """
784 pass
785
806
841
928
930 """
931 Get the list of tables in the database.
932
933 :Parameters:
934 cursor : Cursor
935 a ``Cursor`` object from a recent query
936
937 :rtype: list
938 :return: List of table names. The list will be empty if the database
939 contains no tables.
940
941 :raise NotImplementedError: Capability not supported by database driver
942 :raise Warning: Non-fatal warning
943 :raise Error: Error
944 """
945 raise NotImplementedError
946
948 """
949 Determines whether a table name represents a legal table in the
950 current database, throwing an ``Error`` if not.
951
952 :Parameters:
953 cursor : Cursor
954 an open ``Cursor``
955
956 table_name : str
957 the table name
958
959 :raise Error: bad table name
960 """
961 if not self._is_valid_table(cursor, table_name):
962 raise Error, 'No such table: "%s"' % table_name
963
965 """
966 Determines whether a table name represents a legal table in the
967 current database, throwing an ``Error`` if not.
968
969 :Parameters:
970 cursor : Cursor
971 an open ``Cursor``
972
973 table_name : str
974 the table name
975
976 :rtype: bool
977 :return: ``True`` if the table is valid, ``False`` if not
978 """
979 tables = self.get_tables(cursor)
980 return table_name in tables
981