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
mysql, postgresql, sql injection, sqlite
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: