xUj2rYWi

1#!/usr/bin/env python3
2# -*- coding: utf-8 -*-
3
4# import sqlite3
5
6import sqlite3
7
8# FIRST STEP : CREATION / CONNECTION
9try:
10    # create a connection with bdd (if not existing, created)
11    conn = sqlite3.connect("database.db")
12    # create the cursor from connection to interact with bdd
13    cursor = conn.cursor()
14except Exception as e:
15    raise(e)
16
17
18# 2* STEP : FIRST SQL REQUEST
19try:
20    # write your request
21    req = """CREATE TABLE IF NOT EXISTS users(
22                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
23                name TEXT, age INTEGER  )"""
24    # the cursor execute your request
25    cursor.execute(req)
26    # don't forget to commit :)
27    conn.commit()
28    #  of course you have to commit from conn and not from cursor :)
29except Exception as e:
30    conn.close()
31    raise(e)
32
33
34# LETS INSERT DATAS IN OUR TABLE
35try:
36    user_0 = None  #  first way
37    req = """INSERT INTO users(name, age) VALUES("test",122)"""
38    cursor.execute(req)
39    print("req 0 OK")
40
41    user_1 = ["alex", 31]  #  2* way
42    req = """INSERT INTO users(name, age) VALUES(?,?)"""
43    cursor.execute(req, user_1)
44    print("req 1 OK")
45
46    user_2 = dict(name="agnes", age=34)
47    req = """INSERT INTO users(name, age) VALUES(:name,:age)"""
48    cursor.execute(req, user_2)
49    print("req 2 OK")
50
51    user_3 = ["antoine", 2]
52    req = """INSERT INTO users(name, age) VALUES("{}", {})"""\
53        .format(user_3[0], user_3[1])
54    cursor.execute(req)
55    print("req 3 OK")
56
57
58except Exception as e:
59    print("erreur, on close la connection par securité!")
60    conn.close()
61    raise(e)
62
63
64# LETS NOW IMPORT DATAS FROM TABLE
65
66#  first one lign :
67try:
68    cursor.execute("""SELECT name, age FROM users""")
69
70    # first one lign :
71
72    # user1 = cursor.fetchone()
73    # print(user1)
74    #
75    # user2 = cursor.fetchone()
76    # print(user2)
77    #
78    # or all ligns in one time
79    # rows = cursor.fetchall()
80    # for row in rows:
81    #     print(row)
82    #
83    # age_max = 3
84    # cursor.execute("SELECT * FROM users WHERE age <=?", (age_max,))
85    # reponse = cursor.fetchall()
86    # for val in reponse:
87    #     print(val)
88
89    #  cursor is an iterator, so
90    #  reponse = cursor.fetchall() is not usefull :
91    age_max = 3
92    for val in cursor.execute("SELECT * FROM users WHERE age <=?", (age_max,)):
93        print(val)
94
95except Exception as e:
96    print("erreur, on close la connection par securité!")
97    conn.close()
98    raise(e)
99
100
101# AT THE END TRY TO CLOSE THE CONNECTION IF POSSIBLE :
102try:
103    conn.close()
104    print("OK, all Good")
105except exception as e:
106    raise(e)