Accessing a MySQL database in Python



  • So I'm working on my first nontrivial (i.e. not based on applying silly transformations to some data and writing it to stdout) Python project, involving a web server with a MySQL backend.

    The server is written with the Tornado framework. Reading the Tornado documentation, the solution seems self-evident: access the database through the handy-dandy tornado.database wrapper library. Sounds simple enough, right?

    But that doesn't work. import tornado.database generates an error about the abscence of a module named MySQLdb.constants. Turns out Python doesn't have a built-in database connector for MySQL, so tornado.database relies on a library called MySQLdb for this. OK, fair enough. I just have to download this library and add it to the PYTHONPATH environment variable, like I've been doing for other libraries, right?

    Wrong. Trying import tornado.database now produces an error about a missing module named _mysql. Googling the error message reveals that MySQLdb relies on a native C library for interfacing with MySQL.

    The MySQLdb developer's website scolds me for not reading the README file before trying to install the library. I'm told to go follow the install instructions there. First, I'm supposed to download something called setuptools. Apparently it's some kind of system for installing Python modules, I don't know. From reading the install instructions of other Python libraries, there seem to be about 37 different commonly-used installation systems for Python. Simple, right?

    Then I get to the next step. Here's where the real fun begins. MySQLdb doesn't include any binary builds of the native libraries it relies upon - you have to compile them yourself from source. I don't care what platform you're on, past experience has taught me that "compile this from source" is basically a euphemism for "enjoy spending the next 3+ hours of your life banging your head on the wall". But if you're on Windows, like I am, it changes to "mercury will crystallize in Hell before you get this working".

    The readme explains how the developer doesn't use Windows, but how other people have managed to get the library working in the past. Wonderful. I'm supposed to set an environment variable to tell the installer where my MySQL installation is, because the developer can't be bothered to check the registry. It even says as much.

    Why does it need a MySQL installation anyway? Because it needs the header files from the development utilities for MySQL, which I didn't install. So I need to re-run the installer for MySQL with that extra little checkbox ticked. Alright, fine.

    The readme then suggests I try building MySQLdb's C library with Cygwin. I don't have Cygwin on this machine, so I need to run off and download and install it. 15 minutes later, I try running python setup.py install, which proceeds to inform me:

    Traceback (most recent call last):
    File "setup.py", line 15, in <module>
    metadata, options = get_config()
    File "C:\Program Files (x86)\Python\MySQLdb\setup_windows.py", line 7, in get_
    config
    serverKey = _winreg.OpenKey(_winreg.HKEY_LOCAL_MACHINE, options['registry_ke
    y'])
    WindowsError: [Error 2] The system cannot find the file specified
    </module>

    Gee, thanks.

    OK, more Googling, I get this page telling me I'm supposed to edit the file site.cfg and change the MySQL version number to match my installation's version number. How the hell was I supposed to know that?!

    So I change that, and now I get another error: Unable to find vcvarsall.bat. At this point my patience is rapidly running out. Google, yada yada yada, basically this error means my compiler is screwed in the head and I need to use a different one. That means running off and downloading and installing MinGW.

    Oh, but that's not the end of it. The MinGW installer vomits up an error message of the form "unable to execute file mingw-get.exe". Google Google Google, you know the drill. OK, apparently there's a race condition in the MinGW installer, related to some kind of defect in the Windows API, that makes it so I can't install MinGW on Windows 7 64-bit.

    So basically, at the current point in time, in order to access a database I need to use a Python library that needs another Python library that needs a C library that needs to be compiled using MinGW, which I can't install because of a bug in Windows. Since, as we all know, Python is so easy-to-use and is really improving my productivity by orders of magnitude over, say, Java, where I can access a MySQL database by dropping a library on the classpath.

    I'm going to go back to banging my head on a wall now.



  • It's not surprising that MYSQLdb relies on the MySQL client library - pretty much all MySQL drivers need it, with PHP being a somewhat recent exception with its native driver (mysqlnd). The fact that there are no prebuilt Windows binaries, however, is a pretty big WTF, though it's probably justified due to licensing restrictions or something.



  • I recently had the exact same problems installing MySQLdb for use with Django on Win7. I banged my head until I somehow stumbled on some precompiled binaries for Windows, and now everything is hunky-dory (no sarcasm here).

    I can try and dig up what I downloaded and send it to you, if you want.



  • Is this it? http://sourceforge.net/projects/mysql-python/files/
    The .exe installers in there usually work great for me in Windows.



  • TRWTF is MYSQL doesn't provide a consistent, well-documented, wire-level interface so someone could implement a native driver. You HAVE to use their WTF-ish C client library.

    The next WTF you are goint to run into is that the C libabry is fully blocking and if you want to use it from an async framework you HAVE to use os-level threads [which is what i guess tornado.database does] wich due to how python GIL works will screw up your server's responsiveness when it's waiting for lots of queries to complete. Note again that this problem wouldn't exist if you could use a native driver.

    Oh and if you plan seriously using python under windows look at ActivePython.. their distribution includes some win32 modules that aren't in the official python.org distro and they have a repository with many C modules already compiled that you can easily install with pypm.



  • @arotenbe said:

    Oh, but that's not the end of it. The MinGW installer vomits up an error message of the form "unable to execute file mingw-get.exe". Google Google Google, you know the drill. OK, apparently there's a race condition in the MinGW installer, related to some kind of defect in the Windows API, that makes it so I can't install MinGW on Windows 7 64-bit.

    I'll bet dollars to donuts that that bug has nothing to do with a bug in the Windows API.



  • TRWTF is MySQL.  Why are you working on a non-trivial project with a toy DB?  Try Firebird; it will make your life so much easier.  (If there's a Python driver for it, at least. Not sure about that.)



  • @smmoonshoes said:

    Is this it? http://sourceforge.net/projects/mysql-python/files/
    The .exe installers in there usually work great for me in Windows.

    Is there one for Python 2.7, or should I just give up and use 2.5? For that matter, why are there different versions for each Python version, anyway?

    @blakeyrat said:

    I'll bet dollars to donuts that that bug has nothing to do with a bug in the Windows API.

    Yeah, me too, but it made a better rant blaming it on Microsoft.



  • @arotenbe said:

    @blakeyrat said:
    I'll bet dollars to donuts that that bug has nothing to do with a bug in the Windows API.

    Yeah, me too, but it made a better rant blaming it on Microsoft.

    It was a stupid statement anyway; donuts cost a dollar or more in most places. Stupid bet to make!

    I dunno if you saw my tag, but did you think about trying an ODBC connector?



  • @arotenbe said:

    in order to access a database I need to use a Python library that needs another Python library that needs a C library that needs to be compiled using MinGW, which I can't install because of a bug in Windows.

    I've seen similar fun in Perl (where occasionally you need a module that needs another module that only works on *IX), and recently I tried to install Magento (eCommerce framework) which also got me running in circles for some time... It's bloody annoying. Some people love this, apparently, or will tell you this is what is so powerful because you get to choose so many options, and besides, it's all Bill's fault anyway, but for me I'd rather just get some work done.

    These days I have an Ubuntu/VMWare image handy so I can try these things on Linux. Chances are it works a bit better there.Then again, chances are you'll get 17 new headaches, so only try this if/when you're desperate.



  • Well, it is a "race condition" as in the installer trying to execute a binary that is opened in exclusive mode (probably for writing) by some other subprocess/thread. The sane options would be to make that writer signal that it's done... or if the alleged problem with WinAPI is that this signal arrives too early (out-ot-ass: caused by antivirus intercepting writes?), just try it a few times with a 2sec sleep between until one of these executes correctly.



  • To add another WTF to the list, MySQLdb doesn't support real parameterization (at least last I checked)...

    This doesn't help you with tornado's wrapper, but you may take a look at http://packages.python.org/oursql/  in the future..



  • @toothrot said:

    This doesn't help you with tornado's wrapper, but you may take a look at http://packages.python.org/oursql/  in the future..

    Actually, I was just going to use Tornado's database module because it seemed convienient. Everything I read said that MySQLdb was the "standard" MySQL connector for Python. There's no restriction to using tornado.database instead of any other library; it's totally separate from the rest of the framework. So... I guess I'll try oursql instead.



  • @arotenbe said:

    Oh, but that's not the end of it. The MinGW installer vomits up an error message of the form "unable to execute file mingw-get.exe". Google Google Google, you know the drill. OK, apparently there's a race condition in the MinGW installer, related to some kind of defect in the Windows API, that makes it so I can't install MinGW on Windows 7 64-bit.
     

    The link you provided says nothing about a race condition or "defect" in Windows.  It says" I was able to use mingw-get to complete the installation from the command line, and went on to successfully build Coin-OR software, so the install seems to be correct. Looks like just a bug in the installer." 



  • @El_Heffe said:

    @arotenbe said:

    Oh, but that's not the end of it. The MinGW installer vomits up an error message of the form "unable to execute file mingw-get.exe". Google Google Google, you know the drill. OK, apparently there's a race condition in the MinGW installer, related to some kind of defect in the Windows API, that makes it so I can't install MinGW on Windows 7 64-bit.
     

    The link you provided says nothing about a race condition or "defect" in Windows.  It says" I was able to use mingw-get to complete the installation from the command line, and went on to successfully build Coin-OR software, so the install seems to be correct. Looks like just a bug in the installer." 

    Read the comment made at 2010-10-09 21:37:26 UTC.


  •  TRWTF is managers deciding to use free software because it is "free".

    "Oh, really? You saved $300 by not paying for X database and using Z dataabase, but because of that your programmer (who costs you $40/hour) spent twenty hours (as well as 5 GB of internet downloads)? Well I guess that saved money,"

     

    B



  • @havokk said:

     TRWTF is managers deciding to use free software because it is "free".

    "Oh, really? You saved $300 by not paying for X database and using Z dataabase, but because of that your programmer (who costs you $40/hour) spent twenty hours (as well as 5 GB of internet downloads)? Well I guess that saved money,"

     

    B

     Why would 5 GB of internet downloads matter?



  • @bdew said:

    ... implement a native driver.
    There is one, but no one cares.


  • Garbage Person

    @DescentJS said:

     Why would 5 GB of internet downloads matter?
    Australia and New Zealand are fucking NOTORIOUS for teeny tiny caps and HUGE per-megabyte fees beyond them. Megabyte for megabyte, I'd prefer using whatever passes for Internet in the middle of subsaharan Africa than what passes for Internet in Australia.

     

    Any time some asshat says "Just use MySQL on that Windows server, it's free!" I point out that SQL Server Express is also free and that I'm leaving if they insist on MySQL.

    I still haven't come up with a Linux database solution that I'm comfortable with. MySQL has most of the requisite features now, but the APIs all suck dicks. Also, now that Toad is the admin suite of choice, I fucking hate the admin tools (Okay, I hated them before, but at least they made a modicum of sense). PostgreSQL is quite featureful but has the shittiest admin toolset I've ever encountered. SQLite is... A fragile little toy and lacks every feature on the god damned list. Firebird scares me because the documentation includes the word 'Delphi' - not once, but OFTEN, in addition to being hilariously underdocumented to begin with. One day I'll get off my ass and try it. My generalized solution to accessing a database from Linux at the present time is "So where's your Windows box with the database on it?"

     

     

     



  • This is why I don't like using "refreshingly simple" tools: once everything goes to shit you're dead in the water.



  • @TehFreek said:

    There is one, but no one cares.

    Could you give a link? my google foo seems to be weak today.

    I do remember someone writing a driver for one of the async frameworks, but it was so tightly coupled to their custom network stuff that porting it over to gevent (which we are using) looked too hard to be worth it.

    Edit: And after posting that i found http://github.com/mthurlin/gevent-MySQL/tree/master/lib/geventmysql/ which looks like someone did just that :)

    @Weng said:

    Also, now that Toad is the admin suite of choice, I fucking hate the admin tools.

    Ughh... that's the first time i hear of it and it looks like a tool for brain dead windows admins. phpmyadmin and/or the cli tools are more than enough to manage mysql.

    @Weng said:

    PostgreSQL is quite featureful but has the shittiest admin toolset I've ever encountered.

    Same here... unless you are talking about some other tool for brain dead windows users?

    @Weng said:

    SQLite is... A fragile little toy and lacks every feature on the god damned list.

    It's not in the same product category as "real" RDBMS. It's something nice to use when you need to process and store locally lots of data in a desktop application and don't want to load it all to memory.

    @Weng said:

    My generalized solution to accessing a database from Linux at the present time is "So where's your Windows box with the database on it?"

    Ok, now TRWTF is you.



  • @Weng said:

    Megabyte for megabyte, I'd prefer using whatever passes for Internet in the middle of subsaharan Africa than what passes for Internet in Australia.

    I've been using the Internet in Australia for 12 years. When I visited Japan (one of the apparently fastest places in the world) I was disappointed in the speed. The grass is greener on the other side!

    I tried searching for some ISPs in some subsaharan countries. I started by searching for ISPs in Kenya. I found a list but almost all of the linked sites were broken, and the ones that worked didn't display prices. South Africa might have better plans then: I think I found their equivalent of Whirlpool, though it isn't as pretty or correct! According to this page the cheapest unlimited plan is R900 (~$133) with no mention of speed. Going to the actual ISP page suggests the "ISP directory" page is out of date: plans don't match but still not very good. (There are other "unlimited" plans there but they are unlimited quota for unlimited money: R80 (~$12) per GB over! Plus their sites appear to be broken)

    Several ISPs in Australia have reasonably priced unlimited ADSL2+ where many others have large quotas. Of course many of these plans aren't available to all people, but coverage is expanding all the time. The NBN will bring more speed to more people, and will eventually bring cheaper access. Hell every year or so I seem to get more quota for less money, with speed steadily increasing (though it is never fast enough). Even many 3G based wireless plans don't root you over any more.

    @Weng said:

    Australia and New Zealand are fucking NOTORIOUS for teeny tiny caps and HUGE per-megabyte fees beyond them

    I haven't been on an wired plan with an excess fee since 2007, and even then it was only $3/GB. Of course I have one on my 3G netbook, but I'm good with quota and mostly use wifi anyway. My current cap is 130GB download quota and I'm considering moving to one of the unlimited plans.

    But then reading the rest of the post you seem to just be negative about everything! (I have to sometimes use SSMS and find it clunky. phpMyAdmin feels easier for me to use, I guess because I'm more used to it.)



  • @arotenbe said:
    So basically, at the current point in time, in order to access a database I need to use a Python library that needs another Python library that needs a C library that needs to be compiled using MinGW, which I can't install because of a bug in Windows.
    Or you could save yourself the hassle and write a DLL that interfaces MySQL.


  • @Weng said:

    Firebird scares me because the documentation includes the word 'Delphi' - not once, but OFTEN

    Also, it has no boolean.



  • @Faxmachinen said:

    @arotenbe said:
    So basically, at the current point in time, in order to access a database I need to use a Python library that needs another Python library that needs a C library that needs to be compiled using MinGW, which I can't install because of a bug in Windows.

    Or you could save yourself the hassle and write a DLL that interfaces MySQL.

    That would save hassle?



  • @bdew said:

    @Weng said:
    Also, now that Toad is the
    admin suite of choice, I fucking hate the admin tools.

    Ughh... that's the first time i hear of it and it looks like a tool for brain dead windows admins. phpmyadmin and/or the cli tools are more than enough to manage mysql.

    Oh it's on.

    Toad isn't bad because it's a GUI. Toad is bad because it's a GUI written in Java.

    Also, I hate your attitude here: usability applies to everybody, even admins. There's nothing wrong with making admin tools more usable, it doesn't make the admin "brain dead" any more than me keeping phone numbers stored in my phone makes me "brain dead." (After all, I should have rote memorized them all, right?) I mean, yah, you're probably gaining some Slashdot-bux by saying that, but let's not insult people trying to improve the user experience.

    Unless they're doing it with Java. Which shows they don't really give a shit about usability, they're just ripping-off whatever Microsoft did 5 years ago. But ignore that last sentence.

    You can take my SQL Server Management Studio when you pry it from my cold dead fingers!

    Edit: Oh BTW, Toad? Significantly better than the awful management tools they had before. When:
    1) The tool doesn't use the shortcut Control-A for "Select All",
    2) The developers file the above bug as "won't fix",
    you know you're on to a "special" type of software. I'm not surprised they gave up and just use Toad now.

    @bdew said:

    @Weng said:
    My generalized solution to accessing a database from Linux
    at the present time is "So where's your Windows box with the database on
    it?"

    Ok, now TRWTF is you.

    I prefer to work with Microsoft databases too, exactly because the admin tools aren't shit. And they're equivalent to competitors in pretty much every other way. (Except cheaper when compared to DB2 or OracleDB. Which both have shitty admin tools.) And all the OLAP stuff is built-in, so you never have to worry about buying another super-expensive add-on just to do OLAP.



  •  Thanks for the heads-up on oursql, I don't really like the mySQLdb module a lot either. And I am on Linux.

    But the OP's rant is what what happens more often than not with FOSS, it's free but a pain to use. You could also shell out money for some MS SQL based solution. On Windows that will probably work better.

     If you want to use MySQL and Python, then searching for a precompiled MySQL binary for your platform is probably a lot easier. It's what I usually do when I see "You have to compile this yourself from source". No matter if I'm on Windows, Linux or OSX, often someone else solved the problem and posted the binary online somewhere. Sometimes even in a convenient MSI, RPM, or DMG.

     I'm currently mostly struggling with the fact that MySQLdb isn't thread safe, probably because the underlying C-code isn't as well.



  • @rad131304 said:

    @Weng said:

    Firebird scares me because the documentation includes the word 'Delphi' - not once, but OFTEN

    Also, it has no boolean.

    Well, as your link points out, it's very easy to add a boolean type as a domain.  And what's scary about Delphi being mentioned in the documentation?



  • @Mason Wheeler said:

    @rad131304 said:

    @Weng said:

    Firebird scares me because the documentation includes the word 'Delphi' - not once, but OFTEN

    Also, it has no boolean.

    Well, as your link points out, it's very easy to add a boolean type as a domain.  And what's scary about Delphi being mentioned in the documentation?

    Don't forget that Firebird is an opensourced fork of old-ish version of Borland Interbase.


  • @Mason Wheeler said:

    @rad131304 said:

    @Weng said:

    Firebird scares me because the documentation includes the word 'Delphi' - not once, but OFTEN

    Also, it has no boolean.

    Well, as your link points out, it's very easy to add a boolean type as a domain.  And what's scary about Delphi being mentioned in the documentation?

    So, because I can easily create it, I should be left to do it rather than have it implemented directly by the database? I'd even be ok with a simple bit data type, but it's silly that I should have to worry about re-inventing the boolean. It would be different if it was some complex data type (i.e. a phone number) but I don't see the harm in implementing the primitive bit.

    I don't have any reason to hate Delphi; I wasn't agreeing with the poster in that respect (although in hindsight it does appear so), just attempting to keep the thought in context.



  • Why do so many open source maintainers insist on making you compile from source yourself? When you do that, you get a fresh-off-the-press binary that... has never been tested. By anyone. It's a lot easier to get a pre-built binary that's already been run through full regression. Why the hell should everyone have to do that themselves when the maintainer can do it once for everyone?



  • @smxlong said:

    Why do so many open source maintainers insist on making you compile from source yourself?
    Because many of them only have access to a very limited set of platforms.



  • @smxlong said:

    Why do so many open source maintainers insist on making you compile from source yourself? When you do that, you get a fresh-off-the-press binary that... has never been tested. By anyone. It's a lot easier to get a pre-built binary that's already been run through full regression. Why the hell should everyone have to do that themselves when the maintainer can do it once for everyone?

    Open source maintainers, with very few exceptions, don't care about usability. That applies to installing the software as much as everything else.



  • @toth said:

    That would save hassle?

    It's only a few hundred lines of C code. But I guess you prefer to bang your head against the keyboard until it passes QA?



  • @Faxmachinen said:

    But I guess you prefer to bang your head against the keyboard until it passes QA?
     

    True story.


  • Garbage Person

    @blakeyrat said:

    Oh BTW, Toad? Significantly better than the awful management tools they had before. When:
    1) The tool doesn't use the shortcut Control-A for "Select All",
    2) The developers file the above bug as "won't fix",
    you know you're on to a "special" type of software. I'm not surprised they gave up and just use Toad now.
    Okay, yeah. They were pretty fucking horrible, but it was a predictable kind of horrible. Hell, they were even kind of good if your only other options are fucking phpmyadmin and the CLI.



  • @Weng said:

    @blakeyrat said:

    Oh BTW, Toad? Significantly better than the awful management tools they had before. When:
    1) The tool doesn't use the shortcut Control-A for "Select All",
    2) The developers file the above bug as "won't fix",
    you know you're on to a "special" type of software. I'm not surprised they gave up and just use Toad now.
    Okay, yeah. They were pretty fucking horrible, but it was a predictable kind of horrible. Hell, they were even kind of good if your only other options are fucking phpmyadmin and the CLI.

    I don't think having sex with a piece of software has ever been a direct option.



  • @DescentJS said:

    @Weng said:

    Okay, yeah. They were pretty fucking horrible, but it was a predictable kind of horrible. Hell, they were even kind of good if your only other options are fucking phpmyadmin and the CLI.

    I don't think having sex with a piece of software has ever been a direct option.

     

    At least it gives you a sense of having accomplished something.



  • @smxlong said:

    Why do so many open source maintainers insist on making you compile from source yourself?
    Because it's expected you get the software from your package manager.



  • @Lingerance said:

    @smxlong said:
    Why do so many open source maintainers insist on making you compile from source yourself?
    Because it's expected you get the software from your package manager.

    It's all a big conspiracy to keep proprietary software off Linux, and to keep Linux software difficult to write and deploy.

    You see, the only way to prevent your system from becoming a spaghetti mess of dependencies is to install all your software from the package manager... but package managers won't take proprietary software, so if you ever install proprietary stuff you either have a huge download because everything it needs has to be statically-linked*, or you have an installer that may or may not work depending on what libraries the package manager has decided to provide you. If you manually update the dependencies, expect your software to break the next time the package manager says "oh nobody (I know of) is using this library, I'll just update it! Or remove it!"

    The sane solution of course is to define a (large) standard set of libraries that ships with every version of the OS. Linux hasn't figured that one out yet.

    Remember this next time a Linux fan tells you they have nothing against proprietary software. They just file it in a basement filing cabinet labeled "beware of the jaguar" for kicks... they don't have anything *against* it!

    *) As an added bonus, statically-linked software is harder to fix when a security hole like, say, a PNG parsing flaw is discovered. So next time your OS patches the PNG libraries, your proprietary software might remain vulnerable.



  • @blakeyrat said:

    but package managers won't take proprietary software

    What? Package managers will install whatever package you tell them to.



  • @Spectre said:

    @blakeyrat said:
    but package managers won't take proprietary software

    What? Package managers will install whatever package you tell them to.

    The software will, yes. But it'll never get a chance, since the humans configuring it won't add proprietary software to the repository.

    I mean, yes, you could package your proprietary program in a .rpm or .deb or whatever, but that still has all the problems I listed above.



  • @blakeyrat said:

    I mean, yes, you could package your proprietary program in a .rpm or .deb or whatever, but that still has all the problems I listed above.

    How come? If you mark your dependencies correctly, the package manager will take care of them.



  • @Spectre said:

    @blakeyrat said:
    I mean, yes, you could package your proprietary program in a .rpm or .deb or whatever, but that still has all the problems I listed above.

    How come? If you mark your dependencies correctly, the package manager will take care of them.

    Well, admittedly it's been a few years since I looked into it, maybe my issues are fixed now.



  • @blakeyrat said:

    Well, admittedly it's been a few years since I looked into it, maybe my issues are fixed now.

    I doubt it, your issues probably require a shrink, denial won't help, the package manager issues, well that is another matter...



  • @bdew said:

    @TehFreek said:

    There is one, but no one cares.

    Could you give a link? my google foo seems to be weak today.


    myconnpy

Log in to reply