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 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 _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
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],
# -*- 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):
