How to connect Python to Mysql database?

Question

I wonder how I can make a simple connection to Mysql through a Python script.

To do this I need to install some library? Or is there something that comes by default in Python (like in PHP)?

Answer 1

Yes. You need to have Mysql installed on your machine. When you have already installed and configured Mysql, you install the API Mysql-python.

Example:

First you import the drivers

import MySQLdb # para o MySQL

And uses the following code to make the connection:

con = MySQLdb.connect(host="ServidorMysql", user="UsuarioMysql", passwd="SuaSenha", db="SeuDb")
con.select_db('banco de dados')

These are the most common parameters. If you installed the server and made no changes, the following can be used:

con = MySQLdb.connect(user='UsuarioMysql', db='SeuDb')

To obtain a transaction (cursor):

cursor = con.cursor()

To execute an SQL command:

cursor.execute('INSERT INTO TABELA (CAMPO1, CAMPO2, CAMPO3) VALUES (?,?,?)', (valor1, valor2, valor3))

You can use a for to insert various values:

for i in xrange(10):
     c.execute("INSERT INTO teste VALUES (%s, 'teste%s')"%(i, i))

To save the changes:

con.commit()

To get the result:

rs = cursor.fetchone() # busca uma linha ou;
rs = cursor.fetchall() # busca todas as linhas ou;
rs = cursor.dictfetchall() # busca todas as linhas,
                           # cada linha tem um dicionário
                           # com os nomes dos campos

Sample script:

#!/usr/bin/python
# -*- coding: latin-1 -*-
# Importa o modulo de conexao com o mysql
import MySQLdb

# Gera a string de conexao ex.: seu host, seu usuario, sua senha e seu db
db = MySQLdb.connect(host="mysql.lhost03.w3br.com", user="lhost03", passwd="suasenha", db="seudb")
# Posiciona o cursor
cursor = db.cursor()
# Executa a consulta na tabela selecionada
cursor.execute("SELECT * FROM seudb.suatabela")
# Conta o numero de linhas na tabela
numrows = int(cursor.rowcount)
# Obtendo resultados
print "--------------------------------------------------"
print "| ID  Campo                                      |"
print "--------------------------------------------------"
# Laço for para retornar os valores, ex.: row[0] primeira coluna, row[1] segunda coluna, row[2] terceira coluna, etc.
for row in cursor.fetchall():
   print " ",row[0]," ",row[1]

This is not the only API, there is also the DB-API.

If you don’t want to use the API, you can install the Mysqldb, that unlike php, in Python, only the Sqlite driver is installed by default.

For Linux, you can use sudo apt-get install python-mysqldb.

After installation:

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",    # seu host
                     user="seuNome",      # seu user
                     passwd="senha",      # sua senha
                     db="SeuDB")          # nome do seu banco de dados

# Cria Cursor  
c = db.cursor()

# Executa o comando SQL
c.execute("SELECT * FROM YOUR_TABLE_NAME")

# Imprimir toda a primeira célula de todas as linhas
for l in c.fetchall():
    print l[0]

db.close()

Based on that reply stackoverflow.

Recalling that the Python does not have native access to SQL databases in its standard library, only to the Berkley database engine(BDB), but sets a standard API that drivers accessing these databases must follow, so whatever the database the procedures are similar.

Answer 2

The site itself mysql has an area focused on this subject, but I will try to summarize a little.

First, you need the Mysql for Python connector.

After installing the Nector, just import it into your system. An example would be this:

import mysql.connector

cnx = mysql.connector.connect(user='scott', password='tiger',
                              host='127.0.0.1',
                              database='employees')
cnx.close()

To use the connection, just do something like this:

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

If you want to see more examples, you can get official Mysql documentation.

There are other ways to do what is shown in this question by Soen.

Answer 3

Only one addendum: Who had difficulties installing the Mysql Connector library through the virtualenv, as I had, you can use the following command:

$ pip install mysql-connector-python-rf

The other library cited, Mysql-Python, I had to run the following commands:

$ easy_install -U distribute
$ pip install MySQL-Python