How to connect Python with Mysql?

7

I would like to know how I can make a simple Mysql connection through a Python script.

For me to do this do I need to install some library? Or is there something that comes by default in Python (same as in PHP)?

    
asked by anonymous 10.01.2017 / 19:07

3 answers

8

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

Example :

First you import the drivers

import MySQLdb # para o MySQL

And use 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 may use:

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

To get 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 for to insert multiple values:

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

To save 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 DB-API .

If you do not want to use API, you can install MySQLdb , which 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 this response of stackoverflow in English.

Remembering that Python does not have native access to SQL databases in its default library, only the Berkley database engine (BDB), but it defines a default API that the access drivers to these databases should follow, so whatever the database the procedures are alike.

    
10.01.2017 / 19:12
5

MySQL's own site has an area dedicated to this subject, but I'll try to summarize one little.

First, you need MySQL Connector for Python .

After installing the connector, simply 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'd like to see more examples, you can get the official MySQL documentation .

There are other ways to do this, which is shown this SOen question .

    
10.01.2017 / 19:20
1

Only one addendum: Anyone who had difficulty installing the Mysql Connector library through virtualenv , as I did, could use the following command:

$ pip install mysql-connector-python-rf

As for the other library mentioned, MySQL-Python, I had to run the following commands:

$ easy_install -U distribute
$ pip install MySQL-Python
    
29.05.2017 / 19:08