mysql-cli-workbench-python/main.py at master · muleyashutosh/mysql-cli-workbench-python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
import mysql.connector
from dotenv import load_dotenv
import os

load_dotenv()

def connectDB(database):
    user = 'muleyashutosh'
    password = os.getenv('mySQL_muleyashutosh_password')
    #print(password)
    host = 'localhost'
    database = database
    conn = mysql.connector.connect(host = host,
                                    user = user,
                                    password = password,
                                    database = database)
    return conn

def showTables(conn):
    curr = conn.cursor()
    query = 'SHOW TABLES;'
    curr.execute(query)
    return curr.fetchall()

def dropTable(conn, tablename):
    query = 'DROP TABLE ' + tablename
    curr = conn.cursor()
    curr.execute(query)

# SELECT atribute1,atribute2,... FROM tablename WHERE ... = ... ;
def selectFrom(tablename, conn, attributes, whereClause, key):
    key = " " + key + " "

    if len(attibutes) == 0:
        attr = '*'
    else:
        attr = ",".join(attributes)

    whereClause = [ k + ' = "' + v + '"' for k,v in whereClause.items() ]

    if len(whereClause) == 0:
        where = ";"
    else:
        where = " WHERE " + key.join(whereClause) + ';'
    search = 'SELECT ' + attr + ' FROM ' + tablename + where

    #print(search)
    curr = conn.cursor()
    curr.execute(search)
    return curr.fetchall()


#DELETE FROM tablename WHERE
def deleteFrom(tablename, conn, whereClause, key):
    key = ' ' + key + ' '

    where = [k + ' = "' + v + '"'for k,v in whereClause.items()]
    where = key.join(where)
    query = 'DELETE FROM ' + tablename + ' WHERE ' + where
    #print(query)
    curr = conn.cursor()
    curr.execute(query);
    conn.commit()

# UPDATE tablename SET ... = ... WHERE ... = ... ;
def UpdateTable(conn, tablename, updates, whereClause = {}, key = 'AND'):
    key = ' ' + key + ' '
    updates = [k + ' = "' + v + '"' for k, v in updates.items()]
    updates = ', '.join(updates)

    if whereClause :
        where  = [k + ' = "' + v + '"' for k, v in whereClause.items()]
        where = key.join(where)
        query = 'UPDATE ' + tablename + ' SET ' + updates + ' WHERE ' + where
    else :
        query = 'UPDATE ' + tablename + ' SET ' + updates

    #print(query)
    curr = conn.cursor()
    curr.execute(query)
    conn.commit()

# def createTable(conn,tablename,columns, pkey = ''):
#     columns = [ k + ' ' + v for k, v in columns.items()]
#     columns = ', '.join(columns)
#     if pkey:
#         columns += ', PRIMARY KEY('+ pkey + ')'

#     query = 'CREATE TABLE ' + tablename + ' ( ' + columns + ' );'
#     curr = conn.cursor()
#     curr.execute(query)


conn = connectDB('Sample')
tablename = 'employee'

# display tables
result = showTables(conn)
for x in result:
    print(x)
print('_______________________________')

# deleteFrom('employee', conn, where,'AND')
# print('_______________________________')

# updates = { 'firstname': 'Ashutosh', 'lastname' : 'Muley'}
# where= { 'firstname': 'Lynn', 'lastname' : 'Dennis'}
# UpdateTable(conn, tablename, updates, where)

# print('_______________________________')

# result = selectFrom(tablename, conn, attibutes, {}, 'AND')

# for x in result:
#     print(x)
 # create tables


result = showTables(conn)
for x in result:
    print(x)
print('_______________________________')