Hi, i am working on this project and i have the following question along the way,
How do we check if a table has a pirmary key?
How do we change a pirmary key of a table from column A to column B after creation.
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)
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.
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:
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’.
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.
Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT … FROM X.
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
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.
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.
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.
Commit the transaction started in step 2.
If foreign keys constraints were originally enabled, reenable them now.