Sematkan kueri sql dengan python

Semua konektor menyertakan fungsi eksekusi yang mengasumsikan pernyataan SQL sebagai parameter string dan yang dapat dijalankan pada bagian database. Namun, penggunaan Python tidak terlalu masuk akal sampai SQL dihasilkan secara dinamis dan digerakkan oleh data

Pada titik inilah saya ingin menginterupsi dan mendemonstrasikan berbagai alternatif – dimulai dengan metode yang paling sederhana tetapi juga paling tidak cerdas – dan diakhiri dengan praktik terbaik tentang bagaimana string SQL harus ditransmisikan

Pertama-tama, saya pertama-tama harus memperjelas bahwa setiap alternatif kecuali yang terakhir merupakan celah keamanan yang berpotensi berbahaya. Ada kemungkinan, jika tidak ada tindakan pengamanan lain yang diambil, data sensitif dapat diambil atau bahkan dihapus

Pendekatan paling naif dan paling berbahaya. rangkaian string

Pertama-tama, kami membuat database pengujian di SQLite. Alasan saya menggunakan SQLite untuk demonstrasi adalah bahwa SQLite hadir dengan Python, dimungkinkan untuk membuat database langsung di memori untuk runtime skrip yang berarti bahwa contoh dapat direplikasi untuk masing-masing. Namun, saya hanya dapat menjamin eksekusi bebas kesalahan dari contoh yang dimulai dengan Python 3. x

 import sqlite3 
db = sqlite3.connect(':memory:') 
db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
db.execute("INSERT INTO staff (person_id, lastname) VALUES (1, 'Pavlov') ") 
db.execute("INSERT INTO staff (person_id, lastname) VALUES (2, 'Skinner') ") 

Dalam kode sampel, database tanpa nama diinisialisasi di memori dengan memasukkan '. Penyimpanan. ' sebagai lokasi penyimpanan di perintah connect setelah mengimpor modul
Kemudian spreadsheet karyawan bernama "staf" dibuat dan diisi dengan kumpulan data pertama

Sejauh ini bagus. Tetapi kita tidak ingin menulis seluruh perintah penyisipan untuk setiap karyawan yang akan kita tambahkan ke spreadsheet
Jika nama karyawan sudah tersedia sebagai daftar, maka ini benar-benar cocok untuk menggunakan loop

Percobaan pertama

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Terlepas dari semua niat baik, itu gagal. Pemberitahuan kesalahan "TypeError. Tidak dapat mengonversi objek 'int' menjadi str secara implisit" menyiratkan bahwa kami lupa untuk mentransmisikan tipe data person_id dari integer ke str. Meskipun Python fleksibel dalam hampir semua hal, itu masih merupakan bahasa yang sangat kuat dan string yang tidak dapat diubah tidak dapat digabungkan dengan bilangan bulat

Untuk ini, kompiler harus bangun lebih awal. Upaya berikutnya

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR);") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + str(person_id) + ", '" + lastname + "') ")

Yah, itu berjalan tetapi tidak terlihat bagus sama sekali. Terutama ketika banyak dari rangkaian ini digunakan dalam kode, maka pernyataan itu sangat terfragmentasi. Terlebih lagi, saya harus selalu mengurus konversi jenis sendiri

Pendekatan lama. templat string dengan %s

Saat membuka-buka literatur Python, bahkan hal-hal yang lebih terkini, dan membaca berbagai entri forum di Stack Overflow atau di mana pun, teknik dapat dilihat seperti ini di contoh kami

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (%d, '%s') " % (person_id, lastname)) 

Bekerja seperti mimpi. "%d" adalah placeholder untuk sebuah digit dan "%s" adalah placeholder untuk sebuah string

Namun, jika nilai ini diperlukan beberapa kali saat menggunakan notasi ini, ia menjadi agak campur aduk lagi. Mari kita bayangkan contoh di mana kita memeriksa berbagai kondisi dalam kueri

 sql = """SELECT lastname , CASE WHEN %d > 10 THEN 'greater' WHEN %d = 10 THEN 'equal' WHEN %d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%s' and %d > 0 """ % (person_id, person_id, person_id, lastname, person_id) 

Segera setelah kami memasukkan placeholder lain di sini, risiko kesalahan bertambah dan kami harus menghitung posisi dalam kode setiap saat. Di sini lebih baik memilih placeholder yang ditunjuk

 sql = """SELECT lastname , CASE WHEN %(person_id)d > 10 THEN 'greater' WHEN %(person_id)d = 10 THEN 'equal' WHEN %(person_id)d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname <> '%(lastname)d' and %(person_id)d > 0 """ % {'person_id': person_id, 'lastname': lastname) 

