Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 249 Next 30 Older Entries

Displaying posts with tag: Python (reset)

Review: MySQL for Python by Albert Lukaszewski
+1 Vote Up -0Vote Down

Packt Publishing recently sent me a copy of MySQL for Python to review and after reading through the book I must say that I’m rather impressed at the variety of topics that the book covers.

It starts off with the basics of setting up MySQL for your testing/development needs by going over several of the common installation and configuration methods. After that it’s a quick intro for connection methods and simple error reporting for connections. The author gives a quick intro to CRUD and how it relates to databases and python before heading into the common tasks of simple queries. I was surprised to see some database profiling discussion; which is rather handy for a new coder or a

  [Read more...]
Simple Python: a job queue with threading
+0 Vote Up -0Vote Down

Every so often you need to use a queue to manage operations in an application. Python makes this very simple. Python also, as I’ve written about before, makes threading very easy to work with. So in this quick program I’ll describe via comments, how to make a simple queue where each job is processed by a thread. Integrating this code to read jobs from a mysql database would be trivial as well; simply replace the “jobs = [..." code with a database call to a row select query.

#!/usr/bin/env python
## DATE: 2011-01-20
## FILE: queue.py
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
from Queue import *
from threading import Thread, Lock

'''this function will process the items in the queue, in serial'''
def processor():
    if queue.empty() == True:
        print "the Queue is empty!"
        sys.exit(1)
    try:
        job = queue.get()
        print "I'm
  [Read more...]
MySQL Connector/Python v0.3.2-devel released
Employee_Team +1 Vote Up -0Vote Down

MySQL Connector/Python 0.3.2, a development release, is available for download:
https://launchpad.net/myconnpy/+download

Disclaimer: Since version 0.3 is still a development release, or ‘alpha’, it is not
recommended to run this in production.

MySQL Connector/Python 0.3.2-devel is a maintenance release fixing following bugs:

  • lp:701081 -Doesn’t install with Python 2.4

About MySQL Connector/Python: MySQL Connector/Python is implementing the
MySQL Client/Server protocol completely in Python. No MySQL libraries
are needed, and no compilation is necessary to run this Python DB API v2.0





  [Read more...]
MySQL Connector/Python v0.3.1-devel released
Employee_Team +3 Vote Up -0Vote Down

MySQL Connector/Python 0.3.1, a development release, is available for download:
https://launchpad.net/myconnpy/+download

Disclaimer: Since version 0.3.1 is still a development release, or ‘alpha’, it is not
recommended to run this in production.

MySQL Connector/Python 0.3.1-devel is a maintenance release fixing following bugs:

  • lp:695514 – Infinite recursion when setting connection client_flags
  • lp:691836 – Incorrect substitution by cursor.execute when tuple args contains ‘%s’

About MySQL Connector/Python: MySQL



  [Read more...]
Setting client flags with MySQL Connector/Python
Employee_Team +3 Vote Up -0Vote Down

Setting client flags with MySQL Connector/Python works a bit differently than the other MySQL Python drivers. This blog post describes how to set and unset flags, like the CLIENT_FOUND_ROWS.

The default client flags for the MySQL Client/Server protocol can be retrieved using the constants.ClientFlag class:

>>> from mysql.connector.constants import ClientFlag
>>> defaults = ClientFlag.get_default()
>>> print ClientFlag.get_bit_info(defaults)
['SECURE_CONNECTION', 'TRANSACTIONS', 'CONNECT_WITH_DB',
 'PROTOCOL_41', 'LONG_FLAG', 'MULTI_RESULTS',
 'MULTI_STATEMENTS', 'LONG_PASSWD']

To set an extra flag when connecting to MySQL you use the client_flags argument of connect()-method. For example, you’d

  [Read more...]
MySQL v5.5 and Python
Employee_Team +3 Vote Up -0Vote Down

MySQL (http://www.mysql.com) v5.5 is GA, but is it working with Python? Yes, it does. Below you’ll find some quick, small tests I did with MySQLdb, oursql and our own MySQL Connector/Python.

My desktop is a Mac, but when it works on that, I’m sure it works elsewhere too. If not, just let us know!

MySQL for Python (aka MySQLdb)

Installing MySQL v5.5.8 64-bit from tar ball on MacOS X 10.6, it compiled fine and the module loaded giving me the expected result:

>>> import MySQLdb
>>> cnx =
  [Read more...]
oak-hook-general-log: your poor man’s Query Analyzer
+0 Vote Up -0Vote Down

The latest release of openark kit introduces oak-hook-general-log, a handy tool which allows for some analysis of executing queries.

Initially I just intended for the tool to be able to dump the general log to standard output, from any machine capable to connect to MySQL. Quick enough, I realized the power it brings.

With this tool, one can dump to standard output all queries using temporary tables; or using a specific index; or doing a full index scan; or just follow up on connections; or… For example, the following execution will only log queries which make for filesort:

oak-hook-general-log --user=root --host=localhost --password=123456
  [Read more...]
openark-kit (rev. 170): new tools, new functionality
+3 Vote Up -0Vote Down

I’m pleased to announce a new release of the openark kit. There’s a lot of new functionality inside; following is a brief overview.

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand.

It’s been a while since the last announced release. Most of my attention was on mycheckpoint, building new features, writing documentation etc. However my own use of openark kit has only increased in the past few months, and there’s new useful solutions to common problems that have been developed.

I’ve used and improved many tools over this time, but doing the final cut, along with proper documentation, took

  [Read more...]
MySQL Connector/Python 0.3.0 has been released!
Employee_Team +5 Vote Up -0Vote Down

MySQL Connector/Python 0.3.0, a development release, is available for download:
https://launchpad.net/myconnpy/+download

Since version 0.3.0 is still a development release, or ‘alpha’, it is not
recommended to run this in production.

MySQL Connector/Python 0.3.0 adds following features:

  • Python v2.4 support is back.
  • Support for compressed protocol.
  • Support for SSL connections (when Python’s ssl module is available).
  • Support for packets which are bigger than 16MB.
  • Max allowed packetsize defaults to 1GB.
  • Some performance improvements.

See the ChangeLog for extra details.

Please report bugs and comments



  [Read more...]
Query caching with MySQL Connector/Python
Employee_Team +2 Vote Up -0Vote Down

This blog post shows how to create a cursor class for MySQL Connector/Python which will allow you to cache queries. It will hold the query itself and the result in a global variable.

Note: this is a proof of concept and is only meant as a demonstration on how to extend MySQL Connector/Python.

Why query caching?

You are doing lots of queries that have the same result. It would be expensive to always run the same exact query. MySQL has already a query cache, and there is also memcached. But you like MySQL Connector/Python so much you’d like to do it yourself.

A cursor caching queries and their result

To demonstrate a simple implementation of a query cache, we inherit from an existing

  [Read more...]
Buffering results with MySQL Connector/Python
Employee_Team +1 Vote Up -0Vote Down

MySQL Connector/Python doesn’t buffer results by default. This means you have to fetch the rows when you issued a SELECT. This post describes how you can change this behavior.

Why buffering result sets?

Buffering or storing the result set on the client side is handy when you, for example, would like to use multiple cursors per connection and you’de like to traverse each one interleaved.

Keep in mind that with bigger result sets, the client side will use more memory. You just need to find out for yourself what’s best. When you know result sets are mostly small, you might opt to buffer.

MySQLdb by default buffers results and you need to use a different cursor to disable it. oursql does not buffer by default. This is good to know

  [Read more...]
mycheckpoint (rev 208): aggregation tables, enhanced charting, RPM distribution
+1 Vote Up -0Vote Down

Revision 208 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

  • Aggregation tables: aggregated data makes for fast reports on previously slow queries.
  • Enhanced charting: interactive charts now present time stamps dynamically (see demo); “Zoom in” charts are available (see demo) on mycheckpoint‘s HTTP server.
  • RPM distribution: a “noarch” RPM
  [Read more...]
A simple load test script in Python
+1 Vote Up -0Vote Down

Lately I’ve had to do some environment load testing so I wrote this quick script. It can be modified as needed but the basic idea is that it spawns $x threads (–threads) and then sends two connections (or however many you want with –per-connection=) per thread to the URL (–url=). You can have it wait a configurable time between connections as well (–wait=).

The url is appended with a 32 character randomized string so that any database/caching on the backend of the site isn’t serving data from a warm cache. You can hunt down the string length for 32 and change it to whatever you want. Feel free to change and use as needed, just keep my info at top.

#!/usr/bin/python
################################################################################
## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE:
  [Read more...]
Fetching rows as dictionaries with MySQL Connector/Python
Employee_Team +4 Vote Up -1Vote Down

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))
pprint(result)
cur.close()
cnx.close()
[python]

