title: Select string from QComboBox and insert ID into SQLite db
body: <p>I have a simple form containing a combobox and a button "Save". The drop-down list of the combobox displays: "Choose color", "Red", "Green", "Blue". The "Save" button saves the user's choice from the combobox into an underlying database table, <code>tbl_palette</code>.</p>
  
  <p>I also have a lookup table, <code>tlkp_colors</code>, which contains  a list of colors and their respective ids. I use this lookup table to populate the combo box.</p>
  
  <p>The complete code of this oversimplified example is shown below (it can be run as is):</p>
  
  <pre class="lang-py prettyprint-override"><code>import sys
  import os.path
  import sqlite3
  
  ######### Qt DESIGNER AUTO GENERATED CODE ##########
  
  from PyQt5 import QtCore, QtGui, QtWidgets
  
  class Ui_MainWindow(object):
      def setupUi(self, MainWindow):
          MainWindow.setObjectName("MainWindow")
          MainWindow.resize(407, 240)
          self.centralwidget = QtWidgets.QWidget(MainWindow)
          self.centralwidget.setObjectName("centralwidget")
          self.comboBox = QtWidgets.QComboBox(self.centralwidget)
          self.comboBox.setGeometry(QtCore.QRect(100, 60, 201, 22))
          self.comboBox.setObjectName("comboBox")
          self.pushButton = QtWidgets.QPushButton(self.centralwidget)
          self.pushButton.setGeometry(QtCore.QRect(150, 130, 101, 28))
          self.pushButton.setObjectName("pushButton")
          MainWindow.setCentralWidget(self.centralwidget)
          self.menubar = QtWidgets.QMenuBar(MainWindow)
          self.menubar.setGeometry(QtCore.QRect(0, 0, 407, 26))
          self.menubar.setObjectName("menubar")
          MainWindow.setMenuBar(self.menubar)
          self.statusbar = QtWidgets.QStatusBar(MainWindow)
          self.statusbar.setObjectName("statusbar")
          MainWindow.setStatusBar(self.statusbar)
  
          self.retranslateUi(MainWindow)
          QtCore.QMetaObject.connectSlotsByName(MainWindow)
  
      def retranslateUi(self, MainWindow):
          _translate = QtCore.QCoreApplication.translate
          MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
          self.pushButton.setText(_translate("MainWindow", "Save"))
  
  ######### END OF AUTO GENERATED CODE ###############
  
  DIR_PATH = os.path.dirname(os.path.abspath(__file__))
  DB_NAME = 'test.db'
  target_db = os.path.join(DIR_PATH, DB_NAME)
  
  class Database:
  
      def __init__(self):
          # create database 'test.db', add schema, then close it
          cnn = sqlite3.connect(target_db)
          c = cnn.cursor()
          c.executescript("""
                              DROP TABLE IF EXISTS tlkp_colors;
                              CREATE TABLE tlkp_colors
                              (
                                  color_id INTEGER PRIMARY KEY,
                                  color_name TEXT
                              );
  
                              DROP TABLE IF EXISTS tbl_palette;
                              CREATE TABLE tbl_palette
                              (
                                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                                  color NUMBER
                              );
  
                              INSERT INTO tlkp_colors VALUES (0, 'Choose color');
                              INSERT INTO tlkp_colors VALUES (10, 'Red');
                              INSERT INTO tlkp_colors VALUES (20, 'Green');
                              INSERT INTO tlkp_colors VALUES (30, 'Blue');
                          """)
          cnn.commit()
          cnn.close()
  
      def save(self, your_choice):
          data_tuple = (your_choice,)
          sqlite_insert_query = """INSERT INTO tbl_palette (color) VALUES (?)"""
          db = sqlite3.connect(target_db)
          c = db.cursor()
          c.execute(sqlite_insert_query, data_tuple )
          db.commit()
          db.close()    
  
      def close_db(self):
          target_db.close()
  
  def populate_cbo_from_tlkp_table():
      cnn = sqlite3.connect(target_db)
      c = cnn.cursor()
      c.execute("SELECT color_name FROM tlkp_colors")
      list_of_strings = [item[0] for item in c.fetchall()]
      cnn.commit()
      cnn.close()
      return  list_of_strings
  
  
  # comment the following line after the first run (db created already)
  db = Database()    # create a new instance Database
  
  app = QtWidgets.QApplication(sys.argv)
  MainWindow = QtWidgets.QMainWindow()
  ui = Ui_MainWindow()
  ui.setupUi(MainWindow)
  
  ui.comboBox.addItems(populate_cbo_from_tlkp_table())
  ui.pushButton.clicked.connect(lambda : db.save(ui.comboBox.currentIndex()))
  
  MainWindow.show()
  sys.exit(app.exec_())
  
  
  if __name__ == "__main__":
      import sys
      app = QtWidgets.QApplication(sys.argv)
      MainWindow = QtWidgets.QMainWindow()
      ui = Ui_MainWindow()
      ui.setupUi(MainWindow)
      MainWindow.show()
      sys.exit(app.exec_())
  </code></pre>
  
  <p>When I click on the "Save" button, I want the id of the color to be saved in the underlying table <code>tbl_palette</code>, not the color itself - i.e. I want <code>10</code> NOT <code>"Red"</code>.</p>
  
  <p>I would be very thankful if you could explain how to change the code to do just that. I have searched online, but nothing I have seen so far helped me to solve my problem. I am new to Qt Designer and it seems that I am missing something.</p>