Python MySql API

Question

I am creating an API for mysql using python. I am trying to get multiple columns. In SQL query, you would generally do something like SELECT col1, col2.... FROM tablename; My python API for the same looks something like this:

def get_multiple_columns(self, tablename, limit, *colname):
        sql = 'Select {} From {} limit {}'.format(*colname, tablename, limit)
        return self.query_exec_fetch_res(sql)

For reference, I have attached the rest of the code:

class database(object):
    def __init__(self, host = MYSQL_DB_IP,
                       port = MYSQL_DB_PORT,
                       username = MYSQL_DB_USERNAME,
                       password = MYSQL_DB_PASSWORD,
                       dbname = MYSQL_DB_TRADING):
        self.dbname = dbname
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.session = None
        self.cursor  = None
        self.connect_to_db()

    # Function to connect to database.     
    def connect_to_db(self):

        if not self.session:
            try:
                self.session = pymysql.connect(host=self.host,
                                               port=self.port,
                                               user=self.username,
                                               passwd=self.password,
                                               db=self.dbname)
                self.cursor = self.session.cursor()
            except Exception as e:
                print("database init failed \n{}".format(e))

    def get_db_client(self):
        return self.session

    def get_db_cursor(self):
        return self.cursor

    def disconnect_from_db(self):
        if self.session:
            self.session.close()
            self.session = None
            self.cursor  = None

    #function to execute query and fetch the result
    def query_exec_fetch_res(self,query):
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def get_mysql_version(self):
        sql = "SELECT VERSION()"
        return self.query_exec_fetch_res(sql)

    def get_all_column_info(self, tablename):
        sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \"{}\"".format(tablename)
        return self.query_exec_fetch_res(sql) 

    def get_all_column_names(self, tablename):
        result = self.get_all_column_info(tablename)
        return [row[3] for row in result]

    def get_info_where_clause(self,tablename,colname,fieldname, value):
        sql = "SELECT {} FROM {} WHERE {}=\"{}\"".format(colname, tablename, fieldname, value)
        result = self.query_exec_fetch_res(sql)
        info = [ row for row in result ]
        return info 

    def get_multiple_columns(self, tablename, limit, *colname):
        sql = 'Select {} From {} limit {}'.format(*colname, tablename, limit)
        return self.query_exec_fetch_res(sql)

All the functions work except the get_multiple_columns function. Can anybody help me with this?

Answer 1

In your method get_multiple_columns, argument colname comes in as a tuple, so you want:

sql = 'Select {} From {} limit {}'.format(', '.join(colname), tablename, limit)