Hebat. Kode sekarang jauh lebih mudah dibaca karena sekarang kita dapat melihat apa yang kita sisipkan dan di mana kita langsung menyisipkannya

Notasi ini hanya menimbulkan satu masalah kecil, yaitu dianggap usang dan, setidaknya di Python 3, telah diganti dengan yang lebih baik. Sekitar 3 atau 4 tahun yang lalu, saya membaca berkali-kali di forum bahwa notasi ini bahkan dianggap sudah usang. Ini berarti bahwa itu tidak boleh digunakan lagi karena kelanjutannya dalam versi Python yang lebih baru tidak dijamin. Namun saat ini, itu belum ditinggalkan – mungkin karena masih sangat luas dalam modul

Pendekatan baru. template string dengan {}

Notasi resmi yang baru menggunakan kurung kurawal. Tidak hanya terlihat berbeda, tetapi juga menyimpan lebih banyak potensi dalam hal opsi pemformatan. Ketika notasi baru diadopsi karena bisa berbuat lebih banyak, mengapa tidak menggunakannya secara konsisten?

Mari kita lihat versi sederhananya terlebih dahulu

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(person_id, lastname)) 

Penting untuk dicatat di sini bahwa ketika tampilan tidak berperan dalam hal penempatan koma atau angka nol di depan, dll. , maka tidak perlu ada perbedaan antara string atau nilai numerik untuk placeholder. Ya, bahkan dimungkinkan untuk menggunakan tupel, misalnya

 db.execute("SELECT * FROM staff WHERE person_id in {}".format((1,3,4))) 

Fungsi format dari string memanggil metode __str__ dari setiap objek. Ini kemudian sesuai dengan setiap str(objek)

Ada juga notasi dengan label di sini, tetapi tidak ada kamus yang dikirimkan. Sebaliknya, alokasi ditulis dalam bentuk parameter fungsional

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({person_id}, '{lastname}') ".format(person_id=person_id, lastname=lastname)) 

Setiap orang yang malas menulis juga bisa menggunakan tuple packing dan tuple unpacking sendiri

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for row in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}') ".format(*row)) 

baris adalah tuple di setiap siklus loop karena enumerate() mengembalikan dua nilai sebagai tuple. Ini kemudian dipaksa ke baris variabel. Dengan notasi ". format(*row)" tuple dapat dibongkar lagi dan nilainya dapat dipanggil dalam urutan yang sesuai

Hal yang sama bekerja dengan kamus

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Contoh paling ekstrem karena ingin menghindari pengetikan adalah contoh teoretis dari fungsi penyisipan ini

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Di sini, perintah format hanya mengambil data dari variabel yang ditentukan di namespace pemanggilan fungsi, dalam hal ini, parameter fungsional. Saya telah menggunakan contoh ini sendiri di Python 2. 7. Dalam Python 3. x, bagaimanapun, ini tidak berfungsi lagi dan saya yakin lebih baik seperti ini

Sekarang, bayangkan kita mendapatkan karyawan baru, yang kelima puluh, bernama OʼReilly. Nama ditambahkan dengan cepat

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

SQLite mengeluh di sini "sqlite3. Kesalahan Operasional. di dekat "Reilly". kesalahan sintaks". Apa itu semua tentang?

Dimungkinkan untuk menemukan kelegaan di tengah hiruk pikuk pengkodean di sini dengan menghapus apostrof. Ini bervariasi menurut database. Di SQLite, apostrof harus digandakan

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Yah, itu berfungsi untuk saat ini, tapi itu hanya solusi yang murah

Jadi, sekarang kami mendapatkan karyawan baru lagi, yang kelima puluh satu, bernama Tuan "');staf DROP TABLE;". Nama yang aneh tetapi jika itu yang dimasukkan pengguna maka itu pasti benar

Jika kita tidak akan menghapus berbagai karakter dari acara dengan Tuan O'Reilly dan jika kita tidak akan bekerja dengan SQLite, sebuah program yang tidak mengizinkan 2 pernyataan dalam string eksekusi, string kueri akan muncul sebagai berikut

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Seluruh spreadsheet telah dihapus di sini berkat input pengguna yang tidak bersih. Suntikan SQL ini adalah bahaya nyata bagi keamanan database. Beberapa tahun yang lalu, ketika saya mencoba berbagai hal, saya menemukan pengecer online besar yang bahkan tidak membersihkan apostrof dalam pencarian produk mereka.

Praktek terbaik. kueri berparametri

Ketiga alternatif yang didemonstrasikan berhasil. Demi kecepatan, saya sendiri masih menggunakan salah satu notasi semacam ini atau yang lainnya

