有关Python拼SQL操作数据库的故事

  • 5月 12, 2012

小白问：

我想用python操作数据库，我拼sql该怎么半？
 
PY控：
想想java吧
 
小白：
sql += name
sql += ‘, now(),’
…
 
PY控：
No No No
第一，拼string浪费内存，你这样不好于StringBuffer(‘123’+’ABC’)的错误。
 
第二，这样容易造成了sql注入，看看下面的笑话吧
一个家长接到电话： 
Teacher: Hi. This is your son’s school. We are having some computer trouble. 
Mom: Oh dear! Did he break something? 
Teacher: In a way… Did you really name your son "Robert’); DROP TABLE students –"? 
Mom: Yes. Little Bobby Tables, we call him. 
Teacher: Well, we’ve lost this year’s student records. I hope you’re happy. 
Mom: And I hope you’ve learned to sanitize your database input. 
 
  
第三，我说java其实是想让你想想用preparestatment，模板来填入?形式的参数，所以提供给你一段代码吧，不要光板砖，想想为什么。
 
#!/usr/bin/python2 
 
 import sqlite3 
 import datetime 
 import contextlib 
 
 with sqlite3.connect("testing.db") as conn: 
     with contextlib.closing(conn.cursor()) as cursor: 
         cursor.execute('''create table if not exists person ( 
                 id int, 
                 name text, 
                 birthday text 
                 )''') 
 
         cursor.execute('''insert into person values 
                 (1, 'Zhang San', "1980-01-02") 
                 ''') 
 
         cursor.execute('''insert into person values 
                 (2, 'Li Si', ?) 
                 ''', [datetime.datetime(1990,3,4)]) 
 
         cursor.execute('''insert into person values 
                 (?, ?, ?) 
                 ''', [3, "Wang Wu", datetime.datetime.now()]) 
 
         cursor.executemany('''insert into person values 
                 (?, ?, ?) 
                 ''', [ 
                     (4, "Alice", datetime.datetime(2001,7,8)), 
                     (5, "Bob", datetime.datetime(2002,9,10)), 
                     (6, "Cindy", datetime.datetime(2003,11,12)), 
                     ]) 
 
         d_str = raw_input("Before which date? (in yyyy-MM-dd. for example: 1995-5-6): ") 
         d = datetime.datetime.strptime(d_str, "%Y-%m-%d") 
 
         cursor.execute('''select * from person where birthday < ?''', 
                 [ d ]) 
 
         a = cursor.fetchall() 
         print a
 
 文章参考自byr论坛wks同学~
 
 
 

Leave a Comment.