sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

[gfrmin]

This code worked in the Classic version, but not in the Beta, with the error as in the title. Why?

[zarino]

Question 1: Which language are you writing in?

Question 2: Somewhere in your code, you are calling scraperwiki.sql.save() or the ruby equivalent. Could you show us that line of code? If it includes variables from earlier on, could you include the whole section, so we can see what types the variables are?

[gfrmin]

It's in Python. I thought I included the link to the scraper, but it looks like I forgot: https://beta.scraperwiki.com/dataset/eeecqfq/settings

Relevant code:

tds = root.cssselect("tr td tr td")
namestds = root.cssselect("td.data") 

data = {
        'cr' : tds[1].text_content(),
        'English Company Name' : tds[2].text_content().rsplit('\r')[1].lstrip('\n\t'),
        'Chinese Company Name' : tds[2].text_content().rpartition('\r')[2].lstrip('\r\n\t'),
        'Company Type' : tds[4].text_content()[:-1],
        'Date of incorporation' : tds[6].text_content(),
        'Company status' : tds[8].text_content()[:-1],
        'Active status' : tds[10].text_content()[:-1],
        'Remarks' : tds[11].text_content()[16:],
        'Winding up mode' : tds[13].text_content()[:-1],
        'Date of Dissolution' : tds[15].text_content(),
        'Register of Charges' : tds[17].text_content()[:-1],
        'Important Note' : tds[18].text_content()[15:].lstrip('\r\n\t'),
        'Name History' : names
        }        

scraperwiki.sqlite.save(unique_keys=['cr'], data=data)

I reiterate that this works in Classic scraperwiki. Thanks for your help!

[zarino]

Oh wow, I'm surprised this ever worked in ScraperWiki Classic.

names = {}
for namesno in range(len(namestds)):
    names["Name" + str(namesno)] = namestds[namesno].text_content()
data = {
    ...
    ...
    'Name History' : names
}

On line 37 you add a names value to your data dictionary, which is, itself, a dictionary. Your data variable ends up looking like this:

{
    'Date of incorporation': u'12-10\xe6\x9c\x88-1865', 
    'Name History': {
        'Name0': "12-10-1865BRITISH TRADERS' INSURANCE COMPANY LIMITED -THE-"
    }, 
    'Company status': 'Public and Non-listed',
    ...
    'Winding up mode': "Members' Voluntary Winding Up"
}

SQL doesn't allow you to save nested structures like this. You either have to pickle the inner dictionary into some "flat" format like JSON (using JSON.dumps() to save and JSON.loads() to read), or denormalize your table so the histories are stored in a second table joined to a particular row in the first by a common key.

ScraperWiki Classic must have been converting the types for you. Do you have a link to the original scraper on ScraperWiki Classic, so we can see how it ended up storing the "Name History" field?

Since scraperwiki-python is a compatibility layer with ScraperWiki Classic, we should fix it to match your expectations.

[gfrmin]

Interesting! Thanks for breaking it down for me. The original scraper is at https://scraperwiki.com/scrapers/hong_kong_company_records/

It seems it's currently storing a string looking like a dictionary, e.g. "{'Name0': '25-01-1926CHINA GINGER PRESERVING COMPANY, LIMITED -THE-'}". I'm happy to keep it that way. Will you make it match that behaviour?

[zarino]

Thanks for your feedback @slygent. I'm re-filing this as a bug on scraperwiki-python

https://github.com/scraperwiki/scraperwiki-python/issues/21

[stella1995]

-- coding: utf-8 --

Form implementation generated from reading ui file 'login.ui'

#

Created by: PyQt4 UI code generator 4.11.4

#

WARNING! All changes made in this file will be lost!

from PyQt4 import QtCore, QtGui from welcome import Ui_MainWindow from signup import Ui_signUp import sqlite3 try: _fromUtf8 = QtCore.QString.fromUtf8 except AttributeError: def _fromUtf8(s): return s

try: _encoding = QtGui.QApplication.UnicodeUTF8 def _translate(context, text, disambig): return QtGui.QApplication.translate(context, text, disambig, _encoding) except AttributeError: def _translate(context, text, disambig): return QtGui.QApplication.translate(context, text, disambig)

