NvSM3EtE

1import sqlite3
2from sqlite3 import Error
3 
4 
5def create_connection(db_file):
6    """ create a database connection to the SQLite database
7        specified by db_file
8    :param db_file: database file
9    :return: Connection object or None
10    """
11    conn = None
12    try:
13        conn = sqlite3.connect(db_file)
14        return conn
15    except Error as e:
16        print(e)
17 
18    return conn
19 
20 
21def create_table(conn, create_table_sql):
22    """ create a table from the create_table_sql statement
23    :param conn: Connection object
24    :param create_table_sql: a CREATE TABLE statement
25    :return:
26    """
27    try:
28        c = conn.cursor()
29        c.execute(create_table_sql)
30    except Error as e:
31        print(e)
32  
33def create_user(conn, user):
34    """
35    Create a new user into users table
36    :param conn:
37    :param user:
38    :return: user id
39    """
40    sql = ''' INSERT INTO ranks(id,xp,lvl)
41              VALUES(?,?,?) '''
42    cur = conn.cursor()
43    cur.execute(sql, user)
44    return cur.lastrowid
45
46def create_guild(conn, guild):
47    """
48    Create a new guild table
49    :param conn:
50    :param guild:
51    :return:
52    """
53 
54    sql = ''' INSERT INTO guilds(id,prefix)
55              VALUES(?,?) '''
56    cur = conn.cursor()
57    cur.execute(sql, guild)
58    return cur.lastrowid
59
60def update_user(conn, user):
61    """
62    update xp and lvl of user
63    :param conn:
64    :param task:
65    :return: project id
66    """
67    sql = ''' UPDATE ranks
68              SET xp = ? ,
69                  lvl = ? 
70              WHERE id = ?'''
71    cur = conn.cursor()
72    cur.execute(sql, user)
73    conn.commit()
74
75def select_user(conn, user):
76    """
77    Get user xp and lvl in a array
78    :param conn: the Connection object
79    :param user:
80    :return xp, lvl:
81    """
82    cur = conn.cursor()
83    sql = """SELECT * FROM ranks"""
84    cur.execute(sql)
85 
86    rows = cur.fetchall()
87
88    xpandlvl = []
89    #print('cur: ', cur)
90    for row in rows:
91        xpandlvl.append(row)
92        #print(row)
93
94    print(xpandlvl)
95    return xpandlvl
96
97def check_user(conn, user):
98    """
99    Checks if user exists
100    :param conn: the Connection object
101    :param user:
102    :return boolean:
103    """
104    cur = conn.cursor()
105    cur.execute("SELECT ? FROM ranks", (user,))
106 
107    rows = cur.fetchall()
108
109    print('k', rows)
110
111    try:
112        if rows[1]:
113           return bool(rows[1])
114        else:
115            return False
116    except Exception as e:
117      print('Nope.', e)
118      return False
119 
120def connect():
121    database = r"database.db"
122 
123    sql_create_ranks_table = """ CREATE TABLE IF NOT EXISTS ranks (
124                                        id integer PRIMARY KEY,
125                                        xp integer NOT NULL,
126                                        lvl integer NOT NULL
127                                    ); """
128 
129    sql_create_guilds_table = """CREATE TABLE IF NOT EXISTS guilds (
130                                    id integer PRIMARY KEY,
131                                    prefix text NOT NULL
132                                );"""
133 
134    # create a database connection
135    conn = create_connection(database)
136 
137    # create tables
138    if conn is not None:
139        # create ranks table
140        create_table(conn, sql_create_ranks_table)
141 
142        # create guilds table
143        create_table(conn, sql_create_guilds_table)
144
145    else:
146        print("Error! cannot create the database connection.")
147
148def getConn():
149    database = r"database.db"
150
151    try:
152    # create a database connection
153        conn = create_connection(database)
154        with conn:
155          return conn
156    except Error as e:
157        print(e)
158 
159 
160if __name__ == '__main__':
161    connect()