Error binding parameter 0 - probably unsupported type.

[illmattic]

Hoping I can get some help on this error:

c.execute("INSERT INTO USMoneySupply (MonetaryBase, M1, M2) VALUES (?, ?, ?)", sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

api_key = open('quandlapikey.txt', 'r').read()

def data_extract(code):
    data = quandl.get(code, authtoken=api_key, collapse='monthly')
    data['Y/Y, (%)'] = data['Value'].pct_change(12)*100
    return data

m2 = data_extract('FRED/M2')
m1 = data_extract('FRED/M1')
mb = data_extract('FRED/BOGMBASEW')

conn = sqlite3.connect('Database.db')
c = conn.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS USMoneySupply(MonetaryBase REAL, MonetaryBasePer REAL, M1 REAL, M1Per REAL, M2 REAL, M2Per REAL)")

def data_entry():
    mb_data = mb['Value']
    mb_per = mb['Y/Y, (%)']
    m1_data = m1['Value']
    m1_per = m1['Y/Y, (%)']
    m2_data = m2['Value']
    m2_per = m2['Y/Y, (%)']
    c.execute("INSERT INTO USMoneySupply (MonetaryBase, MonetaryBasePER, M1, M1Per, M2, M2Per) VALUES (?, ?, ?, ?, ?, ?)",
              (mb_data, mb_per, m1_data, m1_per, m2_data, m2_per))
    conn.commit()

create_table()
data_entry()
c.close()
conn.close()

Thank you.

[Larz60+]

please show the actual error traceback, complete and unaltered.
It contains valuable debugging information.

[illmattic]

Sorry about that. Is this better?

Error:
Traceback (most recent call last):
  File "C:/Users/duo1m/PycharmProjects/python_training/database/SQLite_training.py", line 35, in <module>
    data_entry()
  File "C:/Users/duo1m/PycharmProjects/python_training/database/SQLite_training.py", line 30, in data_entry
    c.execute("INSERT INTO USMoneySupply (MonetaryBase, MonetaryBasePER, M1, M1Per, M2, M2Per) VALUES (?, ?, ?, ?, ?, ?)",
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Process finished with exit code 1

[Larz60+]

It's complaining about the type of MonetaryBase (I believe),
add just before insert statement:

print(f"Type MonetaryBase: {type(MonetaryBase)}")

What are the results?

[illmattic]

print(f"Type MonetaryBase: {type(MonetaryBase)}")

Error:
Traceback (most recent call last):
  File "C:/Users/duo1m/PycharmProjects/python_training/database/SQLite_training.py", line 38, in <module>
    print(f"Type MonetaryBase: {type(MonetaryBase)}")
NameError: name 'MonetaryBase' is not defined

[Yoriz]

It is the value of mb_data that you need to find out what it is

[illmattic]

It is the value of mb_data that you need to find out what it is

Like this:

def data_entry():
    mb_data = mb['Value']
    mb_per = mb['Y/Y, (%)']
    m1_data = m1['Value']
    m1_per = m1['Y/Y, (%)']
    m2_data = m2['Value']
    m2_per = m2['Y/Y, (%)']
    print(f"Type mb_data: {type(mb_data)}")

Output:
Type mb_data: <class 'pandas.core.series.Series'>

Process finished with exit code 0

[illmattic]

I found a different way to achieve the desired result:

api_key = open('quandlapikey.txt', 'r').read()

def data_extract(code, column_name):
    data = quandl.get(code, authtoken=api_key, start_date='2000-01-01', collapse='monthly',)
    data['Y/Y, (%)'] = data['Value'].pct_change(12)*100
    data.rename(columns={'Value': column_name}, inplace=True)
    return data

m2 = data_extract('FRED/M2','M1')
m1 = data_extract('FRED/M1','M1')
mb = data_extract('FRED/BOGMBASEW', 'Monetary Base')

df_merged = [mb,m1,m2]
df = reduce(lambda  left,right: pd.merge(left,right,on='Date', how='outer'), df_merged).fillna('-')

conn = sqlite3.connect('Database.db')
c = conn.cursor()

def data_entry():
    df.to_sql('US Money Supply', conn, if_exists='replace')
    conn.commit()
    c.close()
    conn.close()

data_entry()

Here I get no errors and the output I wanted.