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