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

[Prince_Bhatia]

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 ?

[snippsat]

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

[Prince_Bhatia]

but i have already connected it with the database...it is just not able to submit the data

[snippsat]

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

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.

@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.