%sql: To Pandas and Back

A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.

The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus PERSIST pseudo-SQL command that simply calls to_sql with the open database connection:

%sql PERSIST mydataframe

The result is (more...)

auto-generate SQLAlchemy models

PyOhio gave my lightning talk on ddlgenerator a warm reception, and Brandon Lorenz got me thinking, and PyOhio sprints filled my with py-drenaline, and now ddlgenerator can inspect your data and spit out SQLAlchemy model definitions for you:

$ cat merovingians.yaml
name: Clovis I
from: 486
to: 511
name: Childebert I
from: 511
to: 558
$ ddlgenerator --inserts sqlalchemy merovingians.yaml

from sqlalchemy import create_engine, Column, Integer, Table, Unicode
engine (more...)


I've had it on github for a while, but I finally released ddlgenerator to PyPI.

I've been frustrated for years that there was no good open-source way to set up RDBMS tables from flat data files. Sure, you could import the data - after setting up the DDL by hand. ddlgenerator handles that; in fact, you can go from zero, setting up and populating a table in a single line. Nothing up my sleeve:

$ (more...)

Configuring ODBC to MySQL from Oracle

Sometimes people want to connect to MySQL from Oracle and copy table data between the databases.  You can do that with Oracle Hetrogenous Services via ODBC.  This post will show how to create an odbc connection to your MySQL database which is the first part of this.

For my example, (more...)

Configuring MySQL on EL5, Setting Passwords and Network Access

I find myself installing and running mysql of different versions in different places for different reasons all the time (well often enough to do it and not remember the little things that cost time when setting up)   Its with that in mind, I'm making notes for myself and you (more...)

ipython-sql for multi-database comparisons

For my newest ipython-sql trick, I needed to compare some queries run across different databases. How hard would it be to get side-by-side results into tidy IPython Notebook output?

Not hard at all, it turns out, if you're willing to violate basic principles of human decency.

That's an itty-bitty image, (more...)

When to consider sharding a MySQL database

| Mar 13, 2013

When to consider sharding a MySQL database


Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is required to maintain acceptable performance. So when is the right time to shard your database?


When to consider sharding a MySQL database   Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is […]


Reclaiming Space from Deleted Big Tables from MySQL

So, in my earlier post, I mentioned about a need of dynamically resizing (increasing) EBS volume on EC2. Here is how I landed in the situation. In the prototype, my database grew very high and I could not reclaim the innodb space of mysql even after dropping large tables or (more...)

Separate docs for MySQL Connectors

The MySQL documentation section has always had this Topic Guides page containing links to the docs for the various MySQL Connectors -- the official database drivers for various languages and programming technologies. That is the most convenient way to get the information for each Connector in PDF form, rather than downloading the entire Ref Man PDF. For HTML, it was more of a shortcut, because

Book Review – High Performance MySQL 3rd Edition

Add to Technorati Favorites Ver este articulo en Español

This is THE MySQL performance book. Period!

Every chapter is very well crafted, with a precise balance between theory and practice, and full of invaluable nuggets, sometimes transcending the MySQL arena and applicable to any database! Such cases are Chapter 2-Benchmarking MySQL and Chapter 3-Profiling Server Performance, very solid foundations for the reading ahead.

All over the text, authors propose tools, examples of use and proven diagnostic techniques, that will greatly improve your performance firefighter skills and enhance your knowledge of MySQL internals. Nevertheless, what I liked the most from this book is taking into (more...)

For People That Have Managers

Interesting take on what managers are thinking: http://quickbase.intuit.com/blog/2012/01/09/10-things-your-boss-isnt-telling-you/

MySQL Group By is a little too indulgent

After 30 years of Oracle, I've found myself using MySQL recently. I came across a little thing that surprised me. I'm by no means the first to trip over this - I found this 2006 post from Peter Zaitsey on the same topic.

MySQL lets you write a group by statement that references columns that aren't in the group by, and aren't aggregates. For example:

mysql> select table_name, column_name, count(*)
-> from information_schema.columns
-> where table_schema = 'information_schema'
-> group by table_name
-> limit 5;
| table_name | column_name | count(*) |

Oracle Database on Amazon RDS

Recently, Amazon and Oracle announced that they are going to make "Oracle 11g Database" available on Amazon AWS as a service. It's being brought into AWS's RDS feature which currently offers "MySQL database as a service".

Amazon RDS is a web service that allows you to set up, operate, and scale a relational database in the cloud. You can provision a relational database (currently only MySQL) on RDS in just a few minutes. Amazon RDS will also manage database administration tasks including continuous backups, software patching etc.
When launched (sometime in Q2 of 2011), you will have the option to (more...)

Finding the Best Match With a Top-N Query

There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g., LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.

I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement (more...)

InnoDB Plugin Doc

The InnoDB Plugin manual is now available on the MySQL web site.

Ten Years Gone

I've been pretty quiet lately, because I'm in a transitional period. After 10 years on documentation for Oracle Database and other enterprise server products, I'm switching to the InnoDB group that already works with MySQL. New development environments, new customers, it's an exciting time!A decade seems to be the right timeframe for me. It was 10 years at IBM before that. Check back in 2019, I'm

EU will force MySQL out of Oracle/Sun merger

In Europe, many people love Open Source with its connotations of sharing and village co-operatives. On the other hand, many Europeans don’t like the big, successful American companies (witness the ritual McDonalds-bashing).

This emotional preference for Open Source to big American companies is why the EU competition authorities have decided (more...)