Python sqlite3 database with varying number of columns?

Question

When using pythons sqlite3 module, if I were to create a table and the first row had say 4 columns would the next row have to have 4 columns or could I have more/less?

I'm looking to create a database of vocabulary words. Each word may have a varying number of definitions.

For example 'set' would have many more definitions than 'panacea'.

I would work this vocabulary database in with a scraper that could easily look up the word and definition from a dictionary-reference site.

#! /usr/bin/env python
import mechanize
from BeautifulSoup import BeautifulSoup
import sys
import sqlite3

def dictionary(word):
    br = mechanize.Browser()
    response = br.open('http://www.dictionary.reference.com')
    br.select_form(nr=0)
    br.form['q'] = word 
    br.submit()
    definition = BeautifulSoup(br.response().read())
    trans = definition.findAll('td',{'class':'td3n2'})
    fin = [i.text for i in trans]
    query = {}
    for i in fin: 
        query[fin.index(i)] = i

    ## The code above is given a word to look up and creates a 'dict' of its definiton from the site.

    connection = sqlite3.connect('vocab.db')
    with connection:
        spot = connection.cursor()

        ## This is where my uncertainty is.  I'm not sure if I should iterate over the dict values and 'INSERT' for each definition or if there is a way to put them in all at once? 

        spot.execute("CREATE TABLE Words(Name TEXT, Definition TEXT)")
        spot.execute("INSERT INTO Words VALUES(word, Definition (for each number of definitions))")

    return query


print dictionary(sys.argv[1])

This isn't an assignment but, more of a personal exercise for learning sqlite3.

themel · Accepted Answer · 2012-08-13 06:30:08Z

4

Your design goes against the spirit of relational databases (where Wikipedia defines a relation as "a set of tuples that have the same attributes"), of which sqlite is one.

The appropriate design here is a table for words and a table for definitions, linked by a foreign key. If your word has no other attributes besides its content, you can get by with skipping the words table and just using the keys from the definitions table.

Note, however, that you'll have one row per definition, not one per word.

answered Aug 13, 2012 at 6:30

themel

8,9152 gold badges34 silver badges32 bronze badges

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

3 Comments

tijko Over a year ago

Thanks for responding first off! With this technique, Tichodroma called it a '1-to-n' relation database, thats what this is? What I'm picturing is one table containing 'Words' with Primary Keys matching to another table with 'definitions' each having its own row with a Primary Key matching the word in the first table?

tijko Over a year ago

My comments took awhile because my internet connection was dropping out.

themel Over a year ago

I think @Tichodroma's answer sums up pretty neatly what I meant.

score 3 · Accepted Answer · 2012-08-13 06:54:28Z

3

if I were to create a table and the first row had say 4 columns would the next row have to have 4 columns or could I have more/less?

You can't create a table where the rows have different numbers of cells in SQLite. You can put Null into a row's cell, though.

Perpahs you need a 1-to-n relation: each word can have many definitions.

Edit:

Take a look at this diagram with two tables, Word and Definiton:

                +------------+
+-------+       | Definition |
| Word  |       +------------+
+-------+       | id PK      |
| id PK |-1---*-| word_id FK |
| text  |       | text       |
+-------+       +------------+

In both tables, PK is the primary key of the table. FK marks a foreign key, that is a column that references a PK of a different table. In this diagram, the FK word_id in Definiton references the PK id of Word. This relation is symbolized by the -1---*- connection between the two rows.

edited Aug 13, 2012 at 6:54

answered Aug 13, 2012 at 6:26

user647772

4 Comments

tijko Over a year ago

I not clear on what you mean(not much experience). You are saying I could create rows based on the maximum number of definitions? Where some 'words' would have a bunch of 'Null' cells?

user647772 Over a year ago

You could do this but I advise against it. Use a 1-to-n relation as described in my or themel's answer.

user647772 Over a year ago

Take a look at my edited answer for the concept of the 1-to-n relation.

tijko Over a year ago

Why not use just one key to match each other? Or should I always use keys for every row when creating a table?

Collectives™ on Stack Overflow

Python sqlite3 database with varying number of columns?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related