SQLite questions

Hi, i am working on this project and i have the following question along the way,

  1. How do we check if a table has a pirmary key?
  2. How do we change a pirmary key of a table from column A to column B after creation.
  3. How do we prevent the insert data is not repeated? i know there is an option for unique which returns error when there is identical row, but is there anyway that we can have multiple execution of a cell in juypter notebook, yet still return the same and unique data like the first execution?

I achieve it by dropping the table before creating the table,

run_command('''drop table if exists league''') # for uniqueness.
run_command(create_tab_league)
run_command(insert_data_league)

here is the full code:

create_tab_league = '''
create table if not exists league (
league_id text pirmary key,
name text)
'''

insert_data_league = '''
insert or ignore into league
(league_id, name)
values
('NL', 'National Leauge'),
('AL', 'American League'),
('AA', 'American Association'),
('FL', 'Federal League'),
('PL', 'Player League'),
('UA', 'Union Association')'''

q = '''
select * from league'''
run_command('''drop table if exists league''') # for uniqueness.
run_command(create_tab_league)
run_command(insert_data_league)

Is there any other more pythonic way to do so?

Thanks.

Expand to see answer to this question

We can use table_info for that. Here’s an example that runs at the end of the given solution for that project:

game_info = run_query("PRAGMA table_info(game);")

This returns the dataframe game_info that looks like

cid name type notnull dflt_value pk
0 0 game_id TEXT 0 None 1
1 1 date TEXT 0 None 0
2 2 number_of_game INTEGER 0 None 0
3 3 park_id TEXT 0 None 0
4 4 length_outs INTEGER 0 None 0
5 5 day BOOLEAN 0 None 0
6 6 completion TEXT 0 None 0
7 7 forefeit TEXT 0 None 0
8 8 protest TEXT 0 None 0
9 9 attendance INTEGER 0 None 0
10 10 legnth_minutes INTEGER 0 None 0
11 11 additional_info TEXT 0 None 0
12 12 acquisition_info TEXT 0 None 0

The pk column holds the answer. From the documentation linked above:

The “pk” column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Expand to see answer to this question

We don’t, SQLite doesn’t allow this. You need to create the primary key at creation time.

Other databases typically allow you to do this with the ALTER statement applied to the table object, coupled with ADD CONSTRAINT and DROP CONSTRAINT. See for instances Postgres’ ALTER TABLE's documentation.

SQLite’s ALTER TABLE doesn’t allow this:

The only schema altering commands directly supported by SQLite are the “rename table”, “rename column”, and “add column” commands shown above.

(Source).

If you want to change it, you need to create a new table with the same data and then rename it appropriately. A sequence of steps is suggested in the source, just below the quotation above.

The steps to make arbitrary changes to the schema design of some table X
are as follows:

  1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.

  2. Start a transaction.

  3. Remember the format of all indexes, triggers, and views associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name=‘X’.

  4. Use CREATE TABLE to construct a new table “new_X” that is in the desired revised format of table X. Make sure that the name “new_X” does not collide with any existing table name, of course.

  5. Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT … FROM X.

  6. Drop the old table X: DROP TABLE X.

  7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

  8. Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.

  9. If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.

  10. If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.

  11. Commit the transaction started in step 2.

  12. If foreign keys constraints were originally enabled, reenable them now.

You can use a try:.. except:.. finally:.. block, in addition to the UNIQUE constraint.

1 Like