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...)
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
name: Childebert I
$ ddlgenerator --inserts sqlalchemy merovingians.yaml
from sqlalchemy import create_engine, Column, Integer, Table, Unicode
As part of my series on the keys to and likelihood of success, I outlined some examples from the DBMS industry. The list turned out too long for a single post, so I split it up by millennia. The part on 20th Century DBMS success and failure went up Friday; in this one I’ll cover more recent events, organized in line with the original overview post. Categories addressed will include analytic RDBMS (including data (more...)
Fre MySQL seminar on 27. august 2014 @ 13:00. Announcement by Oracle User Group Estonia:
Developing modern applications using MySQL.
In this seminar series learn how to best use MySQL for your existing and new development requirements with leading MySQL expert and Oracle Ace Director Ronald Bradford.
These presentations provide a detailed review of the essential lifecycle components for developing a successful software application and offer a checklist for your company to review the (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:
Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build ans test procedures. As usual, you can download source and binary packages at MySQL Labs. Binary packages (more…)
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...)
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...)
When Steven and I wrote MySQL Stored Procedure programming our biggest reservation about the new stored procedure language was the lack of support for proper error handling. The lack of the SIGNAL and RESIGNAL clauses prevented a programmer from raising an error that could be propagated throughout a call (more...)
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
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 […]
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
Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).
After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle (more...)
As every year the DOAG (German Oracle User Group) Conference will take place Nürnberg/Germany from the 20th November until 22nd November 2012.
The Call for Papers is already open and ends on 30th June 2012:
German Link: http://www.doag.org/de/events/konferenzen/doag-2012/fuer-referenten.html
English Link: http://www.doag.org/en/events/konferenzen/doag-2012/fuer-referenten.html
Once again, I (more...)
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...)
Interesting take on what managers are thinking: http://quickbase.intuit.com/blog/2012/01/09/10-things-your-boss-isnt-telling-you/
I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.
One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.
Requirements I had:
- Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances (more...)
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(*) |
| CHARACTER_SETS | CHARACTER_SET_NAME | (more...)