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))
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)
