Top 10 Python mysql Questions Answered

TL;DR:

• pip install -Iv (i.e. pip install -Iv MySQL_python==1.2.2)

First, I see two issues with what you’re trying to do. Since you already have an installed version, you should either uninstall the current existing driver or use pip install -I MySQL_python==1.2.2

However, you’ll soon find out that this doesn’t work. If you look at pip’s installation log, or if you do a pip install -Iv MySQL_python==1.2.2 you’ll find that the PyPI URL link does not work for MySQL_python v1.2.2. You can verify this here: http://pypi.python.org/pypi/MySQL-python/1.2.2

The download link 404s and the fallback URL links are re-directing infinitely due to sourceforge.net’s recent upgrade and PyPI’s stale URL.

So to properly install the driver, you can follow these steps:

pip uninstall MySQL_python
pip install -Iv http://sourceforge.net/projects/mysql-python/files/mysql-python/1.2.2/MySQL-python-1.2.2.tar.gz/download

Connecting to MYSQL with Python 2 in three steps

1 – Setting

You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it’s hard to install it using easy_install. Please note MySQLdb only supports Python 2.

For Windows user, you can get an exe of MySQLdb.

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)

For Mac, you can install MySQLdb using Macport.

2 – Usage

After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.

Then it is just like using any other package :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="john",         # your username
                     passwd="megajonhy",  # your password
                     db="jonhydb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]

db.close()

Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.

3 – More advanced usage

Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

I strongly advise you to use it: your life is going to be much easier.

I recently discovered another jewel in the Python world: peewee. It’s a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title

This example works out of the box. Nothing other than having peewee (pip install peewee) is required.

It’s easy to do, but hard to remember the correct spelling:

pip install mysqlclient

If you need 1.2.x versions (legacy Python only), use pip install MySQL-python

Note: Some dependencies might have to be in place when running the above command. Some hints on how to install these on various platforms:

Ubuntu 14, Ubuntu 16, Debian 8.6 (jessie)

sudo apt-get install python-pip python-dev libmysqlclient-dev

Fedora 24:

sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

Mac OS

brew install mysql-connector-c

if that fails, try

brew install mysql

It seems mysql_config is missing on your system or the installer could not find it.
Be sure mysql_config is really installed.

For example on Debian/Ubuntu you must install the package:

sudo apt-get install libmysqlclient-dev

Maybe the mysql_config is not in your path, it will be the case when you compile by yourself
the mysql suite.

Update: For recent versions of debian/ubuntu (as of 2018) it is

sudo apt install default-libmysqlclient-dev

mySQLdb is a python interface for mysql, but it is not mysql itself. And apparently mySQLdb needs the command ‘mysql_config’, so you need to install that first.

Can you confirm that you did or did not install mysql itself, by running “mysql” from the shell? That should give you a response other than “mysql: command not found”.

Which linux distribution are you using? Mysql is pre-packaged for most linux distributions. For example, for debian / ubuntu, installing mysql is as easy as

sudo apt-get install mysql-server

mysql-config is in a different package, which can be installed from (again, assuming debian / ubuntu):

sudo apt-get install libmysqlclient-dev

if you are using mariadb, the drop in replacement for mysql, then run

sudo apt-get install libmariadbclient-dev

Reference:
https://github.com/JudgeGirl/Judge-sender/issues/4#issuecomment-186542797

Probably one of the indices is wrong, either the inner one or the outer one.

I suspect you meant to say [0] where you said [1], and [1] where you said [2]. Indices are 0-based in Python.

I was facing the similar issue. My env details –
Python 2.7.11
pip 9.0.1
CentOS release 5.11 (Final)

Error on python interpreter –

>>> import mysql.connector
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named mysql.connector
>>>

Use pip to search the available module –

$ pip search mysql-connector | grep --color mysql-connector-python



mysql-connector-python-rf (2.2.2)        - MySQL driver written in Python
mysql-connector-python (2.0.4)           - MySQL driver written in Python

Install the mysql-connector-python-rf –

$ pip install mysql-connector-python-rf

Verify

$ python
Python 2.7.11 (default, Apr 26 2016, 13:18:56)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-54)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector
>>>

Thanks =)

For python3 and later use the next command:
$ pip3 install mysql-connector-python-rf

I had the same problem and passing auth_plugin='mysql_native_password' did not work, because I accidentally installed mysql-connector instead of mysql-connector-python (via pip3). Just leaving this here in case it helps someone.

MySQL support is simple to add. In your DATABASES dictionary, you will have an entry like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'DB_NAME',
        'USER': 'DB_USER',
        'PASSWORD': 'DB_PASSWORD',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

You also have the option of utilizing MySQL option files, as of Django 1.7. You can accomplish this by setting your DATABASES array like so:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': '/path/to/my.cnf',
        },
    }
}

You also need to create the /path/to/my.cnf file with similar settings from above

[client]
database = DB_NAME
host = localhost
user = DB_USER
password = DB_PASSWORD
default-character-set = utf8

With this new method of connecting in Django 1.7, it is important to know the order connections are established:

1. OPTIONS.
2. NAME, USER, PASSWORD, HOST, PORT
3. MySQL option files.

In other words, if you set the name of the database in OPTIONS, this will take precedence over NAME, which would override anything in a MySQL option file.

If you are just testing your application on your local machine, you can use

python manage.py runserver

Adding the ip:port argument allows machines other than your own to access your development application. Once you are ready to deploy your application, I recommend taking a look at the chapter on Deploying Django on the djangobook

Mysql default character set is often not utf-8, therefore make sure to create your database using this sql:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_bin

If you are using Oracle’s MySQL connector your ENGINE line should look like this:

'ENGINE': 'mysql.connector.django',

Note that you will first need to install mysql on your OS.

brew install mysql (MacOS)

Also, the mysql client package has changed for python 3 (MySQL-Client works only for python 2)

pip3 install mysqlclient

Do:

con.query('SET GLOBAL connect_timeout=28800')
con.query('SET GLOBAL interactive_timeout=28800')
con.query('SET GLOBAL wait_timeout=28800')

Parameter meaning (taken from MySQL Workbench in Navigator: Instance > Options File > Tab "Networking" > Section "Timeout Settings")

• connect_timeout: Number of seconds the mysqld server waits for a connect packet before responding with ‘Bad handshake’
• interactive_timeout Number of seconds the server waits for activity on an interactive connection before closing it
• wait_timeout Number of seconds the server waits for activity on a connection before closing it

BTW: 28800 seconds are 8 hours, so for a 10 hour execution time these values should be actually higher.