About the Pandas DataFrame SQLite3 conversion

Hello. I'm trying to google several times, but this part is blocked, so I'm asking you a question. We created two lists as shown below, and created a DataFrame that identifies the quantity of values within the range by creating categories using pd.cut and value_counts(). And I keep getting errors in the process of converting this DataFrame to save it as a db file. In general, DataFrame can be saved as a db file, but I wonder if the categorical type cannot be converted to a db file.

Please tell me why and how it can't be converted :(

Error content:

Traceback (most recent call last):
  File "C:/Users/lajet/Desktop/New Version/Exam_2.py", line 74, in <module>
    df.to_sql('test2', conn, if_exists="append")
  File "C:\Users\lajet\Anaconda3\envs\BP3\lib\site-packages\pandas\core\generic.py", line 2531, in to_sql
    dtype=dtype, method=method)
  File "C:\Users\lajet\Anaconda3\envs\BP3\lib\site-packages\pandas\io\sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "C:\Users\lajet\Anaconda3\envs\BP3\lib\site-packages\pandas\io\sql.py", line 1547, in to_sql
    table.insert(chunksize, method)
  File "C:\Users\lajet\Anaconda3\envs\BP3\lib\site-packages\pandas\io\sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "C:\Users\lajet\Anaconda3\envs\BP3\lib\site-packages\pandas\io\sql.py", line 1319, in _execute_insert
    conn.executemany(self.insert_statement(), data_list)
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

    import sqlite3 as sq
    import pandas as pd

    conn = sq.connect('test.db')
    cur = conn.cursor()

    list1 = (1, 1, 2, 4, 6, 5, 3, 12, 11, 12, 11, 9, 10, 8, 6, 6, 7)
    list2 = (1, 3, 6, 9, 12)

    _cut = pd.cut(list1, list2)
    df = _cut.value_counts()
    df = df.to_frame()
    df = df.reset_index()
    df.columns = ['range', 'unit']

    # Error converting to sql!
    df.to_sql('test', conn)

    cur.execute("SECLECT * FROM test")
    rows = cur.fetchall()
    for row in rows:
        print(row)

python pandas dataframe database

2022-09-22 08:33

1 Answers

The problem seems to be caused by special symbols in the data such as (1,3] and (3,6] generated by pd.cut.

2022-09-22 08:33

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656