searchable columns with sqlalchemy and postgres

Question

I am a beginner with databases and I am not sure to understand them properly.

As far as I understand given a table with several columns I can make queries like: SELECT * FROM table WHERE col1>3.

This query has complexity N. In order to make the search more efficient I can use col1 as index. In this case the same query should have complexity log(N).

Now as far as I understood a column is made searchable in sqlalchemy setting it as a primary key.

If this is correct I do not understand why I am not able to set columns with duplicates as primary key.

For example:

import sqlite3                                                                                                                                                                                              
from sqlalchemy import *                                                                                                                                                                                    


metadata = MetaData()                                                                                                                                                                                       
table = Table('example', metadata,                                                                                                                                                                          
              Column('col1', Integer, primary_key=True),                                                                                                                                                    
              Column('col2', Integer))                                                                                                                                                                      

engine = create_engine('sqlite:///:memory:', echo=True)                                                                                                                                                     
con = engine.connect()                                                                                                                                                                                      

table.create(engine, checkfirst=True)                                                                                                                                                                       


data = [{'col1':1, 'col2':2}, {'col1':3, 'col2':4},  {'col1':3, 'col2':4}]                                                                                                                                  
ins = table.insert().values(data)                                                                                                                                                                           
con.execute(ins)                                                                                                                                                                                            


print list(con.execute("SELECT * FROM example"))                                                                                                                                                            



          returns

IntegrityError: (sqlite3.IntegrityError) PRIMARY KEY must be unique [SQL: u'INSERT INTO example (col1, col2) VALUES (?, ?), (?, ?), (?, ?)'] [parameters: (1, 2, 3, 4, 3, 4)]

How can I make a non unique column searchable in log(N)?

EDIT: The example is written using sqlite but I am actually working with postgres .

That should be "Now as far as I understood a column is made searchable in SQL setting it as a primary key." SQLAlchemy is just a tool for using SQL databases. — Ilja Everilä
– Ilja Everilä, Commented Apr 23, 2018 at 6:10

Blender · Accepted Answer · 2018-04-22 23:09:52Z

1

Now as far as I understood a column is made searchable in sqlalchemy setting it as a primary key.

A primary key column is automatically indexed, but you frequently need to index non-unique columns. You'd do this with the index keyword argument:

table = Table('example', metadata,
    Column('col1', Integer, index=True),
    Column('col2', Integer)
)

You can see in the log file the corresponding SQL:

INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_example_col1 ON example (col1)

answered Apr 22, 2018 at 23:09

Blender

300k55 gold badges462 silver badges511 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

searchable columns with sqlalchemy and postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related