Время выполнения запроса Python SQL
У меня мало опыта работы с Python и SQL. Я учился сам, чтобы закончить магистерскую диссертацию.
Я только что написал небольшой скрипт для тестирования примерно 50 идентично структурированных баз данных, как показано ниже:
import thesis,pyodbc # SQL Server settings drvr = '' host = 'host_directory' user = 'username' pswd = 'password' table = 'tBufferAux' # Found (by inspection) to be the table containing relevant data column = 'Data' # Establish a connection to SQL Server cnxn = pyodbc.connect(driver=drvr, server=host, uid=user, pwd=pswd) # Setup connection endRow = 'SELECT TOP 1 ' + column + ' FROM [' # Query template for ending row with open(thesis.db_metadata_path(),'w') as file: for db in thesis.db_list(): # Prepare queries countRows_query = 'SELECT COUNT(*) FROM [' + db + '].dbo.' + table firstRow_query = endRow + db + '].dbo.' + table + ' ORDER BY ' + column + ' ASC' lastRow_query = endRow + db + '].dbo.' + table + ' ORDER BY ' + column + ' DESC' # Execute queries N_rows = cnxn.cursor().execute(countRows_query).fetchone()[0] first_row = cnxn.cursor().execute(firstRow_query).fetchone() last_row = cnxn.cursor().execute(lastRow_query).fetchone() # Save output to text file file.write(db + ' ' + str(N_rows) + ' ' + str(first_row.Data) + ' ' + str(last_row.Data) + '\n') # Close session cnxn.cursor().close() cnxn.close() Я был удивлен, обнаружив, что эта простая программа запускается почти 10 секунд, поэтому мне было интересно, нормально ли это или у меня есть какая-то часть моего кода, которая может замедлять выполнение. (Напоминаю, что цикл for выполняется всего 56 раз)
Обратите внимание, что любая функция из модуля thesis (настраиваемый) имеет очень небольшое влияние, поскольку все они являются просто назначениями переменных (за исключением thesis.db_list() , который является быстрым чтением файла .txt)
РЕДАКТИРОВАТЬ: Это выходной файл .txt, созданный этой программой. Второй столбец — это количество записей этой таблицы для каждой базы данных.
Python sqlite3: Находим медленные запросы
Привет, коллеги!
При работе с базами данных sqlite передо мной возникла задача поиска медленных запросов и их логгирования.
Спросив всезнающий Google я к сожалению не обнаружил ни одного решения (плохо искал?).
Поэтому я хочу предложить свой вариант протоколирования.
UPD: Спасибо за подсказку, нативный вариант решения называется APSW
Подсчет времени выполнения запроса мы будем вести на уровне курсора.
Для этого переопределим данный класс добавив в него подсчет времени выполнения запроса.
class mycursor ( sqlite3. Cursor ) :
def execute ( self , * args, ** kwargs ) :
timestart = time . clock ( )
query = super ( mycursor, self ) . execute ( * args, ** kwargs )
idle = time . clock ( ) — timestart
if idle > = 0.1 :
file = open ( «sqlite_slow.log» , «a+» )
file . write ( * args )
file . write ( » IDLE = » + str ( idle ) + «n» )
file . close ( )
return query
В данном примере все запросы, выполнение которых превышает 0.1 секунду будут попадать в лог-файл. Соответственно, при необходимости вы можете изменить критерий.
Вызов же полученного курсора необходимо делать следующим образом:
dbconnection = sqlite3. connect ( «some_slqite_base.db)
dbcursor = dbconnection.cursor(mycursor)
dbcursor.execute(» SELECT * FROM sqlite_master «)
В лог файл информация попадает в следующем виде:
insert into objects (‘comment’, ‘xmlns’, ‘name’) values (‘Patch number 125124’, ‘http://oval.mitre.org/XMLSchema/oval-definitions-5#solaris’, ‘patch_object’) IDLE = 1.5530665503253545
insert into advisory_cpe (‘advisory_id’, ‘cpe_id’) values (‘665’, ‘158’) IDLE = 0.19326974126357754
Где значение IDLE это время выполнения команды.
Спасибо за уделенное время. Надеюсь Вам это будет тоже полезным.
Тайм-аут запроса MySQLDB Python
Я пытаюсь установить ограничение по срокам запросов в python MySQLDB. У меня есть ситуация, когда я не контролирую запросы, но должен убедиться, что они не работают в течение установленного срока. Я попытался использовать signal.SIGALRM для прерывания вызова для выполнения, но это, похоже, не работает. Сигнал отправляется, но не добирается до тех пор, пока вызов для завершения не завершится. Я написал тестовый пример, чтобы доказать это поведение:
#!/usr/local/bin/python2.6 import time import signal from somewhere import get_dbc class Timeout(Exception): """ Time Exceded """ def _alarm_handler(*args): raise Timeout dbc = get_dbc() signal.signal(signal.SIGALRM, _alarm_handler) signal.alarm(1) try: print "START: ", time.time() dbc.execute("SELECT SLEEP(10)") except Timeout: print "TIMEOUT!", time.time()' «SELECT SLEEP (10)» имитирует медленный запрос, но я вижу то же поведение с фактическим медленным запросом. Результат:
START: 1254440686.69 TIMEOUT! 1254440696.69 - Почему я не получаю сигнал до завершения выполнения?
- Есть ли еще один надежный способ ограничить время выполнения запроса?
6 ответов
@nosklo на основе скручивания является элегантным и работоспособным, но если вы хотите избежать зависимости от скручивания, задача по-прежнему выполнима, например.
import multiprocessing def query_with_timeout(dbc, timeout, query, *a, **k): conn1, conn2 = multiprocessing.Pipe(False) subproc = multiprocessing.Process(target=do_query, args=(dbc, query, conn2)+a, kwargs=k) subproc.start() subproc.join(timeout) if conn1.poll(): return conn1.recv() subproc.terminate() raise TimeoutError("Query %r ran for >%r" % (query, timeout)) def do_query(dbc, query, conn, *a, **k): cu = dbc.cursor() cu.execute(query, *a, **k) return cu.fetchall() @A.WilsonA.Wilson — нет, conn параметр фактически требуется для этой работы. Команда do_query должна использовать его для отправки результата назад, а не просто для его return .
Ха-ха, пять лет спустя. Конечно, если бы он был модифицирован, чтобы фактически использовать conn для передачи результата обратно, то это имело бы большой смысл. Я предполагаю, что реальный ответ здесь — «это должно быть отредактировано, чтобы использовать этот аргумент, чтобы прояснить его полезность». Это не обязательно для работы, в конце концов, это просто может быть .
Я попытался использовать signal.SIGALRM для прерывания вызова для выполнения, но это, похоже, не работает. Сигнал отправляется, но не добирается до тех пор, пока вызов для завершения не завершится.
Библиотека mysql обрабатывает прерванные системные вызовы внутри, поэтому вы не увидите побочные эффекты SIGALRM до тех пор, пока не завершится вызов API (не дожидаясь уничтожения текущего потока или процесса)
Вы можете попробовать исправить MySQL-Python и использовать параметр MYSQL_OPT_READ_TIMEOUT (добавленный в mysql 5.0.25)
Общие примечания
В последнее время у меня возникла одна и та же проблема с несколькими условиями, которые мне пришлось выполнить:
- решение должно быть потокобезопасным
- несколько подключений к базе данных с одного и того же компьютера могут быть активны одновременно, убейте точное соединение/запрос
- приложение содержит соединения со многими различными базами данных — переносным обработчиком для каждого хоста БД
У нас был следующий макет класса (к сожалению, я не могу опубликовать реальные источники):
class AbstractModel: pass class FirstDatabaseModel(AbstractModel): pass # Connection to one DB host class SecondDatabaseModel(AbstractModel): pass # Connection to one DB host И создал несколько потоков для каждой модели.
Решение Python 3.2
В нашем приложении одна модель = одна база данных. Поэтому я создал «сервисное соединение» для каждой модели (чтобы мы могли выполнять KILL в параллельном соединении). Поэтому, если был создан один экземпляр FirstDatabaseModel , было создано 2 соединения с базой данных; если было создано 5 экземпляров, было использовано только 6 соединений:
class AbstractModel: _service_connection = None # Formal declaration def __init__(self): ''' Somehow load config and create connection ''' self.config = # . self.connection = MySQLFromConfig(self.config) self._init_service_connection() # Get connection ID (pseudocode) self.connection_id = self.connection.FetchOneCol('SELECT CONNECTION_ID()') def _init_service_connection(self): ''' Initialize one singleton connection for model ''' cls = type(self) if cls._service_connection is not None: return cls._service_connection = MySQLFromConfig(self.config) def _kill_connection(self): # Add your own mysql data escaping sql = 'KILL CONNECTION <>'.format(self.connection_id) # Do your own connection check and renewal type(self)._service_connection.execute(sql) Примечание: connection.execute = создать курсор, выполнить, закрыть курсор.
И сделайте кеш-поток безопасным с помощью threading.Lock :
def _init_service_connection(self): ''' Initialize one singleton connection for model ''' cls = type(self) if cls._service_connection is not None: return cls._service_connection = MySQLFromConfig(self.config) cls._service_connection_lock = threading.Lock() def _kill_connection(self): # Add your own mysql data escaping sql = 'KILL CONNECTION <>'.format(self.connection_id) cls = type(self) # Do your own connection check and renewal try: cls._service_connection_lock.acquire() cls._service_connection.execute(sql) finally: cls._service_connection_lock.release() И, наконец, добавьте метод timed execute, используя threading.Timer :
def timed_query(self, sql, timeout=5): kill_query_timer = threading.Timer(timeout, self._kill_connection) kill_query_timer.start() try: self.connection.long_query() finally: kill_query_timer.cancel() Автоматизация выполнения запросов в SQL с помощью Python
Основная идея работы скрипта – генерация запросов с учетом различных параметров и последовательное их выполнение для вставки данных в существующую таблицу или выгрузка данных в файл. Сама программа состоит из 3 частей:
- Соединение c базой данных.
- Определение варьируемых параметров.
- Выполнение запросов к базе (структура построения запросов позволяет выполнять запросы последовательно или параллельно, что позволяет управлять скоростью загрузки/выгрузки данных с сервера).
Соединение с БД определяется фабрикой, в которой содержатся параметры соединения с определенным сервером и определены ссылки на классы для работы с БД.
Сами объекты для работы с БД содержат 3 метода:
- collect– запускает запрос с помощью метода read_sql библиотеки pandas и возвращает DataFrame, содержащий результат выполненного запроса;
- execute– запускает запросы типа CREATE, UPDATE, DELETE\TRUNCATE\DROP;
- execute_many – используется в основном для загрузки данных внутрь БД. Сама загрузка производится с помощью BULK вставки.
db.collect(‘select top 100 * from table’) db.execute(‘insert into table select * from another_table’) db. execute_many (‘insert into from table (id, name, age) values (. )’, [1,’Jhon’, 25])
Далее пользователь может задать параметры запроса с помощью метода add_var класса SqlContext. Данный метод принимает 4 параметра: наименование колонки, значения данной переменной, условие (=, =, between и т.п.) и разделитель (под разделителем понимаются команды AND и OR).
context = SqlContext() context.add_var(‘col_name’, [1,2,3,4,5], separator=’AND’, condition=’=’) context.add_var(‘col_name_1’, [[‘a’,’b’,’v’], [‘a1’,’b2’,’v3’],] , separator=’AND’, condition=’in’)
В случае определения нескольких параметров одновременно, в запросе они будут варьироваться по следующему правилу: сначала варьируются те параметры, которые были заданы в последнюю очередь. Если все вариации последнего параметра будут пройдены, то берутся следующее значения параметра выше и вновь перебираются все вариации последнего параметра. Так продолжается до тех пор, пока не переберутся все возможные комбинации заданных параметров.
После того, как мы определили варьируемые параметры необходимо задать сам sql запрос. Для этого создаем объект SqlBuilder и вызываем метод custom_sql внутрь которого помещаем сам запрос:
builder = SqlBuilder() builder.custom_sql(»’ INSERT INTO insertable_table SELECT * FROM table WHERE 1=1 AND col1 in (1, 2,10,98,34) AND col2 = 9 AND col3 between ‘20200101’ and ‘20200201’ »’)
или можно воспользоваться встроенными в объект методами для генерации sql (select, insert_into, create_table и т.д.):
Для запуска скрипта необходимо создать объект класса SqlGenerator, объекты SqlBuilder и SqlContext и с помощью цикла запустить обработку запроса (в качестве примера был взят вариант последовательного исполнения запроса):
generator = SqlGenerator(builder, context) for sql in tqdm(generator.generate()): t = time.time() db.execute(sql) print(‘Итоговое время работы запроса: ‘ + str(time.time()-t))
В итоге данный скрипт позволяет значительно сократить трудозатраты и время на выполнение рутинных запросов, чем я неоднократно пользовался в своей работе.
Весь исходный код опубликован на github.