Edit on GitHub

backend.lib.database_mysql

Database wrapper

 1"""
 2Database wrapper
 3"""
 4import pymysql.connections as mysqlconnections
 5import pymysql
 6
 7class MySQLDatabase:
 8	"""
 9	Simple database handler for MySQL connections
10
11	4CAT uses PostgreSQL for its database - this MySQL class is available as a
12	convenience for data sources that wish to use the Sphinx full-text search
13	engine via SphinxQL. As such, only methods needed for that (i.e.
14	`fetchall()`) are implemented.
15	"""
16	cursor = None
17	log = None
18
19	def __init__(self, logger=None, dbname=None, user=None, password=None, host=None, port=None):
20		"""
21		Set up database connection
22		"""
23		self.connection = mysqlconnections.Connection(database=dbname, user=user, password=password, host=host, port=port)
24		self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)
25		if logger:
26			self.log = logger
27
28	def mogrify(self, query, replacements):
29		"""
30		Parse a query with replacement variables
31
32		:param str query:  Query
33		:param list replacements:  Replacement variables
34		:return str: Parsed query
35		"""
36		return self.cursor.mogrify(query, replacements)
37
38	def query(self, query, replacements=None):
39		"""
40		Execute a query
41
42		:param string query: Query
43		:param args: Replacement values
44		:return None:
45		"""
46		if self.log:
47			self.log.debug("Executing query %s" % self.mogrify(query, replacements))
48
49		return self.cursor.execute(query, replacements)
50
51	def fetchall(self, query, *args):
52		"""
53		Fetch all rows for a query
54		:param query:  Query
55		:param args: Replacement values
56		:return list: The result rows, as a list
57		"""
58		self.query(query, *args)
59		try:
60			return self.cursor.fetchall()
61		except AttributeError:
62			return []
63
64	def fetchone(self, query, *args):
65		"""
66		Fetch one result row
67
68		:param query: Query
69		:param args: Replacement values
70		:return: The row, as a dictionary, or None if there were no rows
71		"""
72		self.query(query, *args)
73		try:
74			return self.cursor.fetchone()
75		except pymysql.ProgrammingError:
76			self.commit()
77			return None
78
79	def commit(self):
80		"""
81		Commit the current transaction
82
83		This is required for UPDATE etc to stick around.
84		"""
85		self.connection.commit()
86
87	def rollback(self):
88		"""
89		Roll back the current transaction
90		"""
91		self.connection.rollback()
92
93	def close(self):
94		"""
95		Close connection
96
97		Running queries after this is probably a bad idea!
98		"""
99		self.connection.close()
class MySQLDatabase:
  8class MySQLDatabase:
  9	"""
 10	Simple database handler for MySQL connections
 11
 12	4CAT uses PostgreSQL for its database - this MySQL class is available as a
 13	convenience for data sources that wish to use the Sphinx full-text search
 14	engine via SphinxQL. As such, only methods needed for that (i.e.
 15	`fetchall()`) are implemented.
 16	"""
 17	cursor = None
 18	log = None
 19
 20	def __init__(self, logger=None, dbname=None, user=None, password=None, host=None, port=None):
 21		"""
 22		Set up database connection
 23		"""
 24		self.connection = mysqlconnections.Connection(database=dbname, user=user, password=password, host=host, port=port)
 25		self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)
 26		if logger:
 27			self.log = logger
 28
 29	def mogrify(self, query, replacements):
 30		"""
 31		Parse a query with replacement variables
 32
 33		:param str query:  Query
 34		:param list replacements:  Replacement variables
 35		:return str: Parsed query
 36		"""
 37		return self.cursor.mogrify(query, replacements)
 38
 39	def query(self, query, replacements=None):
 40		"""
 41		Execute a query
 42
 43		:param string query: Query
 44		:param args: Replacement values
 45		:return None:
 46		"""
 47		if self.log:
 48			self.log.debug("Executing query %s" % self.mogrify(query, replacements))
 49
 50		return self.cursor.execute(query, replacements)
 51
 52	def fetchall(self, query, *args):
 53		"""
 54		Fetch all rows for a query
 55		:param query:  Query
 56		:param args: Replacement values
 57		:return list: The result rows, as a list
 58		"""
 59		self.query(query, *args)
 60		try:
 61			return self.cursor.fetchall()
 62		except AttributeError:
 63			return []
 64
 65	def fetchone(self, query, *args):
 66		"""
 67		Fetch one result row
 68
 69		:param query: Query
 70		:param args: Replacement values
 71		:return: The row, as a dictionary, or None if there were no rows
 72		"""
 73		self.query(query, *args)
 74		try:
 75			return self.cursor.fetchone()
 76		except pymysql.ProgrammingError:
 77			self.commit()
 78			return None
 79
 80	def commit(self):
 81		"""
 82		Commit the current transaction
 83
 84		This is required for UPDATE etc to stick around.
 85		"""
 86		self.connection.commit()
 87
 88	def rollback(self):
 89		"""
 90		Roll back the current transaction
 91		"""
 92		self.connection.rollback()
 93
 94	def close(self):
 95		"""
 96		Close connection
 97
 98		Running queries after this is probably a bad idea!
 99		"""
