Archive

Posts Tagged ‘mysql’

SQLite: prevent SQL injection

December 31, 2012 2 comments

DON’T do this:

cmd = "update people set name='{0}' where id='{1}'".format(name, id)
curs.execute(cmd)

DO this instead:

cmd = "update people set name=? where id=?"
curs.execute(cmd, (name, id))

“If you are using MySQL or PostgreSQL, use %s (even for numbers and other non-string values!) and if you are using SQLite, use ?.”

Tip from here.

Categories: python Tags: , , ,

Using MySQL from Python

December 14, 2010 Leave a comment

Problem

You want to interact with a MySQL database from your Python script.

Solution

First of all, you need to install the following package:

sudo apt-get install python-mysqldb

Then try the following basic script to check if everything is OK:

#!/usr/bin/env python

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version: ", row[0]
cursor.close ()
conn.close ()

Example:

We have a .csv file with two columns: symbol and name. Iterate through the lines and insert each line in a database table as a record.

#!/usr/bin/env python

import MySQLdb

f1 = open('./NYSE.csv',  'r')
# A line looks like this:
# ZLC;    Zale Corporation

conn = MySQLdb.connect(host = "localhost",
                       user = "user",
                       passwd = "passwd",
                       db = "table")
cursor = conn.cursor()

for line in f1:
    pieces = map(str.strip, line.split(';'))
    #print "'%s' => '%s'" % (pieces[0], pieces[1])
    query = "INSERT INTO symbol_name (symbol, name) VALUES (\"%s\", \"%s\")" % (pieces[0], pieces[1])
    #print query
    cursor.execute(query)

f1.close()

conn.commit()
cursor.close ()
conn.close ()

Links

There are lots of Python-MySQL tutorials on the net. Let’s see some of them:

Categories: python Tags: