Khóa học Sắp xếp dữ liệu của James Howison từ Trường Thông tin tại Đại học Texas ở Austin
Các truy vấn được tham số hóa cho phép chúng tôi chèn các biến vào các truy vấn SQL của mình. Điều này tương tự với chuỗi f và phương thức
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
0Các truy vấn được tham số hóa có hai chức năng quan trọng
- Họ “khử trùng” đầu vào từ người dùng để bảo vệ chống lại các truy vấn độc hại đến cơ sở dữ liệu
- Chúng đơn giản hóa việc sử dụng lại các truy vấn bên trong các vòng lặp
Đôi khi, chúng tôi có các giá trị ở dạng chuỗi và int trong python mà chúng tôi muốn sử dụng trong các truy vấn. Chúng tôi có thể đã nhận được đầu vào từ thiết bị đầu cuối hoặc biểu mẫu web. Rất thường xuyên, chúng tôi đang làm việc với dữ liệu mà chúng tôi đã đọc từ tệp CSV và đang chèn vào cơ sở dữ liệu
Trong những trường hợp này, chúng tôi không thể chỉ làm việc với các truy vấn SQL được mã hóa cứng [chẳng hạn như
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
1] mà chúng tôi phải đưa dữ liệu vào các truy vấn. Điều này rất giống với việc nội suy các biến thành các chuỗi, như chúng ta đã làm với bài thơ của Michael Finegan, nhưng có một số điểm khác biệt. Cho dù dữ liệu do ai đó nhập vào hay đến từ tệp CSV, chúng tôi phải coi chúng là dữ liệu nguy hiểm tiềm ẩn vì chúng có thể chứa các ký tự đặc biệt có thể gây hại cho cơ sở dữ liệu [xem bên dưới]Đối với các chuỗi nội suy, chúng tôi đã sử dụng
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
2 và chúng tôi đã sử dụng with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
3 hoặc with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
4. Đối với sql thì không đủ, vì nếu chúng tôi đang chèn một số, chúng tôi không cần dấu ngoặc kép nhưng nếu chúng tôi đang chèn một chuỗi thì chúng tôi sẽ làm. Có thể quan trọng hơn, chúng tôi cũng phải đảm bảo rằng chúng tôi không chèn các chuỗi có thể chứa SQL cố gây ra sự cố. Một trong những lỗi bảo mật phổ biến nhất được gọi là “Tấn công tiêm nhiễm SQL” và có thể tránh được lỗi này thông qua các truy vấn được tham số hóa. Xem truyện tranh xuất sắc của XKCD giải thích điều này. Nghiêm túc đó, đọc truyện tranh đó và đọc lời giải thích. Tôi sẽ đợiTrong ví dụ đầu tiên, chúng ta sẽ chỉ làm việc với một đầu vào để nội suy [hay còn gọi là dính vào truy vấn]. Chúng tôi thiết lập một từ điển [được gọi là
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
5, chỉ là một quy ước] với các phím, sau đó sử dụng các phím đó trong trình giữ chỗ và chuyển cả trình giữ chỗ và param_dict cho con trỏ. hành hình[]Lưu ý rằng phần giữ chỗ ở đây không phải là
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
6 như với with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
0 và with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
4 mà là with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
9 khá xấu xí. Vì vậy, đó là dấu phần trăm with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
00 sau đó là dấu ngoặc tròn with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
01 và dấu cuối cùng là with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
02. Dễ dàng quên đi with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
02 cuối cùng đó. [và nó luôn là một with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
02 ngay cả khi đó là một con số hoặc một ngày tháng]with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
5Chúng tôi cũng có thể sử dụng điều này với nhiều phím trong lệnh và thật thuận tiện để
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
05 dữ liệu. Đây là những gì bạn sẽ sử dụng trong dự án của bạnwith psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
0Trường hợp điều này trở nên thực sự hữu ích là thay thế dữ liệu được lấy từ các hàng khác nhau của tệp CSV. Mỗi hàng từ CSV sẽ xuất hiện dưới dạng từ điển, vì vậy chúng tôi sử dụng tên của các khóa làm trình giữ chỗ trong truy vấn SQL của mình
Trong ví dụ bên dưới, chúng tôi đang làm việc với danh sách từ điển, sử dụng một truy vấn SQL được tham số hóa để chèn từng hàng vào cơ sở dữ liệu. Id được chỉ định vì cột
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
06 được khai báo là SERIAL. Chúng ta sẽ tìm hiểu cách lấy lại id đã tạo sauwith psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
0Chúng tôi xác định chuỗi một lần, nhưng thực hiện bằng cách sử dụng nó một lần trên mỗi hàng. Bạn có thể xem các thay đổi bằng cách chạy
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
1 [thông qua Python hoặc trong sổ ghi chép xsql]Nhận id #
Để có được
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
06 được chỉ định bằng cách thêm một hàng vào bảng được xác định bằng cột SERIAL PRIMARY KEY, chúng ta có thể thêm with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
09 vào truy vấn with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
05 của mìnhwith psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
5Sử dụng python để chạy truy vấn này [tạo bảng mới]
TẠO BẢNG sinh viên [ id SERIAL PRIMARY KEY, name TEXT, age INTEGER ]
Sau đó, tạo danh sách từ điển có ba tên và tuổi của học sinh và sử dụng truy vấn được tham số hóa để chèn các hàng
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
6with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
7with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
8Bản ghi hình cũ hơn #
Bản ghi màn hình có liên quan, sử dụng không phải Jupyter, có ở đây MySQL và trình giữ chỗ/truy vấn được tham số hóa
Phần còn lại của điều này chỉ dành cho sở thích, đừng bận tâm đến nó trừ khi bạn tò mò #
Mặc dù tôi nghĩ rằng kiểu tham số hóa param_dict [còn được gọi là tham số “pyformat”] dễ sử dụng nhất quán nhưng có hai dạng khác mà bạn có thể gặp phải. Cái đầu tiên sử dụng riêng %s cho từng tham số. Hơi khó hiểu là %s cho mỗi trường, bất kể đó là chuỗi, int hay kiểu dữ liệu khác. Kiểu thông số này được gọi là “định dạng” sql = “CẬP NHẬT hiệu suất SET địa điểm_id = %s WHERE id = %s”
Sau đó, bạn chuyển các biến theo đúng thứ tự sang phần thứ hai của con trỏ. thực hiện chức năng. Hơi khó hiểu khi bạn phải bao quanh danh sách các biến trong một tập hợp các dấu ngoặc tròn. Nếu bạn quan tâm thì đây là một kiểu dữ liệu khác được gọi là “tuple”.
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
01Phong cách khác chỉ là tạo chuỗi theo cách thủ công với. nội suy chuỗi định dạng. Ngoài việc không an toàn, điều này còn khó khăn vì bạn phải nhớ khi nào thêm dấu ngoặc kép xung quanh chuỗi hoặc không xung quanh int, v.v.
with psycopg2.connect[host='localhost', dbname='class_music_festival'] as conn:
with conn.cursor[cursor_factory=RealDictCursor] as cursor:
cursor.execute["ALTER SEQUENCE venues_id_seq RESTART WITH 1000"] # fix sequence due to copying ids from csv file
0Nếu bạn có lo ngại về bảo mật [e. g. , đặc biệt nếu dữ liệu bạn đang chèn đến từ một biểu mẫu web] và vì lý do nào đó bạn không muốn sử dụng tham số hóa %s hoặc %[dict_key]s [định dạng hoặc pyformat], thì bạn có thể “khử răng cưa” . thoát khỏi