class UiDialog(object): def showMessageBox(self,title,message): msgBox = QtGui.QMessageBox() msgBox.setIcon(QtGui.QMessageBox.Warning) msgBox.setWindowTitle(title) msgBox.setText(message) msgBox.setStandardButtons(QtGui.QMessageBox.Ok) msgBox.exec() def welcomeWindowShow(self): self.welcomeWindow = QtGui.QMainWindow() self.ui = Ui_MainWindow() self.ui.setupUi(self.welcomeWindow) self.welcomeWindow.show() def signUpShow(self): self.signUpWindow = QtGui.QDialog() self.ui = Ui_signUp() self.ui.setupUi(self.signUpWindow) self.signUpWindow.show() def loginCheck(self): username = self.uname_lineEdit.text() password = self.pass_lineEdit.text()

    connection = sqlite3.connect("login.db")
    connection.execute("SELECT * FROM USERS WHERE USERNAME = ? AND PASSWORD = ?",(username,password))

    if(len(result.fetchall()) > 0):
        print("User Found ! ")
        self.welcomeWindowShow()
    else:
        print("User Not Found !")
        self.showMessageBox('Warning','Invalid Username And Password')
    connection.close()

def signUpCheck(self):
    print(" Sign Up Button Clicked !")
    self.signUpShow()
def setupUi(self, Dialog):
    Dialog.setObjectName(_fromUtf8("Dialog"))
    Dialog.resize(496, 265)
    self.u_name_label = QtGui.QLabel(Dialog)
    self.u_name_label.setGeometry(QtCore.QRect(150, 110, 71, 20))
    font = QtGui.QFont()
    font.setPointSize(10)
    font.setBold(False)
    font.setWeight(50)
    self.u_name_label.setFont(font)
    self.u_name_label.setAlignment(QtCore.Qt.AlignCenter)
    self.u_name_label.setObjectName(_fromUtf8("u_name_label"))
    self.pass_label = QtGui.QLabel(Dialog)
    self.pass_label.setGeometry(QtCore.QRect(150, 150, 71, 21))
    font = QtGui.QFont()
    font.setPointSize(10)
    self.pass_label.setFont(font)
    self.pass_label.setAlignment(QtCore.Qt.AlignCenter)
    self.pass_label.setObjectName(_fromUtf8("pass_label"))
    self.uname_lineEdit = QtGui.QLineEdit(Dialog)
    self.uname_lineEdit.setGeometry(QtCore.QRect(230, 110, 113, 20))
    self.uname_lineEdit.setObjectName(_fromUtf8("uname_lineEdit"))
    self.pass_lineEdit = QtGui.QLineEdit(Dialog)
    self.pass_lineEdit.setGeometry(QtCore.QRect(230, 150, 113, 20))
    self.pass_lineEdit.setObjectName(_fromUtf8("pass_lineEdit"))
    self.login_btn = QtGui.QPushButton(Dialog)
    self.login_btn.setGeometry(QtCore.QRect(230, 200, 51, 23))
    self.login_btn.setObjectName(_fromUtf8("login_btn"))
    ######################### Button Event ##############################3
    self.login_btn.clicked.connect(self.loginCheck)
    #####################################################################
    self.signup_btn = QtGui.QPushButton(Dialog)
    self.signup_btn.setGeometry(QtCore.QRect(290, 200, 51, 23))
    self.signup_btn.setObjectName(_fromUtf8("signup_btn"))
    ######################### Button Event ##############################3
    self.signup_btn.clicked.connect(self.signUpCheck)
    #####################################################################
    self.label = QtGui.QLabel(Dialog)
    self.label.setGeometry(QtCore.QRect(190, 10, 211, 51))
    font = QtGui.QFont()
    font.setPointSize(18)
    self.label.setFont(font)
    self.label.setObjectName(_fromUtf8("label"))

    self.retranslateUi(Dialog)
    QtCore.QMetaObject.connectSlotsByName(Dialog)

def retranslateUi(self, Dialog):
    Dialog.setWindowTitle(_translate("Dialog", "Login Form", None))
    self.u_name_label.setText(_translate("Dialog", "USERNAME ", None))
    self.pass_label.setText(_translate("Dialog", "PASSWORD", None))
    self.login_btn.setText(_translate("Dialog", "Login", None))
    self.signup_btn.setText(_translate("Dialog", "Sign Up", None))
    self.label.setText(_translate("Dialog", "Login Form", None))

if name == "main": import sys app = QtGui.QApplication(sys.argv) Dialog = QtGui.QDialog() ui = UiDialog() ui.setupUi(Dialog) Dialog.show() sys.exit(app.exec()) This is my program but i am getting error in the line as sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. *connection.execute("SELECT FROM USERS WHERE USERNAME = ? AND PASSWORD = ?",(username,password))**

I am running this code in ubuntu, please guide me to clear this error........