def open(self):
try:
# TODO: remove datetime convert function
from pymysql.constants import FIELD_TYPE
from pymysql.converters import conversions as conv
conv = conv.copy()
del conv[FIELD_TYPE.DATETIME]
conv[10] = str
self.conn = pymysql.connect(
#unix_socket='/secui/config/db/mysql.sock',
host=self.connect_info.get('host'),
port=self.connect_info.get('port'),
user=self.connect_info.get('user'),
passwd=self.connect_info.get('passwd'),
db=self.connect_info.get('db'),
autocommit=self.connect_info.get('autocommit'),
charset=self.connect_info.get('charset'),
cursorclass=self.connect_info.get('cursorclass'),
conv=conv)
except:
raise
else:
self.cursor = self.conn.cursor()
return self
def pymysql_converter():
from pymysql.constants import FIELD_TYPE
from pymysql.converters import conversions as conv
converter = conv.copy()
converter[FIELD_TYPE.DECIMAL] = float
converter[FIELD_TYPE.NEWDECIMAL] = float
return converter
def __init__(self,params=None,removeConv=False): self.open = False self.db = None self.removeConv = removeConv # print(config['DB']) if params is not None: self.params=params else: self.params=app.config['DB'] if self.params is not None: if removeConv: cf = conv.copy() del cf[FIELD_TYPE.DATE] del cf[FIELD_TYPE.DATETIME] del cf[FIELD_TYPE.TIME] self.db = pymysql.connect(host=self.params['host'],port=self.params['port'], user=self.params['user'], password=self.params['password'], db=self.params['db'], cursorclass=pymysql.cursors.DictCursor,conv=cf) else: self.db = pymysql.connect(host=self.params['host'],port=self.params['port'], user=self.params['user'], password=self.params['password'], db=self.params['db'], cursorclass=pymysql.cursors.DictCursor) self.open = True self.cur=self.db.cursor()
def mysql_convert_decimal_to_float():
"""
The default datatype returned by mysql-python for numerics is decimal.Decimal.
This type cannot be serialised to JSON, therefore we need to autoconvert to floats.
Even worse, there's two types of decimals created by the MySQLdb driver, so we must
override both.
:return converter: Converter object
"""
converter = None
try:
import MySQLdb.converters # pylint: disable=import-error
from MySQLdb.constants import FIELD_TYPE # pylint: disable=import-error
converter = MySQLdb.converters.conversions.copy()
converter[FIELD_TYPE.DECIMAL] = float
converter[FIELD_TYPE.NEWDECIMAL] = float
except ImportError:
try:
from pymysql.constants import FIELD_TYPE
from pymysql.converters import conversions as conv
converter = conv.copy()
converter[FIELD_TYPE.DECIMAL] = float
converter[FIELD_TYPE.NEWDECIMAL] = float
except ImportError:
raise RucioException(
'Trying to use MySQL without mysql-python or pymysql installed!'
)
return converter
def connect(self, database=None, user=None, password=None, host=None,
port=None, socket=None, charset=None, local_infile=None, ssl=None):
db = (database or self.dbname)
user = (user or self.user)
password = (password or self.password)
host = (host or self.host)
port = (port or self.port)
socket = (socket or self.socket)
charset = (charset or self.charset)
local_infile = (local_infile or self.local_infile)
ssl = (ssl or self.ssl)
_logger.debug('Connection DB Params: \n'
'\tdatabase: %r'
'\tuser: %r'
'\thost: %r'
'\tport: %r'
'\tsocket: %r'
'\tcharset: %r'
'\tlocal_infile: %r'
'\tssl: %r',
db, user, host, port, socket, charset, local_infile, ssl)
conv = conversions.copy()
conv.update({
FIELD_TYPE.TIMESTAMP: lambda obj: (convert_mysql_timestamp(obj) or obj),
FIELD_TYPE.DATETIME: lambda obj: (convert_datetime(obj) or obj),
FIELD_TYPE.TIME: lambda obj: (convert_timedelta(obj) or obj),
FIELD_TYPE.DATE: lambda obj: (convert_date(obj) or obj),
})
conn = pymysql.connect(database=db, user=user, password=password,
host=host, port=port, unix_socket=socket,
use_unicode=True, charset=charset, autocommit=True,
client_flag=pymysql.constants.CLIENT.INTERACTIVE,
local_infile=local_infile,
conv=conv, ssl=ssl)
if hasattr(self, 'conn'):
self.conn.close()
self.conn = conn
# Update them after the connection is made to ensure that it was a
# successful connection.
self.dbname = db
self.user = user
self.password = password
self.host = host
self.port = port
self.socket = socket
self.charset = charset
self.ssl = ssl
# retrieve connection id
self.reset_connection_id()
def connect(self, database=None, user=None, password=None, host=None,
port=None, socket=None, charset=None, local_infile=None, ssl=None):
db = (database or self.dbname)
user = (user or self.user)
password = (password or self.password)
host = (host or self.host)
port = (port or self.port)
socket = (socket or self.socket)
charset = (charset or self.charset)
local_infile = (local_infile or self.local_infile)
ssl = (ssl or self.ssl)
_logger.debug('Connection DB Params: \n'
'\tdatabase: %r'
'\tuser: %r'
'\thost: %r'
'\tport: %r'
'\tsocket: %r'
'\tcharset: %r'
'\tlocal_infile: %r'
'\tssl: %r',
database, user, host, port, socket, charset, local_infile, ssl)
conv = conversions.copy()
conv.update({
FIELD_TYPE.TIMESTAMP: lambda obj: (convert_mysql_timestamp(obj) or obj),
FIELD_TYPE.DATETIME: lambda obj: (convert_datetime(obj) or obj),
FIELD_TYPE.TIME: lambda obj: (convert_timedelta(obj) or obj),
FIELD_TYPE.DATE: lambda obj: (convert_date(obj) or obj),
})
conn = pymysql.connect(database=db, user=user, password=password,
host=host, port=port, unix_socket=socket,
use_unicode=True, charset=charset, autocommit=True,
client_flag=pymysql.constants.CLIENT.INTERACTIVE,
local_infile=local_infile,
conv=conv, ssl=ssl)
if hasattr(self, 'conn'):
self.conn.close()
self.conn = conn
# Update them after the connection is made to ensure that it was a
# successful connection.
self.dbname = db
self.user = user
self.password = password
self.host = host
self.port = port
self.socket = socket
self.charset = charset
self.ssl = ssl
# retrieve connection id
self.reset_connection_id()
def openDb(self): if self.removeConv: cf = conv.copy() del cf[FIELD_TYPE.DATE] del cf[FIELD_TYPE.DATETIME] del cf[FIELD_TYPE.TIME] self.db = pymysql.connect(host=self.params['host'],port=self.params['port'], user=self.params['user'], password=self.params['password'], db=self.params['db'], cursorclass=pymysql.cursors.DictCursor,conv=cf) else: self.db = pymysql.connect(host=self.params['host'],port=self.params['port'], user=self.params['user'], password=self.params['password'], db=self.params['db'], cursorclass=pymysql.cursors.DictCursor) self.open = True
def handle(self, *args, **options):
# Our Reading dates in MySQL are sometimes like '2005-01-00' or
# '2005-00-00' and pymysql will try converting these into python dates
# which results in `None`. So we'll stop it converting them:
new_conv = conv.copy()
del new_conv[FIELD_TYPE.DATE]
self.connection = pymysql.connect(host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
db=DB_NAME,
port=int(DB_PORT),
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor,
conv=new_conv)
self.import_publication_series()
self.import_publications()
self.import_authors()
self.import_author_publications()
self.import_readings()
self.connection.close()
Convert something into a SQL string literal. If using
MySQL-3.23 or newer, string_literal() is a method of the
_mysql.MYSQL object, and this function will be overridden with
that method when the connection is created.
"""
#return Thing2Literal(value.strftime("%Y-%m-%d %H:%M:%S"), conv)
return escape_datetime(value)
# MySQLdb-1.2.1 returns TIME columns as timedelta -- they are more like
# timedelta in terms of actual behavior as they are signed and include days --
# and Django expects time, so we still need to override that. We also need to
# add special handling for SafeText and SafeBytes as MySQLdb's type
# checking is too tight to catch those (see Django ticket #6052).
# Finally, MySQLdb always returns naive datetime objects. However, when
# timezone support is active, Django expects timezone-aware datetime objects.
django_conversions = conversions.copy()
django_conversions.update({
FIELD_TYPE.TIME: conversion_adapter(util.typecast_time),
FIELD_TYPE.DECIMAL: conversion_adapter(util.typecast_decimal),
FIELD_TYPE.NEWDECIMAL: conversion_adapter(util.typecast_decimal),
FIELD_TYPE.DATETIME: parse_datetime_with_timezone_support,
datetime.datetime: adapt_datetime_with_timezone_support,
})
# -----------------------------------------------------------------------------
# The rest of this file is taken verbatim from django/db/backends/mysql/base.py
# which, unfortunately, cannot be imported directly in an environment without
# MySQLdb installed.
# -----------------------------------------------------------------------------
def connect(self, database=None, user=None, password=None, host=None,
port=None, socket=None, charset=None, local_infile=None,
ssl=None, ssh_host=None, ssh_port=None, ssh_user=None,
ssh_password=None, ssh_key_filename=None):
db = (database or self.dbname)
user = (user or self.user)
password = (password or self.password)
host = (host or self.host)
port = (port or self.port)
socket = (socket or self.socket)
charset = (charset or self.charset)
local_infile = (local_infile or self.local_infile)
ssl = (ssl or self.ssl)
ssh_user = (ssh_user or self.ssh_user)
ssh_host = (ssh_host or self.ssh_host)
ssh_port = (ssh_port or self.ssh_port)
ssh_password = (ssh_password or self.ssh_password)
ssh_key_filename = (ssh_key_filename or self.ssh_key_filename)
_logger.debug(
'Connection DB Params: \n'
'\tdatabase: %r'
'\tuser: %r'
'\thost: %r'
'\tport: %r'
'\tsocket: %r'
'\tcharset: %r'
'\tlocal_infile: %r'
'\tssl: %r'
'\tssh_user: %r'
'\tssh_host: %r'
'\tssh_port: %r'
'\tssh_password: %r'
'\tssh_key_filename: %r',
db, user, host, port, socket, charset, local_infile, ssl,
ssh_user, ssh_host, ssh_port, ssh_password, ssh_key_filename
)
conv = conversions.copy()
conv.update({
FIELD_TYPE.TIMESTAMP: lambda obj: (convert_mysql_timestamp(obj) or obj),
FIELD_TYPE.DATETIME: lambda obj: (convert_datetime(obj) or obj),
FIELD_TYPE.TIME: lambda obj: (convert_timedelta(obj) or obj),
FIELD_TYPE.DATE: lambda obj: (convert_date(obj) or obj),
})
defer_connect = False
if ssh_host:
defer_connect = True
conn = pymysql.connect(
database=db, user=user, password=password, host=host, port=port,
unix_socket=socket, use_unicode=True, charset=charset,
autocommit=True, client_flag=pymysql.constants.CLIENT.INTERACTIVE,
local_infile=local_infile, conv=conv, ssl=ssl, program_name="mycli",
defer_connect=defer_connect
)
if ssh_host and paramiko:
client = paramiko.SSHClient()
client.load_system_host_keys()
client.set_missing_host_key_policy(paramiko.WarningPolicy())
client.connect(
ssh_host, ssh_port, ssh_user, ssh_password,
key_filename=ssh_key_filename
)
chan = client.get_transport().open_channel(
'direct-tcpip',
(host, port),
('0.0.0.0', 0),
)
conn.connect(chan)
if hasattr(self, 'conn'):
self.conn.close()
self.conn = conn
# Update them after the connection is made to ensure that it was a
# successful connection.
self.dbname = db
self.user = user
self.password = password
self.host = host
self.port = port
self.socket = socket
self.charset = charset
self.ssl = ssl
# retrieve connection id
self.reset_connection_id()
if settings.DEBUG:
from warnings import filterwarnings
filterwarnings("error", category=Database.Warning)
DatabaseError = Database.DatabaseError
IntegrityError = Database.IntegrityError
# PyMySQL raises an InternalError with error 1048 (BAD_NULL) -- here we patch
# the error map to force an IntegrityError instead.
from pymysql.err import error_map
error_map[1048] = IntegrityError
django_conversions = conversions.copy()
# It's impossible to import datetime_or_None directly from MySQLdb.times
datetime_or_None = conversions[FIELD_TYPE.DATETIME]
# As with the MySQLdb adapter, PyMySQL returns TIME columns as timedelta --
# so we add a conversion here
def datetime_or_None_with_timezone_support(connection, field, obj):
dt = datetime_or_None(connection, field, obj)
# Confirm that dt is naive before overwriting its tzinfo.
if dt is not None and settings.USE_TZ and is_naive(dt):
dt = dt.replace(tzinfo=utc)
return dt
# The conversion functions in django.db.util only accept one argument, while
# the PyMySQL converters require three. This adapter function produces a
def connect(self, database=None, user=None, password=None, host=None,
port=None, socket=None, charset=None, local_infile=None,
ssl=None, ssh_host=None, ssh_port=None, ssh_user=None,
ssh_password=None, ssh_key_filename=None):
db = (database or self.dbname)
user = (user or self.user)
password = (password or self.password)
host = (host or self.host)
port = (port or self.port)
socket = (socket or self.socket)
charset = (charset or self.charset)
local_infile = (local_infile or self.local_infile)
ssl = (ssl or self.ssl)
ssh_user = (ssh_user or self.ssh_user)
ssh_host = (ssh_host or self.ssh_host)
ssh_port = (ssh_port or self.ssh_port)
ssh_password = (ssh_password or self.ssh_password)
ssh_key_filename = (ssh_key_filename or self.ssh_key_filename)
_logger.debug(
'Connection DB Params: \n'
'\tdatabase: %r'
'\tuser: %r'
'\thost: %r'
'\tport: %r'
'\tsocket: %r'
'\tcharset: %r'
'\tlocal_infile: %r'
'\tssl: %r'
'\tssh_user: %r'
'\tssh_host: %r'
'\tssh_port: %r'
'\tssh_password: %r'
'\tssh_key_filename: %r',
db, user, host, port, socket, charset, local_infile, ssl,
ssh_user, ssh_host, ssh_port, ssh_password, ssh_key_filename
)
conv = conversions.copy()
conv.update({
FIELD_TYPE.TIMESTAMP: lambda obj: (convert_datetime(obj) or obj),
FIELD_TYPE.DATETIME: lambda obj: (convert_datetime(obj) or obj),
FIELD_TYPE.TIME: lambda obj: (convert_timedelta(obj) or obj),
FIELD_TYPE.DATE: lambda obj: (convert_date(obj) or obj),
})
defer_connect = False
if ssh_host:
defer_connect = True
conn = pymysql.connect(
database=db, user=user, password=password, host=host, port=port,
unix_socket=socket, use_unicode=True, charset=charset,
autocommit=True, client_flag=pymysql.constants.CLIENT.INTERACTIVE,
local_infile=local_infile, conv=conv, ssl=ssl, program_name="mycli",
defer_connect=defer_connect
)
if ssh_host:
client = paramiko.SSHClient()
client.load_system_host_keys()
client.set_missing_host_key_policy(paramiko.WarningPolicy())
client.connect(
ssh_host, ssh_port, ssh_user, ssh_password,
key_filename=ssh_key_filename
)
chan = client.get_transport().open_channel(
'direct-tcpip',
(host, port),
('0.0.0.0', 0),
)
conn.connect(chan)
if hasattr(self, 'conn'):
self.conn.close()
self.conn = conn
# Update them after the connection is made to ensure that it was a
# successful connection.
self.dbname = db
self.user = user
self.password = password
self.host = host
self.port = port
self.socket = socket
self.charset = charset
self.ssl = ssl
# retrieve connection id
self.reset_connection_id()
def database(engine='mysql',
host='database',
db='test',
user='******',
*a,
**k):
"""create a database object"""
# pylint: disable=invalid-name
if engine == 'mysql':
import MySQLdb
db = Database(MySQLdb.connect, host=host, db=db, user=user, *a, **k)
db.autocommit(1)
return db
elif engine == 'sqlite':
import sqlite3
db = Database(sqlite3.connect, database=db, *a, **k)
return db
elif engine == 'pymysql':
import pymysql
db = Database(pymysql.connect,
host=host,
db=db,
user=user,
charset='utf8',
*a,
**k)
db.autocommit(1)
return db
elif engine == 'pymysql_back':
# pymysql engine with mysqldb/dz backwards compatibility
# This is mainly done for tests, running dz tests, unchanged, under
# pymysql
import pymysql
from pymysql.converters import conversions
from pymysql.constants import FIELD_TYPE
from pymysql.cursors import Cursor
class LegacyCursor(Cursor):
"""MySQLdb combatible cursor"""
def __getattribute__(self, name):
r = object.__getattribute__(self, name)
if name == 'lastrowid':
r = long(r)
return r
mysqldb_compat = conversions.copy()
mysqldb_compat[FIELD_TYPE.LONG] = long
mysqldb_compat[FIELD_TYPE.LONGLONG] = long
db = Database(pymysql.connect,
host=host,
db=db,
user=user,
conv=mysqldb_compat,
charset='latin1',
use_unicode=False,
cursorclass=LegacyCursor,
*a,
**k)
db.autocommit(1)
return db
def database(
engine='mysql',
host='database',
db='test',
user='******',
*a,
**k
):
"""create a database object"""
# pylint: disable=invalid-name
if engine == 'mysql':
import MySQLdb
db = Database(MySQLdb.connect, host=host, db=db, user=user, *a, **k)
db.autocommit(1)
return db
elif engine == 'sqlite':
import sqlite3
db = Database(sqlite3.connect, database=db, *a, **k)
return db
elif engine == 'pymysql':
import pymysql
db = Database(
pymysql.connect,
host=host,
db=db,
user=user,
charset='utf8',
*a,
**k
)
db.autocommit(1)
return db
elif engine == 'pymysql_back':
# pymysql engine with mysqldb/dz backwards compatibility
# This is mainly done for tests, running dz tests, unchanged, under
# pymysql
import pymysql
from pymysql.converters import conversions
from pymysql.constants import FIELD_TYPE
from pymysql.cursors import Cursor
class LegacyCursor(Cursor):
"""MySQLdb combatible cursor"""
def __getattribute__(self, name):
r = object.__getattribute__(self, name)
if name == 'lastrowid':
r = long(r)
return r
mysqldb_compat = conversions.copy()
mysqldb_compat[FIELD_TYPE.LONG] = long
mysqldb_compat[FIELD_TYPE.LONGLONG] = long
db = Database(
pymysql.connect,
host=host,
db=db,
user=user,
conv=mysqldb_compat,
charset='latin1',
use_unicode=False,
cursorclass=LegacyCursor,
*a,
**k
)
db.autocommit(1)
return db