The above results in an output like this:

[python light="true"]
[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
  [Read more...]
MySQL Connector/Python 0.2-devel available
Employee_Team +5 Vote Up -1Vote Down

Next development release v0.2.0 of MySQL Connector/Python is available for download and testing. We still don’t recommend to use it in production: it is not beta or GA yet, but we are getting there.

Bug reports and feature requests are welcome through the Launchpad bug tracking tool.

Highlights:

  • .executemany() now optimizes INSERT statements using the MySQL
    multiple row syntax.
  • Setting sql_mode and time_zone when connecting as well as collation.
  • Raw Cursors can be used when you want to do the conversion yourself.
  • Unittests now bootstrap own MySQL server instance.
  • Tidying the source tree.

Full list of changes and bug fixes can be found


  [Read more...]
Thoughts and ideas for Online Schema Change
+6 Vote Up -0Vote Down

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

“The original table must have PK. Otherwise an error is returned.”

  [Read more...]
MySQL Workbench Plugin: mforms example and slow query log statistics
Employee +2 Vote Up -0Vote Down

As an update to my prior post, I’ve added a form to the workbench plugin.  Now, the user can select a slow query log file and generate statistics from it.  The plugin provides information to answer the following questions:

Figure 1. Sample plugin form

  • What type of queries run most often?
  • What type of queries are the slowest?
  • Which queries access the most rows?
  • Which queries send the most data?

The plugin scans the slow query log, aggregates

  [Read more...]
dbbenchmark.com – default Thread quantity now self determined
+0 Vote Up -0Vote Down

There’s a new version of the dbbenchmark tool available. Now we’re creating the MySQL connection pool thread count based on automatic reporting of core quantity. There is still the same method to set the thread count manually if you are interested in finding your system’s thread limits. Download the MySQL benchmarking script now and add your server performance to the community database of results!

Having fun with MySQL and Python: converting MySQL character set to utf8
+0 Vote Up -0Vote Down

Lately I worked quite a bit with Python and Linux, writing monitoring and automation utilities. I am in a transition period, so I thought I ought to write some Python stuff interfacing with MySQL for fun, and start positioning myself for expanded career horizon, I hope.

To get started, I thought it would be fun to rewrite a Perl utility I wrote before with Python. That script converts MySQL character sets to utf8, a very common task for wikis and blogs during an upgrade. This time, I did everything from scratch: firing up an Amazon EC2 Linux instance, hand install and configuring MySQL 5.1.50 (creating mysql user, group, wget tarball, setting directory ownership and permissions, creating symbolic to MySQL binaries, editing my.cnf,

  [Read more...]
MySQL Workbench Plugin: Slow Query Log Statistics
Employee +4 Vote Up -0Vote Down

This is my first attempt at creating a plugin for MySQL Workbench.  As a first step, I’ve created a plugin that summarizes the slow query log if it’s output to the slow_log table, which is an option available in MySQL version 5.1 or newer.  It’s similar to the mysqldumpslow perl script, except that it doesn’t require perl, which should be more convenient on Windows.  In my next update, the plugin will provide the same summary statistics for the slow query log file on disk.

While the slow query log reports query time, lock time, rows sent and rows examined for each query; it’s often useful to group and aggregate similar queries for analysis.  For example, here’s a sample of the plugin output, which is sorted by count, after just a few clicks on a drupal6 site:

  [Read more...]
Open source in the clouds and in the debates
+0 Vote Up -1Vote Down

We continue to see more evidence of the themes we discuss in our latest CAOS special report, Seeding the Clouds, which examines the open source software used in cloud computing, the vendors backing open source, the cloud providers using it and the impact on the industry.

First, as usual, we are seeing consistencies between our own research — which indicates open source is a huge part of today’s cloud computing offerings from major providers like Amazon, Google, Rackspace, Terremark and VMware — and that of code analysis and management vendor Black Duck. In its analysis of code that runs the cloud, Black Duck also found a preponderance of open source pieces, in many cases the same projects we profile in our report.

Indeed, open source software is an

  [Read more...]
dbbenchmark.com – MySQL (basic) connection pool support added
+1 Vote Up -0Vote Down

In this latest release I’ve added a basic MySQL connection pool to the benchmarking script which improves the method in which connections to MySQL are handled and reused. In addition, there have been some optimizations made to the thread handler functions for better debug reporting. Download the latest release now and see how your MySQL server performs against the rest of the community! Download here: download page.

mycheckpoint (rev. 190): HTTP server; interactive charts
+2 Vote Up -0Vote Down

Revision 190 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

  • HTTP server: mycheckpoint can now act as a web server. Point your browser and start browsing through HTML reports. See mock up demo.
  • Interactive charts: HTML line charts are now interactive, presenting with accurate data as you move over them. See sample.
  • Enhanced auto-deploy: now auto-recognizing failed upgrades.
  [Read more...]
dbbenchmark.com – configuring OpenBSD for MySQL benchmarking
+1 Vote Up -0Vote Down

Here are some quick commands for installing the proper packages and requirements for the MySQL dbbenchmark program.

export PKG_PATH="ftp://openbsd.mirrors.tds.net/pub/OpenBSD/4.7/packages/amd64/"
pkg_add -i -v wget
wget http://dbbenchmark.googlecode.com/files/dbbenchmark-version-0.1.beta_rev26.tar.gz
pkg_add -i -v python
Ambiguous: choose package for python
 a       0: 
         1: python-2.4.6p2
         2: python-2.5.4p3
         3: python-2.6.3p1
Your choice: 2

pkg_add -i -v py-mysql
pkg_add -i -v mysql
pkg_add -i -v mysql-server
ln -s /usr/local/bin/python2.5 /usr/bin/python
gzip -d dbbenchmark-version-0.1.beta_rev26.tar.gz
tar -xvf dbbenchmark-version-0.1.beta_rev26.tar
cd dbbenchmark-version-0.1.beta_rev26
./dbbenchmark.py --print-sql
 - login to mysql and execute sql commands
./dbbenchmark.py
dbbenchmark.com – Debian Lenny, MEMORY_ACTIVE bug fix
+1 Vote Up -3Vote Down

Quick solution to an issue that the affected Debian Lenny release: the process used to collect the MEMORY_ACTIVE_BYTES variable has been modified to correct a situation where some systems report an array of memory information instead of the expected single integer value. The bug has been fixed in revision 21 and the current download (revision 22) is available for download or svn update. As usual, you can download the MySQL dbbenchmark script here: [downloads].

Thanks goes to Brian Vowell at Evernote.com for bringing this bug to my attention. The original bug report can be found here: [link]

Easy Python: multi-threading MySQL queries
+2 Vote Up -3Vote Down

There are many times when writing an application that single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions. In this simple example for Python multi-threading you’ll see the how simple it is to improve the performance of your python app.

#!/usr/bin/python
## DATE: 2010-08-30
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
## Copyright 2010-present Matt Reid

from __future__ import division
from socket import gethostname;
import threading
import sys
import os
import MySQLdb

class threader(threading.Thread):
    def __init__(self,method):
        threading.Thread.__init__(self)
        self.tx =
        self.method = method
    def run(self):
  [Read more...]
dbbenchmark.com – MySQL benchmarking now supports multiple threads!
+1 Vote Up -3Vote Down

We had a very successful weekend of Planet.mysql users submitting their database statistics so I’ve pushed some code into a new release today so that everyone can benefit from some new features. The biggest change is to the threading logic. Previously the benchmarking script was serializing MySQL operations and only making use of a secondary thread (not the invoking thread) to query the database. Now you have the option of running with “–threads=x” to make use of your multi-core server. A good example of this improvement was on my Macbook Pro; before the threading change it was inserting ~700/sec, after the code change I tried –threads=4 and saw an improvement to ~900/sec. Rather significant.

Download the new script now and see how your server compares to the ones in the central database!

MySQL Connector/Python and database pooling
Employee_Team +8 Vote Up -0Vote Down

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils' PooledDB module to implement database connection pooling.

Here below you'll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')
    
    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT
  [Read more...]
MySQL Connector/Python and database pooling
Employee_Team +0 Vote Up -0Vote Down

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils’ PooledDB module to implement database connection pooling.

Here below you’ll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')

    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx
  [Read more...]
Installing MySQLdb python module
+1 Vote Up -1Vote Down
MySQLdb is a Python wrapper around _mysql written by Andy Dustman. This wrapper makes it possible to interact with a MySQL Server performing all sorts of DDL and DML statements. I began my Python journey recently and stumbled at the installation of the MySQLdb module install. I was keen not to jump at an apt/yum installation as we have servers that have no outbound connections I decided I wanted to build the module from source.

You can download the MySQLdb files from SourceForge (70kb)

When downloaded you need to prep before your system is ready to build the file. Here are some prerequisites that will make life easier for you. I performed this particular install using an Ubuntu 10.04 64bit OS.

Before you start ensure you have the following installed (MySQL isn't actually





  [Read more...]
Previous 30 Newer Entries Showing entries 91 to 120 of 249 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.