如何查询FLOAT类型数组？SQLAlchemy场景下的技术问询

Alright, let's figure out how to query your SQLAlchemy array column using a Python list. I'll break this down for both the declarative model you defined and the reflected Table approach you're experimenting with.

Using the Declarative Model (create_my_array_table) #

Since you already have a declarative class for your table, this is the most straightforward way to query:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Set up your database connection (adjust credentials to match your setup)
engine = create_engine('postgresql://your_user:your_password@your_host/your_db')
Session = sessionmaker(bind=engine)
session = Session()

# Your target Python list
v = [0.0112, 0.235, 0.25]

# Perform an exact match query
matching_rows = session.query(create_my_array_table).filter(create_my_array_table.myarray == v).all()

# Iterate over results to access data
for row in matching_rows:
    print(f"ID: {row.id}, Array: {row.myarray}")

Using the Reflected Table Object #

If you're using table reflection (the Table(..., autoload_with=engine) approach you mentioned), here's how to adjust the query:

from sqlalchemy import Table, MetaData, create_engine

engine = create_engine('postgresql://your_user:your_password@your_host/your_db')
metadata = MetaData()

# Reflect your existing table
myDBtable = Table(mytablename, metadata, autoload_with=engine)

# Query using a database connection
with engine.connect() as conn:
    v = [0.0112, 0.235, 0.25]
    # Exact match filter
    query = myDBtable.select().where(myDBtable.c.myarray == v)
    results = conn.execute(query).fetchall()

    # Process results (rows are tuples; access columns by name or index)
    for row in results:
        print(f"ID: {row['id']}, Array: {row['myarray']}")

Important Notes to Avoid Headaches #

Float Precision Issues #

Exact matches with floating-point numbers can fail because of tiny precision differences (e.g., the database stores 0.0112000001 but your list has 0.0112). To handle this, you can:

• Round values to a fixed decimal place before comparing:

  from sqlalchemy import func
  
  # Round array elements to 4 decimal places for comparison
  matching_rows = session.query(create_my_array_table).filter(
      func.array_to_string(create_my_array_table.myarray, ',') == func.array_to_string(v, ',')
  ).all()
  

• Or check that each element's difference is within an acceptable tolerance:

  condition = (
      # First ensure arrays are the same length
      func.array_length(create_my_array_table.myarray, 1) == len(v)
      # Then check all elements are within 1e-6 of each other
      & func.all(func.abs(func.unnest(create_my_array_table.myarray) - func.unnest(v)) < 1e-6)
  )
  matching_rows = session.query(create_my_array_table).filter(condition).all()
  

Partial Matches #

If you don't need an exact match, but want rows where the array contains all elements of your list (regardless of order), use PostgreSQL's @> operator:

# Match rows where myarray includes every element in v
matching_rows = session.query(create_my_array_table).filter(create_my_array_table.myarray.op('@>')(v)).all()

内容的提问来源于stack exchange，提问作者pajamas