[python] MySQL 매개 변수화 된 쿼리

Question 1

내 데이터베이스에 정보를 삽입하기 위해 MySQLdb 모듈을 사용하는 데 어려움을 겪고 있습니다. 6 개의 변수를 테이블에 삽입해야합니다.

cursor.execute ("""
    INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
    VALUES
        (var1, var2, var3, var4, var5, var6)

""")

누군가 여기에서 구문을 도와 줄 수 있습니까?

Question 2

SQL 쿼리에 문자열 보간을 사용하면 입력 매개 변수가 올바르게 이스케이프되지 않고 애플리케이션이 SQL 주입 취약성에 노출 될 수 있으므로주의해야합니다. 그 차이는 사소 해 보일 수 있지만 실제로는 엄청납니다 .

잘못됨 (보안 문제 포함)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

맞음 (이스케이프 포함)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

SQL 문에서 매개 변수를 바인딩하는 데 사용되는 수정자가 다른 DB API 구현에 따라 다르며 mysql 클라이언트 라이브러리가 printf일반적으로 허용되는 ‘?’대신 스타일 구문을 사용한다는 혼란을 더합니다. 마커 (예 :에서 사용 python-sqlite).

Question 3

몇 가지 옵션을 사용할 수 있습니다. 파이썬의 문자열 반복에 익숙해지기를 원할 것입니다. 앞으로 이와 같은 것을 알고 싶을 때 더 성공적으로 검색 할 수있는 용어입니다.

쿼리에 더 적합합니다.

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

이미 모든 데이터가 개체 또는 사전에있을 수 있다는 점을 고려할 때 두 번째 형식이 더 적합 할 것입니다. 또한 1 년 안에 돌아와서이 메서드를 업데이트해야 할 때 문자열에 “% s”개의 출현 횟수를 세는 것도 짜증납니다. 🙂

Question 4

링크 된 문서는 다음 예제를 제공합니다.

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

따라서이를 자신의 코드에 적용하면됩니다. 예 :

cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%s, %s, %s, %s, %s, %s)

        """, (var1, var2, var3, var4, var5, var6))

(SongLength가 숫자 인 경우 % s 대신 % d를 사용해야 할 수 있습니다.)

Question 5

실제로 변수 (SongLength)가 숫자 인 경우에도 매개 변수를 올바르게 바인딩하려면 % s로 형식을 지정해야합니다. % d를 사용하려고하면 오류가 발생합니다. 다음은 http://mysql-python.sourceforge.net/MySQLdb.html 링크에서 발췌 한 것입니다 .

쿼리를 수행하려면 먼저 커서가 필요한 다음 쿼리를 실행할 수 있습니다.

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

이 예에서 max_price = 5 그러면 문자열에 % s를 사용하는 이유는 무엇입니까? MySQLdb는이를 SQL 리터럴 값 (문자열 ‘5’)으로 변환하기 때문입니다. 완료되면 쿼리는 실제로 “… WHERE price <5″라고 말합니다.

Question 6

선택한 답변에 대한 대안으로 Marcel과 동일한 안전한 의미를 사용하여 Python 사전을 사용하여 값을 지정하는 간단한 방법이 있습니다. 삽입 할 열을 추가하거나 제거 할 때 쉽게 수정할 수 있다는 이점이 있습니다.

  meta_cols=('SongName','SongArtist','SongAlbum','SongGenre')
  insert='insert into Songs ({0}) values ({1})'.
        .format(','.join(meta_cols), ','.join( ['%s']*len(meta_cols) ))
  args = [ meta[i] for i in meta_cols ]
  cursor=db.cursor()
  cursor.execute(insert,args)
  db.commit()

여기서 meta 는 삽입 할 값이 들어있는 사전입니다. 업데이트는 동일한 방법으로 수행 할 수 있습니다.

  meta_cols=('SongName','SongArtist','SongAlbum','SongGenre')
  update='update Songs set {0} where id=%s'.
        .format(','.join([ '{0}=%s'.format(c) for c in meta_cols ]))
  args = [ meta[i] for i in meta_cols ]
  args.append( songid )
  cursor=db.cursor()
  cursor.execute(update,args)
  db.commit()

Question 7

첫 번째 솔루션이 잘 작동합니다. 여기에 작은 세부 사항 하나를 추가하고 싶습니다. 교체 / 업데이트하려는 변수가 str 유형이어야합니다. 내 mysql 유형은 십진수이지만 쿼리를 실행할 수 있도록 매개 변수 변수를 str로 만들어야했습니다.

temp = "100"
myCursor.execute("UPDATE testDB.UPS SET netAmount = %s WHERE auditSysNum = '42452'",(temp,))
myCursor.execute(var)

Question 8

여기에 다른 방법이 있습니다. MySQL 공식 웹 사이트에 문서화되어 있습니다.
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

정신적으로는 @Trey Stout의 대답과 동일한 메커니즘을 사용하고 있습니다. 그러나 나는 이것이 더 예쁘고 더 읽기 쉽다는 것을 알았습니다.

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

그리고 변수의 필요성을 더 잘 설명하기 위해 :

주의 : 이스케이프가 수행되고 있음을 유의하십시오.

employee_id = 2
first_name = "Jane"
last_name = "Doe"

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (employee_id, conn.escape_string(first_name), conn.escape_string(last_name), datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)