def add_film_info(rj): result = None ext_oid = None if 'externalOrderId' in rj: conv = MySQLConverter() ext_oid = rj['externalOrderId'] success_url = conv.escape(rj.get('successUrl', None)) cancel_url = conv.escape(rj.get('cancelUrl', None)) db = db_connect() try: if not ext_oid: result = db_query(db, 'insert into vidimax (film_id, type, name, price) values ({film_id}, "{type}", "{name}", {price});'\ .format(film_id=rj['id'], type=rj['type'], name=rj['name'], price=int(int(rj['price'])/100)), commit=True, lastrow=True) else: query = 'insert into vidimax values (null, null, "{external_oid}", {film_id}, "{type}", "{name}", {price}, '.format( external_oid=ext_oid, film_id=rj['id'], type=rj['type'], name=rj['name'], price=int(int(rj['price'])/100) ) if success_url: query += '"{0}", '.format(success_url) else: query += 'null, ' if cancel_url: query += '"{0}");'.format(cancel_url) else: query += 'null);' result = db_query(db, query, commit=True, lastrow=True) except Exception as e: print e pass finally: db_disconnect(db) return result
def main(): """Entyr point.""" db = mysql.connector.connect(option_files="config.conf", use_pure=True) cursor = db.cursor() # cmd_query_iter() converter = MySQLConverter(db.charset, True) q1 = """SELECT Name, CountryCode, Population FROM world.city WHERE CountryCode = 'USA' ORDER BY Population DESC LIMIT 3""" q2 = "DO SLEEP(3)" q3 = """SELECT Name, CountryCode, Population FROM world.city WHERE CountryCode = 'IND' ORDER BY Population DESC LIMIT 3""" queries = [q1, q2, q3] result = db.cmd_query_iter(";".join(queries)) # Iterate throw the results count = 0 # It is one of the SELECT statements # as it has coumn definitions # print the result. for results in result: count = count + 1 time = datetime.now().strftime('%H:%M:%S') print(f"query {count} - {time}\n") if 'columns' in results: print(f"{'City':18s} {'Code':7s} {'Popul':3s}") city, eof = db.get_row() while not eof: values = converter.row_to_python(city, results["columns"]) print( f"{values[0]:18s} {values[1]:7s} {values[2]/10000000:3f}" ) city, eof = db.get_row() else: print("No results.") sleep(2) print("") cursor.close() db.close()
def obj2sql(obj): """Convert a Python object to an SQL object. This function convert Python objects to SQL values using the conversion functions in the database connector package.""" from mysql.connector.conversion import MySQLConverter return MySQLConverter().quote(obj)
def _DATETIME_to_python(self, value, dsc=None): """Connector/Python always returns naive datetime.datetime Connector/Python always returns naive timestamps since MySQL has no time zone support. Since Django needs non-naive, we need to add the UTC time zone. Returns datetime.datetime() """ if not value: return None dt = MySQLConverter._DATETIME_to_python(self, value) if settings.USE_TZ and timezone.is_naive(dt): dt = dt.replace(tzinfo=timezone.utc) return dt
def _DATETIME_to_python(self, value, dsc=None): """Connector/Python always returns naive datetime.datetime Connector/Python always returns naive timestamps since MySQL has no time zone support. Since Ohm needs non-naive, assume all datetimes in database are in UTC then convert to Pacific Time Returns datetime.datetime() """ if not value: return value dttm = MySQLConverter._DATETIME_to_python(self, value) if not dttm: return dttm if is_naive(dttm): dttm = db_timestamp_to_pacific_datetime(dttm) return dttm
def _get_sql_data(self, data): """ returns tuple with (columns, values) """ em = EntityManager() db = em.get_db() config = Config() charset = config.db.charset if "charset" in config.db.keys( ) else "utf8" converter = MySQLConverter(charset) def none_to_null(val): if val is None: return "NULL" return val def quote(val): if isinstance(val, NUMERIC_TYPES): return str(val) return "'" + val + "'" def quote_col(val): return "`" + val + "`" _escape_value = compose(none_to_null, quote, converter.escape) _escape_column = compose( none_to_null, quote_col, converter.escape) #column quting is different than normal quotes if self.key_name not in data.keys( ) and self.key is not None: #add the key to the data data[self.key_name] = self.key columns = list() values = list() for k, v in data.items(): values.append(_escape_value(v)) columns.append(_escape_column(k)) return (columns, values)
def _build_update_blob(self, row, new_db, name, blob_col): """Build an UPDATE statement to update blob fields. row[in] a row to process new_db[in] new database name name[in] name of the table conn_val[in] connection information for the destination server query[in] the INSERT string for executemany() blob_col[in] number of the column containing the blob Returns UPDATE string """ if self.column_format is None: self.get_column_metadata() blob_insert = "UPDATE %s.%s SET " % (new_db, name) where_values = [] do_commas = False has_data = False stop = len(row) for col in range(0, stop): col_name = self.q_column_names[col] if col in self.blob_columns: if row[col] is not None and len(row[col]) > 0: if do_commas: blob_insert += ", " blob_insert += "%s = " % col_name + "%s" % \ MySQLConverter().quote(row[col]) has_data = True do_commas = True else: # Convert None values to NULL (not '' to NULL) if row[col] is None: value = 'NULL' else: value = "'{0}'".format(row[col]) where_values.append("{0} = {1}".format(col_name, value)) if has_data: return blob_insert + " WHERE " + " AND ".join(where_values) + ";" return None
def quote_value(self, value): # Inner import to allow module to fail to load gracefully from mysql.connector.conversion import MySQLConverter return MySQLConverter.quote(MySQLConverter.escape(value))
option_files="my.ini", use_pure=True) # Execute a query result = db.cmd_query( """SELECT Name, CountryCode, Population FROM world.city WHERE Population > 9000000 ORDER BY Population DESC""" ) # Fetch the rows (cities, eof) = db.get_rows() # Initialize the converter converter = MySQLConverter( db.charset, True) # Print the rows found print(__file__ + " - Using MySQLConverter:") print("") print( "{0:15s} {1:7s} {2:3s}".format( "City", "Country", "Pop" ) ) for city in cities: values = converter.row_to_python( city, result["columns"]) print( "{0:15s} {1:^7s} {2:4.1f}".format( values[0],
def __init__(self, charset=None, use_unicode=True): MySQLConverter.__init__(self, charset, use_unicode) self._cache_field_types = {}
# -*- coding: utf-8 -*- """ 这些代码节选自: # MySQL Connector/Python - MySQL driver written in Python. # Copyright (c) 2009, 2013, Oracle and/or its affiliates. All rights reserved. """ import re from mysql.connector.conversion import (MySQLConverterBase, MySQLConverter) RE_PY_PARAM = re.compile(b'(%s)') g_converter = MySQLConverter("utf8", True) class _ParamSubstitutor(object): """ Substitutes parameters into SQL statement. """ def __init__(self, params): self.params = params self.index = 0 def __call__(self, matchobj): index = self.index self.index += 1 return self.params[index] @property def remaining(self):
def _datetime_to_mysql(self, value): if not is_naive(value): value = value.replace(microsecond=0).astimezone(global_db_tz or pytz.utc) return MySQLConverter._datetime_to_mysql(self, value)