tahhLVG7

1def create_connection(db_file):
2    """ create a database connection to a SQLite database """
3    # http://www.sqlitetutorial.net/sqlite-python/creating-database/
4    import sqlite3
5    from sqlite3 import Error
6
7    try:
8        conn = sqlite3.connect(db_file)
9        print(sqlite3.version) # yields pysqlite version
10        print(sqlite3.sqlite_version) # yields sqlite version
11    except Error as e:
12        print(e)
13    finally:
14        conn.close()
15
16
17###
18def create_table(conn, create_table_sql):
19    """ create a table from the create_table_sql statement
20    :param conn: Connection object
21    :param create_table_sql: a CREATE TABLE statement
22    :return:
23    """
24    # http://www.sqlitetutorial.net/sqlite-python/create-tables/
25    import sqlite3
26    from sqlite3 import Error
27
28    try:
29        c = conn.cursor()
30        c.execute(create_table_sql)
31    except Error as e:
32        print(e)
33###
34	
35sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
36                                        id integer PRIMARY KEY,
37                                        name text NOT NULL,
38                                        begin_date text,
39                                        end_date text
40                                    ); """
41sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
42                                    id integer PRIMARY KEY,
43                                    name text NOT NULL,
44                                    priority integer,
45                                    status_id integer NOT NULL,
46                                    project_id integer NOT NULL,
47                                    begin_date text NOT NULL,
48                                    end_date text NOT NULL,
49                                    FOREIGN KEY (project_id) REFERENCES projects (id)
50                                );"""
51
52###
53import sqlite3
54db_path = r'C:UsersmynameDocumentsWorkpythonsqlite2.db'
55if __name__ == '__main__':
56    create_connection(db_path)
57
58conn = sqlite3.connect(db_path)
59if conn is not None:
60    conn.execute('pragma foreign_keys=ON')
61    # create projects table
62    create_table(conn, sql_create_projects_table)
63    # create tasks table
64    create_table(conn, sql_create_tasks_table)
65else:
66    print("Error! cannot create the database connection.")
67
68from eralchemy import render_er
69    render_er("sqlite:///C:\Users\myname\Documents\Work\pythonsqlite2.db", 'ex2_erd_from_sqlite.png')
70###