Python Forum
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
#1
hi,

I have created a program that will take user input in form in flask and will save it to sqlite3 , but whenever i submit it i receive the following error

Error:
sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type. [SQL: 'INSERT INTO otrs ("Agent", "Date", "XID_Number", "RERA_Number", "RERA_URL", "City", "State", "Data_Types", creation_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: (<>, '', '', '', '', '', '', 'OTRS', None)] (Background on this error at: http://sqlalche.me/e/rvf5)
This is my db_creaor file:

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker
import psycopg2


engine = create_engine("sqlite:///dashboard.db", echo=True)

Base = declarative_base()

class creation(Base):

    __tablename__ = "rera"

    id = Column(Integer, primary_key=True)

    name = Column(String)

##        def __init__(self, name):
##
##            """"""
##            self.name = name

    def __repr__(self):

        return "<creation: {}>".format(self.name)

class OTRS(Base):

    """"""

    __tablename__ = "otrs"

    id = Column(Integer, primary_key=True)
    Agent = Column(String)
    Date = Column(Date)
    XID_Number = Column(String)
    RERA_Number = Column(String)
    RERA_URL = Column(String)
    City = Column(String)
    State = Column(String)
    Data_Types = Column(String)



    creation_id = Column(Integer, ForeignKey("rera.id"))

    create = relationship("creation", backref=backref(

        "otrs", order_by=id))

##    def __init__(self, Agent, Date, XID_Number,RERA_Number,RERA_URL,City, State, Data_Types):
##
##        """"""
##        self.Agent = Agent
##        self.Date = Date
##        self.XID_Number = XID_Number
##        self.RERA_Number = RERA_Number
##        self.RERA_URL = RERA_URL
##        self.City = City
##        self.State = State
##        self.Data_Types = Data_Types
##
# create tables

Base.metadata.create_all(engine)
This is my main.py
from app import app
from db_setup import init_db, db_session
from forms import DataSearchForm, DataForm
from flask import flash, render_template, request, redirect
from models import OTRS, creation
from sqlalchemy.orm import sessionmaker

init_db()

@app.route('/', methods=['GET', 'POST'])
def index():
    search = DataSearchForm(request.form)
    if request.method == 'POST':
        return search_results(search)

    return render_template('index.html', form=search)


@app.route('/results')
def search_results(search):
    results = []
    search_string = search.data['search']

    if search.data['search'] == '':
        qry = db_session.query(OTRS)
        results = qry.all()

    if not results:
        flash('No results found!')
        return redirect('/')
    else:
        # display results
        return render_template('results.html', results=results)

@app.route('/new_data', methods=['GET', 'POST'])

def new_data():
    """
    Add a new data
    """
    form = DataForm(request.form)

    if request.method == 'POST' and form.validate():
        # save the FORM
        otrs = OTRS()
        print(otrs)
        save_changes(otrs, form, new=True)
        flash('Form created successfully!')
        return redirect('/')
    return render_template('new_data.html', form=form)

def save_changes(otrs, form, new=False):
    """
    Save the changes to the database
    """
    # Get data from form and assign it to the correct attributes
    # of the SQLAlchemy table object
    Agent = creation()
    Agent.name = form.Agent.data

    otrs.Agent = Agent
    #otrs.Agent = form.Agent.data
    otrs.Date = form.Date.data
    otrs.XID_Number = form.XID_Number.data
    otrs.RERA_Number = form.RERA_Number.data
    otrs.RERA_URL = form.RERA_URL.data
    otrs.City = form.City.data
    otrs.State = form.State.data
    otrs.Data_Types = form.Data_Types.data
    if new:
        # Add the new data to the databae
        db_session.add(otrs)
    
    db_session.commit()
    # commit the data to the databa
if __name__ == '__main__':
    app.run()
this is my db.setup:
# db_setup.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import psycopg2

engine = create_engine('sqlite:///dashboard.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    import models
    Base.metadata.create_all(bind=engine)
this my models.py
from app import db

class creation(db.Model):
    __tablename__ = "rera"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
##    def __init__(self, name):
##
##        """"""
##        self.name = name
    def __repr__(self):
        return "<{}>".format(self.name)

class OTRS(db.Model):

    """"""
    __tablename__ = "otrs"
    id = db.Column(db.Integer, primary_key=True)
    Agent = db.Column(db.String)
    Date = db.Column(db.String)
    XID_Number = db.Column(db.String)
    RERA_Number = db.Column(db.String)
    RERA_URL = db.Column(db.String)
    City = db.Column(db.String)
    State = db.Column(db.String)
    Data_Types = db.Column(db.String)

    creation_id = db.Column(db.Integer, db.ForeignKey("rera.id"))
    create = db.relationship("creation", backref=db.backref(

        "otrs", order_by=id), lazy=True)

##    def __init__(self, Agent, Date,XID_Number,RERA_Number,RERA_URL,City, State, Data_Types):
##
##        """"""
##        self.Agent = Agent
##        self.Date = Date
##        self.XID_Number = XID_Number
##        self.RERA_Number = RERA_Number
##        self.RERA_URL = RERA_URL
##        self.City = City
##        self.State = State
##        self.Data_Types = Data_Types
can anyone please tell how to solve this error ?
Reply
#2
You have made this more difficult than it need to be bye using SQLAlchemy directly.
Flask-SQLAlchemy is easier and the common way most use to connect a database in Flask.
It has easy connect Connection URI to most used databases.
Example.
# SQLite
sqlite:////tmp/test.db # Windows 3 ///

# Postgres:
postgresql://scott:tiger@localhost/mydatabase

# MySQL:
mysql://scott:tiger@localhost/mydatabase
Reply
#3
but i have already connected it with the database...it is just not able to submit the data
Reply
#4
(Apr-03-2018, 08:09 AM)Prince_Bhatia Wrote: but i have already connected it with the database...it is just not able to submit the data
It may not be connected,look at link in your error.
@Background on this error at: http://sqlalche.me/e/rvf5
Quote:Exception raised for errors that are related to the database interface rather than the database itself.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.
The InterfaceError is sometimes raised by drivers in the context of the database connection being dropped, or not being able to connect to the database.
For tips on how to deal with this, see the section Dealing with Disconnects.
I have tested and done a lot with databases in Flask,but i would not deal with SQLAlchemy directly when Flask-SQLAlchemy dos this job so good.
Quote:@Flask-SQLAlchemy
It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  POST `api` 415 (Unsupported Media Type) Dexty 2 4,642 Aug-09-2023, 02:42 AM
Last Post: OEVFyeesZbrU
  Flask error sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint pythonpaul32 2 3,488 Feb-21-2023, 03:13 AM
Last Post: noisefloor
  pymysql.err.InterfaceError nikos 1 2,998 Feb-24-2019, 02:07 PM
Last Post: nikos
  Type Not Found error on python soap call using suds library wellborn 1 4,575 Dec-19-2017, 07:53 PM
Last Post: micseydel
  Getting past a none type error CodyW129 1 10,009 Jul-27-2017, 12:22 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020