Làm thế nào để bạn chuyển một biến vào câu lệnh sql trong python?

Question

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

Nội dung chính Show

Screencast cũ hơn
Phần còn lại chỉ dành cho sở thích, đừng bận tâm đến nó trừ khi bạn tò mò
Tôi có thể sử dụng biến Python trong truy vấn SQL không?
Làm cách nào để chuyển tên bảng dưới dạng biến trong SQL Python?
Làm cách nào để lưu trữ truy vấn SQL trong một biến trong Python?
Bạn có thể sử dụng một biến trong câu lệnh SQL không?

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

0

Cá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ẽ đợi

Trong 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à tham số 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


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

0 sau đó là dấu ngoặc tròn


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

1 và dấu cuối cùng


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

2. Dễ dàng quên đi


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

2 cuối cùng đó. (và nó luôn là


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

2 ngay cả khi đó là một con số hoặc một ngày tháng)

import psycopg2
from psycopg2.extras import RealDictCursor

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:

        min_capacity = input("What is the minimum capacity needed? ")

        param_dict = { "min_cap": min_capacity }

        # The placeholder format is %(keyname)s where keyname is the
        # appropriate key in the param_dict. This differs from .format
        # where the placeholder would have been {keyname}
        sql_placeholder = """
                          SELECT * 
                          FROM venues 
                          WHERE capacity >= %(min_cap)s
                          """
        # Then we pass the param_dict along with the query
        # string to cursor.execute
        cursor.execute(sql_placeholder, param_dict)

        for row in cursor:
            print(row)

Chúng tôi cũng có thể sử dụng điều này với nhiều phím trong chính tả và thật thuận tiện để


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

5 dữ liệu. Đây là những gì bạn sẽ sử dụng trong dự án của bạ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


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

Trườ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 gán vì cột


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

6 được khai báo là SERIAL. Chúng ta sẽ tìm hiểu cách lấy lại id đã tạo sau

venues = [{'capacity': 700, 'name': 'AMD'},
          {'capacity': 2000, 'name': 'Honda'},
          {'capacity': 2300, 'name': 'Austin Kiddie Limits'},
          {'capacity': 2000, 'name': 'Austin Ventures'}]

sql_placeholder = """
                  INSERT INTO venues(
                                  name, 
                                  capacity
                              ) VALUES (
                                  %(name)s, 
                                  %(capacity)s
                              )
                  """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        
        for row in venues:
            cursor.execute(sql_placeholder, row)

Chú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


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

6 đượ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


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

9 vào truy vấn


new_venue_params = {"new_name": "Rocking new venue2",
                    "new_capacity": 10000 }

new_venue_sql = """
                INSERT INTO venues (
                                name,
                                capacity
                            ) VALUES (
                                %(new_name)s, 
                                %(new_capacity)s
                            )
                """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(new_venue_sql, new_venue_params)

        get_venues_sql = "SELECT * FROM venues"
        cursor.execute(get_venues_sql)
        for row in cursor:
            print(row)

5 của mình

venues = [{'capacity': 700, 'name': 'AMD'},
          {'capacity': 2000, 'name': 'Honda'},
          {'capacity': 2300, 'name': 'Austin Kiddie Limits'},
          {'capacity': 2000, 'name': 'Austin Ventures'}]

sql_placeholder = """
                  INSERT INTO venues(
                                  name, 
                                  capacity
                              ) VALUES (
                                  %(name)s, 
                                  %(capacity)s
                              )
                  RETURNING id
                  """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        
        for row in venues:
            cursor.execute(sql_placeholder, row)
            new_id = cursor.fetchone()['id']
            print(f"Inserted row with new id: {new_id}")

Sử 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(
            '''
            CREATE TABLE students (
                id SERIAL PRIMARY KEY,
                name TEXT,
                age INTEGER
            )
            '''
        )

students = [{'age': 700, 'name': 'student1'},
          {'age': 2000, 'name': 'student2'},
          {'age': 2300, 'name': 'Student3'}]

sql_placeholder = """
                  INSERT INTO students(
                                  name, 
                                  age
                              ) VALUES (
                                  %(name)s, 
                                  %(age)s
                              )
                  RETURNING id
                  """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        
        for row in students:
            cursor.execute(sql_placeholder, row)
            new_id = cursor.fetchone()['id']
            print(f"Inserted row with new id: {new_id}")

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute(
            '''
            SELECT * FROM students
            '''
        )
    
        for row in cursor:
            print(row)

Screencast 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 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”.

venues = [{'capacity': 700, 'name': 'AMD'},
          {'capacity': 2000, 'name': 'Honda'},
          {'capacity': 2300, 'name': 'Austin Kiddie Limits'},
          {'capacity': 2000, 'name': 'Austin Ventures'}]

sql_placeholder = """
                  INSERT INTO venues(
                                  name, 
                                  capacity
                              ) VALUES (
                                  %(name)s, 
                                  %(capacity)s
                              )
                  """

with psycopg2.connect(host='localhost', dbname='class_music_festival') as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        
        for row in venues:
            cursor.execute(sql_placeholder, row)

1

Phong 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.

sql = "UPDATE performances SET venue_id = {} WHERE id = {}".format(new_venue_id, 40)
cursor.execute(sql)

Nế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” . tẩu thoát

Tôi có thể sử dụng biến Python trong truy vấn SQL không?

Ví dụ để chèn dữ liệu vào bảng MySQL bằng Truy vấn được tham số hóa. Đôi khi bạn cần chèn một biến Python làm giá trị cột trong truy vấn chèn . Ví dụ: người dùng đã điền vào biểu mẫu trực tuyến và nhấp vào gửi. Vì vậy, bạn cần chèn các giá trị đó vào bảng MySQL.

Làm cách nào để chuyển tên bảng dưới dạng biến trong SQL Python?

Nếu bạn muốn chuyển tên bảng dưới dạng biến và bạn có một số kiến thức cơ bản về câu lệnh sql động, bạn có thể tham khảo bên dưới. .

KHAI BÁO @SQL NVARCHAR(tối đa)

KHAI BÁO @Tablename VARCHAR(50)

ĐẶT @Tablename='tem_test_old'

SET @SQL=N'INSERT INTO Temp_table_new

Làm cách nào để lưu trữ truy vấn SQL trong một biến trong Python?

fetchall() truy xuất TẤT CẢ kết quả từ truy vấn của bạn, chúng tôi sẽ đặt chúng vào một biến có tên là rows. Sau đó, chúng tôi tạo một trình vòng lặp (điều bạn đã cố gắng thực hiện với vòng lặp while) bằng cách thực hiện cho hàng trong hàng. Sau đó, chúng tôi chỉ cần in từng hàng

Bạn có thể sử dụng một biến trong câu lệnh SQL không?

Bạn không thể sử dụng một biến trong mệnh đề IN - bạn cần sử dụng SQL động hoặc sử dụng một hàm (TSQL hoặc CLR) để chuyển đổi danh sách các giá trị thành một .

programming python