CODEWITHSUNDEEP
pythondatabasesqlitecsvblob
Meisam H
Meisam H

Reputation: 67

Why SQLITE doesn't accept My INTEGER/TEXT data larger than 8, using Python 3?

Problem

I am trying to read a csv file to Pandas, and write it to a SQLite database.Process works for all the columns in the csv file except for "Fill qty" which is a Positive Integer(int64). The process changes the type from TEXT/INTEGER to BLOB. So I tried to load only the "Fll qty" column from Pandas to SQLite, and surprisingly I noticed I can safely do that for all integers smaller than 10 (I don't have 9 in my dataset, so basically 1,2,...,8 loaded successfully).

Here is what I tried:

I tried what I could think of: change "Fill_Qty" type in Schema to INTEGER to REAL, NULL or TEXT , change data type in Pandas from int64 to float or string before inserting to SQLite table. None of them worked. By the look of it, the "Trade_History.csv" file seems to be fine in Pandas or Excel. Is there something that my eyes dont see?!? So I am really confused what is happening here!

You would need the .csv file to test the code. Here is the code and .csv file: https://github.com/Meisam-Heidari/Trading_Min_code

The code:

### Imports:
import pandas as pd
import numpy as np
import sqlite3
from sqlite3 import Error

def create_database(db_file):
    try:
        conn = sqlite3.connect(db_file)
    finally:
        conn.close()

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    try:
        conn = sqlite3.connect(db_file)
        return conn
    return None

def create_table(conn,table_name):  
    try:
        c = conn.cursor()
        c.execute('''CREATE TABLE {} (Fill_Qty TEXT);'''.format(table_name))
    except Error as e:
        print('Error Code:  ', e)
    finally:
        conn.commit()
        conn.close()
    return None

def add_trade(conn, table_name, trade):
    try:
        print(trade)
        sql = '''INSERT INTO {} (Fill_Qty)
              VALUES(?)'''.format(table_name)
        cur = conn.cursor()
        cur.execute(sql,trade)
    except Error as e:
        print('Error When trying to add this entry:  ',trade)
    return cur.lastrowid

def write_to_db(conn,table_name,df):
    for i in range(df.shape[0]):
        trade = (str(df.loc[i,'Fill qty']))
        add_trade(conn,table_name,trade)
        conn.commit()

def update_db(table_name='My_Trades', db_file='Trading_DB.sqlite', csv_file_path='Trade_History.csv'):
    df_executions = pd.read_csv(csv_file_path)
    create_database(db_file)
    conn = create_connection(db_file)
    table_name = 'My_Trades'
    create_table(conn, table_name)
    # writing to DB
    conn = create_connection(db_file)
    write_to_db(conn,table_name,df_executions)
    # Reading back from DB
    df_executions = pd.read_sql_query("select * from {};".format(table_name), conn)
    conn.close()
    return df_executions



### Main Body:

df_executions = update_db()

Any alternatives

I am wondering if anyone have a similar experience? Any advices/solutions to help me load the data in SQLite? I am Trying to have something light and portable and unless there is no alternatives, I prefer not to go with Postgres or MySQL.

Upvotes: 1

Views: 477

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169304

You're not passing a container to .execute() when inserting the data. Reference: https://www.python.org/dev/peps/pep-0249/#id15

What you need to do instead is:

trade = (df.loc[i,'Fill qty'],)
#                            ^ this comma makes `trade` into a tuple

The types of errors you got would've been:

ValueError: parameters are of unsupported type

Or:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

Upvotes: 1

Related Questions