Our RSS Feeds
Introduction to using MySQLdb with python
What is MySQLdb?
MySQLdb is a python interface to MySQL It follows the python DB API (PEP-0249) which is analagous to JDBC in java. Ideally when using this API one should be able to easily port to another database.
How do I install the MySQLdb?
First see if MySQLdb is already installed by doing the following from a python shell:
>>> import MySQLdbIf you see an error message like the one below, then it isn't installed.
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ImportError: No module named MySQLdb
If you get no error message then, the interface is already installed.
The remainder of this article will explain how to install mysqldb from the source. (Note that my distributions provide binary packages as well).
Download a recent version from the project homepage.
Perform the following from a shell (unix):
$ tar xfz MySQL-python-1.2.0.tar.gz $ cd MySQL-python-1.2.0 $ python setup.py build $ su # or use sudo # python setup.py install
Peform the import test above to see that the install worked.
The basics
The basic flow of interacting with a database using the DB-API consists of:Importing the api module
Acquiring a connection
Issuing SQL statements
Closing the connection
A basic example
Here's a simple example showing all of the steps above. It connects to a database named dbname on the machine named host machine. It selects all of the content from the simpletable table and stores the first row in a variable called data. The connection is then closed to the database:
import MySQLdb
db = MySQLdb.connect("host machine", "dbuser", "password", "dbname")
cursor = db.cursor()
sql = """SELECT * FROM simpletable"""
cursor.execute(sql)
data = cursor.fetchone()
db.close()
Related Links
Interesting post, I enjoyed it and helped me with my python script.
#!/usr/bin/env python
# -*- coding: latin-1 -*-
headlines_url = "http://es.openoffice.org/servlets/ProjectNewsList"
import HTMLParser
import urllib2
import MySQLdb
class HeadlinesParser(HTMLParser.HTMLParser):
def __init__(self):
HTMLParser.HTMLParser.__init__(self)
self.headlines = {}
self.while_anchor = False
self.last_href = None
def handle_starttag(self, name, attrs):
attrs = dict(attrs)
if name == "a":
if "href" in attrs:
current_href = attrs["href"]
if "newsItemID" in current_href:
self.while_anchor = True
self.last_href = current_href
def handle_data(self, content):
content = content.strip()
if self.while_anchor:
self.headlines[content] = self.last_href
def handle_endtag(self, name):
if name == "a":
self.while_anchor = False
def conectarBD():
"""Rutina de conexión a la BBDD"""
try:
db = MySQLdb.Connect(host='localhost',user='myuser',passwd='mypassw
ord',db='mydatabase')
return db
except:
print u"Error en la conexion a la Base de Datos"
return -1
def insertarHeadLines(datos):
"""Funcion para insertar los enlaces en mysql"""
conn = conectarBD()
c = conn.cursor()
if (conn != -1):
try:
"""Insertamos los links"""
for headline in datos:
c.execute('''INSERT INTO Headlines (headline, url) VALUES
('%s', '%s')''' % ( headline ))
except:
print 'Problemas al realizar los «INSERT»'
return -1
def main():
"""
CREATE TABLE `Headlines` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT 'Identificador',
`headline` VARCHAR(254) NOT NULL COMMENT 'Titular de la
noticia',
`url` VARCHAR(254) NOT NULL COMMENT 'Enlace a la
noticia',
INDEX `HL`(`headline`),
PRIMARY KEY(`id`)
)
ENGINE = MYISAM
DEFAULT CHARSET=latin1
AUTO_INCREMENT = 1
COMMENT = 'Almacen de enlaces de es.openoffice.org';
"""
source = urllib2.urlopen(headlines_url).read()
parser = HeadlinesParser()
parser.feed(source)
lineas = parser.headlines.items()
try:
insertarHeadLines(lineas)
except:
print 'Problemas al insertar los enlaces.'
if __name__ == "__main__":
main()
aydin@aydin-ubuntu:~/Desktop/MySQL-python-1.2.2$ python setup.py build
Traceback (most recent call last):
File "setup.py", line 5, in <module>
import ez_setup; ez_setup.use_setuptools()
File "/home/aydin/Desktop/MySQL-python-1.2.2/ez_setup.py", line 85, in use_setuptools
import setuptools; setuptools.bootstrap_install_from = egg
can anyone help me with this error?
aydin@aydin-ubuntu:~/Desktop/MySQL-python-1.2.2$ python setup.py build
Traceback (most recent call last):
File "setup.py", line 5, in <module>
import ez_setup; ez_setup.use_setuptools()
File "/home/aydin/Desktop/MySQL-python-1.2.2/ez_setup.py", line 85, in use_setuptools
import setuptools; setuptools.bootstrap_install_from = egg
Aydin:
As you're using ubuntu you might as well do: sudo apt-get install
python-mysqldb