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)?
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)?
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.
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 .
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