1cTgHUZp

1{
2 "cells": [
3  {
4   "cell_type": "code",
5   "execution_count": 5,
6   "metadata": {
7    "collapsed": true
8   },
9   "outputs": [],
10   "source": [
11    "import sqlite3\n",
12    "\n",
13    "def create_connection(db_file):\n",
14    "    \"\"\" create a database connection to the SQLite database\n",
15    "        specified by db_file\n",
16    "    :param db_file: database file\n",
17    "    :return: Connection object or None\n",
18    "    \"\"\"\n",
19    "    try:\n",
20    "        conn = sqlite3.connect(db_file)\n",
21    "        return conn\n",
22    "    except Error as e:\n",
23    "        print(e)\n",
24    " \n",
25    "    return None"
26   ]
27  },
28  {
29   "cell_type": "code",
30   "execution_count": 7,
31   "metadata": {
32    "collapsed": false
33   },
34   "outputs": [],
35   "source": [
36    "def create_table(conn, create_table_sql):\n",
37    "    \"\"\" create a table from the create_table_sql statement\n",
38    "    :param conn: Connection object\n",
39    "    :param create_table_sql: a CREATE TABLE statement\n",
40    "    :return:\n",
41    "    \"\"\"\n",
42    "    try:\n",
43    "        c = conn.cursor()\n",
44    "        c.execute(create_table_sql)\n",
45    "    except Error as e:\n",
46    "        print(e)"
47   ]
48  },
49  {
50   "cell_type": "code",
51   "execution_count": 9,
52   "metadata": {
53    "collapsed": true
54   },
55   "outputs": [],
56   "source": [
57    "def main():\n",
58    "    database = \"pythonsqlite.db\"\n",
59    " \n",
60    "    sql_create_projects_table = \"\"\" CREATE TABLE IF NOT EXISTS projects (\n",
61    "                                        id integer PRIMARY KEY,\n",
62    "                                        name text NOT NULL,\n",
63    "                                        begin_date text,\n",
64    "                                        end_date text\n",
65    "                                    ); \"\"\"\n",
66    " \n",
67    "    sql_create_tasks_table = \"\"\"CREATE TABLE IF NOT EXISTS tasks (\n",
68    "                                    id integer PRIMARY KEY,\n",
69    "                                    name text NOT NULL,\n",
70    "                                    priority integer,\n",
71    "                                    status_id integer NOT NULL,\n",
72    "                                    project_id integer NOT NULL,\n",
73    "                                    begin_date text NOT NULL,\n",
74    "                                    end_date text NOT NULL,\n",
75    "                                    FOREIGN KEY (project_id) REFERENCES projects (id)\n",
76    "                                );\"\"\"\n",
77    " \n",
78    "    # create a database connection\n",
79    "    conn = create_connection(database)\n",
80    "    if conn is not None:\n",
81    "        # create projects table\n",
82    "        create_table(conn, sql_create_projects_table)\n",
83    "        # create tasks table\n",
84    "        create_table(conn, sql_create_tasks_table)\n",
85    "    else:\n",
86    "        print(\"Error! cannot create the database connection.\")"
87   ]
88  },
89  {
90   "cell_type": "code",
91   "execution_count": 10,
92   "metadata": {
93    "collapsed": true
94   },
95   "outputs": [],
96   "source": [
97    "if __name__ == '__main__':\n",
98    "    main()"
99   ]
100  }
101 ],
102 "metadata": {
103  "anaconda-cloud": {},
104  "kernelspec": {
105   "display_name": "Python [default]",
106   "language": "python",
107   "name": "python3"
108  },
109  "language_info": {
110   "codemirror_mode": {
111    "name": "ipython",
112    "version": 3
113   },
114   "file_extension": ".py",
115   "mimetype": "text/x-python",
116   "name": "python",
117   "nbconvert_exporter": "python",
118   "pygments_lexer": "ipython3",
119   "version": "3.5.2"
120  }
121 },
122 "nbformat": 4,
123 "nbformat_minor": 1
124}