Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

112 lines (112 sloc) 4.416 kb
{
"metadata": {
"name": "SQL - ex 3"
},
"nbformat": 2,
"worksheets": [
{
"cells": [
{
"cell_type": "code",
"collapsed": false,
"input": [
"import sqlite3",
"import csv",
"",
"# Create a database in memory",
"db = sqlite3.connect(\":memory:\")",
"c = db.cursor()",
"",
"c.execute(\"CREATE TABLE states (id INT PRIMARY KEY, name STRING(30), UNIQUE(name))\")",
"c.execute(\"\"\"CREATE TABLE cities (",
" id INT PRIMARY KEY,",
" name STRING(30),",
" state_id INTEGER,",
" population INTEGER,",
" FOREIGN KEY(state_id) REFERENCES states(id))\"\"\")",
"# sqlite does not enable foreign key constraints by default",
"c.execute(\"PRAGMA foreign_keys = ON;\")"
],
"language": "python",
"outputs": [
{
"output_type": "pyout",
"prompt_number": 21,
"text": [
"<sqlite3.Cursor at 0x101f93ce0>"
]
}
],
"prompt_number": 21
},
{
"cell_type": "code",
"collapsed": true,
"input": [
"with open(\"states_only.csv\") as fid:",
" reader = csv.reader(fid)",
" for i, (state,) in enumerate(reader):",
" c.execute(\"INSERT INTO states VALUES(?, ?)\", (i, state))",
"with open(\"cities_only.csv\") as fid:",
" reader = csv.reader(fid)",
" for i, (name, state_id, population) in enumerate(reader):",
" c.execute(\"INSERT INTO cities VALUES(?, ?, ?, ?)\", (i, name, state_id, population))"
],
"language": "python",
"outputs": [],
"prompt_number": 22
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"c.execute(\"INSERT INTO cities (name, state_id, population) VALUES('Honolulu', 32, 955775)\")"
],
"language": "python",
"outputs": [
{
"ename": "IntegrityError",
"evalue": "foreign key constraint failed",
"output_type": "pyerr",
"traceback": [
"<span class=\"ansired\">---------------------------------------------------------------------------</span>\n<span class=\"ansired\">IntegrityError</span> Traceback (most recent call last)",
"<span class=\"ansigreen\">/Users/david/Documents/Pydata/notebooks/&lt;ipython-input-23-ed48eb16b30f&gt;</span> in <span class=\"ansicyan\">&lt;module&gt;</span><span class=\"ansiblue\">()</span>\n<span class=\"ansigreen\">----&gt; 1</span><span class=\"ansired\"> </span>c<span class=\"ansiblue\">.</span>execute<span class=\"ansiblue\">(</span><span class=\"ansiblue\">&quot;INSERT INTO cities (name, state_id, population) VALUES(&apos;Honolulu&apos;, 32, 955775)&quot;</span><span class=\"ansiblue\">)</span><span class=\"ansiblue\"></span>\n",
"<span class=\"ansired\">IntegrityError</span>: foreign key constraint failed"
]
}
],
"prompt_number": 23
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"c.execute(\"INSERT INTO states (name) VALUES('Tex.')\")"
],
"language": "python",
"outputs": [
{
"ename": "IntegrityError",
"evalue": "column name is not unique",
"output_type": "pyerr",
"traceback": [
"<span class=\"ansired\">---------------------------------------------------------------------------</span>\n<span class=\"ansired\">IntegrityError</span> Traceback (most recent call last)",
"<span class=\"ansigreen\">/Users/david/Documents/Pydata/notebooks/&lt;ipython-input-24-55489b4c933b&gt;</span> in <span class=\"ansicyan\">&lt;module&gt;</span><span class=\"ansiblue\">()</span>\n<span class=\"ansigreen\">----&gt; 1</span><span class=\"ansired\"> </span>c<span class=\"ansiblue\">.</span>execute<span class=\"ansiblue\">(</span><span class=\"ansiblue\">&quot;INSERT INTO states (name) VALUES(&apos;Tex.&apos;)&quot;</span><span class=\"ansiblue\">)</span><span class=\"ansiblue\"></span>\n",
"<span class=\"ansired\">IntegrityError</span>: column name is not unique"
]
}
],
"prompt_number": 24
},
{
"cell_type": "code",
"collapsed": true,
"input": [],
"language": "python",
"outputs": [],
"prompt_number": "&nbsp;"
}
]
}
]
}
Jump to Line
Something went wrong with that request. Please try again.