Namun, praktik terbaik yang sangat jelas adalah penggunaan "kueri parameter". Terlepas dari bahasa pemrogramannya, setiap konektor database harus mendukung jenis transmisi kueri ini. Dalam Python, setidaknya, saya tahu dari pengalaman praktis bahwa ini berfungsi untuk Oracle, MySql, SQLite, dan PostgreSQL

Ide di baliknya adalah bahwa string SQL tidak dikompilasi seluruhnya oleh satu orang dan kemudian dikirim ke konektor, melainkan template dan parameter untuk template tersebut ditransmisikan

Ada berbagai keuntungan. Salah satunya adalah driver basis data mengasumsikan semua konversi tipe dan perlakuan khusus simbol seperti apostrof. Itu berarti tidak perlu khawatir tentang konvensi masing-masing dalam database

Lainnya adalah bahwa ada keunggulan kinerja pada platform database tertentu jika query rump yang sama dijalankan dengan berbagai parameter secara sangat sering. Maka pengurai SQL tidak perlu mengurai ulang kueri untuk merencanakan eksekusi setiap saat. Sebaliknya itu jatuh kembali pada eksekusi sebelumnya dan hanya mengganti nilai pada placeholder

Sayangnya, jenis parameterisasi di berbagai platform database sama sekali tidak konsisten. Driver database Python memiliki setidaknya satu atribut bernama paramstyle yang menentukan teknik mana yang harus digunakan

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Dalam contoh SQLite, pertama-tama kita periksa paramstyle mana yang penting. Ini adalah 'qmark', yaitu tanda tanya. Pernyataan insert menunjukkan penggunaan. Tanda tanya ditambahkan untuk setiap posisi. Dalam urutan yang benar, nilai harus ditransmisikan sebagai tuple sebagai parameter kedua dalam pemanggilan fungsi. Jenis konversi berjalan secara otomatis, bahkan untuk objek tanggal

Di PostgreSQL, misalnya, ada format berbeda yang juga memungkinkan parameter yang ditentukan

 db.execute("CREATE TABLE staff (person_id int, lastname CHAR); ") 
for person_id, lastname in enumerate(staff_names): 
    db.execute("INSERT INTO staff (person_id, lastname) VALUES (" + person_id + ", '" + lastname + "') ")

Praktik terbaik = Hanya praktik?

Judul "Best Practice" sebenarnya menyesatkan. Itu harus benar-benar "Hanya Berlatih" sebagai gantinya. Sekilas, kueri berparametri tampak rumit. Terutama ketika pernyataan SQL dibangun secara dinamis menggunakan rangkaian string, mudah untuk parameter menjadi kacau, terutama ketika placeholder hanya ditandai dengan tanda tanya seperti yang terjadi di SQLite. Namun, tidak perlu khawatir tentang konversi jenis dan simbol, jika dibandingkan, merupakan keuntungan utama

Biasanya, saya sekarang akan berkewajiban untuk mengatakan bahwa aspek keamanan – yaitu menghindari injeksi SQL – harus menjadi alasan paling penting untuk menggunakan praktik terbaik ini dan memang sudah cukup dengan sendirinya

Dalam pengaturan ilmu data sehari-hari, pengaturan di mana banyak tindakan pencegahan keamanan harus diambil hingga data dapat diakses dan di mana aplikasi tidak dapat serta merta dioperasikan oleh pengguna yang berpotensi ganas, aspek keamanan lebih merupakan latihan sukarela. Sangat jarang selama bertahun-tahun dalam pekerjaan ini saya berada dalam situasi di mana kode Python yang berpotensi berbahaya telah dapat diakses oleh siapa pun yang tidak terlibat langsung dalam proyek. Dalam kasus ini, setiap orang akan memiliki akses langsung ke database

Bagi siapa saja yang ingin memaksimalkan Python untuk pembuatan SQL dan membuat kueri SQL dinamis yang sangat kuat, maka mereka akan dipaksa untuk berpaling dari praktik terbaik di beberapa titik. Hanya mungkin untuk memparametrise input nilai. Hanya mungkin untuk terus menggunakan nama tabel dan nama kolom dengan menggunakan salah satu teknik yang dijelaskan di atas secara dinamis

Akibatnya, saya harus mengakui bahwa saya sendiri selalu menggunakan campuran kueri berparametri dan template string dengan {}. Namun, penting untuk memastikan bahwa hanya pengguna yang berwenang yang mendapatkan akses ke skrip dan data. Langkah pertama ke arah ini adalah memastikan bahwa setiap orang yang menjalankan skrip database Python memiliki pengguna mereka sendiri untuk koneksi, bukan pengguna generik.

Apakah Python mendukung SQL tersemat?

Bagaimana cara menggunakan kueri SQL dengan Python?

Apa itu SQL tersemat di Python?

Bagaimana cara menyimpan kueri SQL dalam variabel dengan Python?