Home > Net >  How to write an SQL query to get all the unique IDs in this table corresponding to a name?
How to write an SQL query to get all the unique IDs in this table corresponding to a name?

Time:11-18

Sl No ID Name
1 145928 Jake
2 458921 Abel
3 987468 Jake
4 145928 Drake
5 897426 Jake
6 448961 Jake

Hi, I am trying to get all the IDs corresponding to the name Jake in this table. I want to collect these numbers and store it as a python list. Please tell me how I can achieve this with mysql-connect and python.

CodePudding user response:

import mysql.connector

# Database helper class to connect, to disconnect and to make queries
class Database:
    def __init__(self, host, user, password, db):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = mysql.connector.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.db
        )
        self.cursor = self.connection.cursor()

    def disconnect(self):
        self.cursor.close()
        self.connection.close()

    def query(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()

# Connect to database
db = Database('localhost', 'user', 'password', 'database')
db.connect()

# Query database
result = db.query('SELECT * FROM users WHERE name = "Jake"')
for row in result: 
    # Make something with the result
    print(dict(row))
    
# Disconnect from database
db.disconnect()
  • Related