100		self.connection.close()

Simple database handler for MySQL connections

4CAT uses PostgreSQL for its database - this MySQL class is available as a convenience for data sources that wish to use the Sphinx full-text search engine via SphinxQL. As such, only methods needed for that (i.e. fetchall()) are implemented.

MySQLDatabase( logger=None, dbname=None, user=None, password=None, host=None, port=None)
20	def __init__(self, logger=None, dbname=None, user=None, password=None, host=None, port=None):
21		"""
22		Set up database connection
23		"""
24		self.connection = mysqlconnections.Connection(database=dbname, user=user, password=password, host=host, port=port)
25		self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)
26		if logger:
27			self.log = logger

Set up database connection

cursor = None
log = None
connection
def mogrify(self, query, replacements):
29	def mogrify(self, query, replacements):
30		"""
31		Parse a query with replacement variables
32
33		:param str query:  Query
34		:param list replacements:  Replacement variables
35		:return str: Parsed query
36		"""
37		return self.cursor.mogrify(query, replacements)

Parse a query with replacement variables

Parameters
  • str query: Query
  • list replacements: Replacement variables
Returns

Parsed query

def query(self, query, replacements=None):
39	def query(self, query, replacements=None):
40		"""
41		Execute a query
42
43		:param string query: Query
44		:param args: Replacement values
45		:return None:
46		"""
47		if self.log:
48			self.log.debug("Executing query %s" % self.mogrify(query, replacements))
49
50		return self.cursor.execute(query, replacements)

Execute a query

Parameters
  • string query: Query
  • args: Replacement values
Returns
def fetchall(self, query, *args):
52	def fetchall(self, query, *args):
53		"""
54		Fetch all rows for a query
55		:param query:  Query
56		:param args: Replacement values
57		:return list: The result rows, as a list
58		"""
59		self.query(query, *args)
60		try:
61			return self.cursor.fetchall()
62		except AttributeError:
63			return []

Fetch all rows for a query

Parameters
  • query: Query
  • args: Replacement values
Returns

The result rows, as a list

def fetchone(self, query, *args):
65	def fetchone(self, query, *args):
66		"""
67		Fetch one result row
68
69		:param query: Query
70		:param args: Replacement values
71		:return: The row, as a dictionary, or None if there were no rows
72		"""
73		self.query(query, *args)
74		try:
75			return self.cursor.fetchone()
76		except pymysql.ProgrammingError:
77			self.commit()
78			return None

Fetch one result row

Parameters
  • query: Query
  • args: Replacement values
Returns

The row, as a dictionary, or None if there were no rows

def commit(self):
80	def commit(self):
81		"""
82		Commit the current transaction
83
84		This is required for UPDATE etc to stick around.
85		"""
86		self.connection.commit()

Commit the current transaction

This is required for UPDATE etc to stick around.

def rollback(self):
88	def rollback(self):
89		"""
90		Roll back the current transaction
91		"""
92		self.connection.rollback()

Roll back the current transaction

def close(self):
 94	def close(self):
 95		"""
 96		Close connection
 97
 98		Running queries after this is probably a bad idea!
 99		"""
100		self.connection.close()

Close connection

Running queries after this is probably a bad idea!