Python, postgresql, psycopg2 getting a Sqlite3 error. Not using sqlite3

Question

AWS EC2 linux2 Python3.10 code trying to insert into a Postgresql database. Getting a Sqlite3 error message.

Generates the message below

Even though I'm not using Sqlite. I am running in a virtual environment. It is activated.

Python 3.10 on an EC2 linux2 instance.

The version of postgresql is 14.7.

My requirements.txt is:

pandas>=1.3.0
numpy>=1.21.6
psycopg2-binary>=2.9.6
pyarrow>=12.0.0
openpyxl>=3.1.2
fastparquet>=0.8.1
watchdog>=2.1.
parquet-tools
pyspark
sqlalchemy

pip list
(.venv) @ec2-user:~/fluence-test-bench/.venv/include$ pip list Package Version
----------------- --------
boto3 1.26.146
botocore 1.29.146
colorama 0.4.6
cramjam 2.6.2
cursor 1.3.5
et-xmlfile 1.1.0
fastparquet 2023.4.0
fsspec 2023.5.0
greenlet 2.0.2
halo 0.0.29
jmespath 1.0.1
log-symbols 0.0.14
numpy 1.24.3
openpyxl 3.1.2
packaging 23.1
pandas 2.0.2
parquet-tools 0.2.14
pip 23.1.2
psycopg2-binary 2.9.6
py4j 0.10.9.7
pyarrow 12.0.0
pyspark 3.4.0
python-dateutil 2.8.2
pytz 2023.3
s3transfer 0.6.1
setuptools 65.5.0
six 1.16.0
spinners 0.0.24
tabulate 0.8.10
termcolor 2.3.0
thrift 0.13.0
typing_extensions 4.6.3
tzdata 2023.3
urllib3 1.26.16
watchdog 3.0.0

    import  pandas as pd
    import  psycopg2 as sql
    from    sqlalchemy import create_engine
    from    sqlalchemy.sql import text
    
    conn_string = 'postgresql://...amazonaws.com/...'

    db = create_engine(conn_string)
    conn2 = db.connect()
    
    df = pd.read_parquet("files_by_count/Panda_1.parquet")
     
    conn2.execute(text('select *  from sensor_data_in;'))

   df.to_sql('sensor_data_in',con=conn2,if_exists='append',index=False)
    conn2.close()```


>Traceback (most recent call last):
  File "/home/ec2-user/fluence-test-bench/parq.py", line 24, in <module>
    df.to_sql('sensor_data_in',con=conn,if_exists='append',index=False)
  File "/home/ec2-user/fluence-test-bench/.venv/lib/python3.10/site-packages/pandas/core/generic.py", line 2878, in to_sql
    return sql.to_sql(
  File "/home/ec2-user/fluence-test-bench/.venv/lib/python3.10/site-packages/pandas/io/sql.py", line 768, in to_sql
    with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
  File "/home/ec2-user/fluence-test-bench/.venv/lib/python3.10/site-packages/pandas/io/sql.py", line 821, in pandasSQL_builder
    import sqlite3
  File "/usr/local/lib/python3.10/sqlite3/__init__.py", line 57, in <module>
    from sqlite3.dbapi2 import *
  File "/usr/local/lib/python3.10/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'

You are mixing Pandas and psycopg2 when Pandas is looking for SQLAlchemy connection. Per to_sql con sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection Your psycopg2 is not being seen as correct so Pandas is falling back to a sqlite3 connection attempt. — Adrian Klaver
– Adrian Klaver, Commented Jun 4, 2023 at 23:35
Installed sqlalchemy. Added the create_engine connection, and still got the same error. — Bec Wilson
– Bec Wilson, Commented Jun 5, 2023 at 0:30
Hmm. Not sure what is going on, especially as sqlite3 ships as a Python global module. How as Python installed on that machine? — Adrian Klaver
– Adrian Klaver, Commented Jun 5, 2023 at 15:19
@AdrianKlaver yes, I agree about suspecting the Python install because this works perfectly on a Windows 10 box I have. This is an EC2 machine and a coworker installed Python3 on it as it came with Python 2.7 something. I am going to wait for him to get back and I will discuss with him. I even installed pysqlite3 to no avail. I will update this with the solution. Thanks1 — Bec Wilson
– Bec Wilson, Commented Jun 5, 2023 at 21:40
@BecWilson: I seem to have a similar problem, but only on a Linux machine (Centos 8), not on Windows 11. On both, Python 3.10.5 is running in a virtual environment. Could you find something in the meantime? — Jürg Schneider
– Jürg Schneider, Commented Oct 27, 2023 at 10:00

snakecharmerb · Accepted Answer · 2023-11-01 21:47:31Z

1

Try to install the sqlite dev tools prior to compiling Python from source on your machine. Check this: Missing sqlite3 after Python3 compile

edited Nov 1, 2023 at 21:47

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

answered Oct 27, 2023 at 13:22

Jürg Schneider

832 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

snakecharmerb · Accepted Answer · 2023-11-01 22:04:55Z

0

From this line in the traceback

File "/home/ec2-user/fluence-test-bench/.venv/lib/python3.10/site-packages/pandas/io/sql.py", line 821, in pandasSQL_builder import sqlite3

we can see that Pandas is trying to import Python's sqlite3 module. This import is unconditional; Pandas will always try to import sqlite3 because it will try to create an SQLite connection as a fallback if no connection is provided to to_sql and similar methods.

It would seem that the Python build on your instance does not include sqlite3.

Depending on the OS/Python install on your instance, you will need to either use the OS package manager to install Python's sqlite3 module or install SQLite and its headers and then recompile Python.

answered Nov 1, 2023 at 22:04

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Collectives™ on Stack Overflow

Python, postgresql, psycopg2 getting a Sqlite3 error. Not using sqlite3

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related