https://ploomber.io/ logo
Join Slack
Powered by
# jupysql
  • t
    Copy code
    UsageError: An error happened while creating the connection: type object 'ClickHouseDialect_http' has no attribute 'dbapi'.

Perhaps you meant to use the 'clickhouse' db 
To find more information regarding connection: <https://jupysql.ploomber.io/en/latest/integrations/clickhouse.html>

To fix it:

Pass a valid connection string:
    Example: %sql <postgresql://username:password@hostname/dbname>

For more details, see: <https://jupysql.ploomber.io/en/latest/connecting.html>
    e
    • 2
    • 1
  • n
    hi! how to use --with in jupysql with several inputs tried several options and none of them worked 
    --with=transform_types,recrafts,image_types
    --with transform_types --with recrafts --with image_types
    --with transform_types,recrafts,image_types
    e
    • 2
    • 5
  • n
    Copy code
    RuntimeError: Orig exception: Code: 394. DB::Exception: Query was cancelled. (QUERY_WAS_CANCELLED) (version 23.8.11.28 (official build))

If you need help solving this issue, send us a message: <https://ploomber.io/community>
    e
    • 2
    • 3
  • m
    Hi, this seems like a common thing but I'm not sure how to start debugging. I'm having trouble loading data from a file with mysql:
    Copy code
    %%sql

LOAD DATA LOCAL INFILE 'penguins.csv'
INTO TABLE PENGUIN (
    SPECIES, ISLAND, BILL_LENGTH, BILL_DEPTH, FLIPPER_LENGTH,
    BODY_MASS, SEX, YEAR
)
    and getting
    Copy code
    RuntimeError: (pymysql.err.OperationalError) (3948, 'Loading local data is disabled; this must be enabled on both the client and server sides')
    as far as I can tell it's enabled on the server:
    Copy code
    %%sql

show global variables like 'local_infile';

Running query in '<mysql+pymysql://martin>:***@localhost/mysql'
1 rows affected.
Variable_name 	Value
local_infile 	ON
    e
    • 2
    • 5
  • w
    Hello all, I'm also doing some testing with JupySQL for use by analysts against an OracleDB. Specifically, I'm trying to setup the connections.ini to keep credentials separate. However, Oracle URLs require the schema to be in the query part. I fail however to get that parsed correctly... what's magic as it should return a dict. The error when loading a section, returns:
    Copy code
    ValueError: not enough values to unpack (expected 2, got 1)
    ... which brings me here...
    e
    • 2
    • 6
  • p
    Wondering whether I can do nested SQL queries (subqueries) to process nested JSON, like this: sql SELECT date,raw.capable FROM (SELECT data FROM read_json_auto(’https://stats.labs.apnic.net/cgi-bin/json-table-v6.pl?x=IN55836')) The result of the subquery is a big JSON object; so I guess it needs to be put into another read_json_auto(). I’ve tried a few variations (being unsure of the syntax) but no success. Thanks for any advice! The URL above is live and it’s public data BTW.
    e
    • 2
    • 2
  • t
    Hey all, I’m having a problem with getting my DDL statements to commit when integrating with DuckDB using Jupyter notebooks. E.g.
    Copy code
    import duckdb

%load_ext sql

%sql duckdb:///test.db

# %%
%%sql
DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data AS SELECT * FROM read_csv_auto('/tmp/data.csv');

-- Below works when run within the notebook.
select count(*) from test_data;
    However, when I open test.db afterwards, the table is not there. I tried wrapping the query inside 
    BEGIN TRANSACTION
    … 
    COMMIT
    but I get an error saying transactions are not supported. Is there anything specific I need to do to make sure the data saves?
    d
    • 2
    • 3
  • j
    hi, is it possible to get syntax highlighting for 
    %%sql
    cells in VSCode jupyter notebooks? I believe the syntax highlighting docs are for Jupyter Lab, and not VSCode. jupysql is working great, this is the one thing that’s kind of annoying 😕
    d
    • 2
    • 3
  • a
    Hello I was wondering if one can do something like this:
    Copy code
    query_template = """
SELECT
    MONTH(created) AS month,
    COUNT(*) AS row_count
FROM
    {table_name}
GROUP BY
    MONTH(created)
ORDER BY
    month;
"""
m2m = []
for table in ("T1", "T2", "T3"):
    query = query_template.format(table_name=table)
    result = %sql {query}
    m2m.append(result)
    e
    • 2
    • 4
  • t
    This is probably a stupid question, but I would like to use the tables and columns information I get from the database within my code. How can I extract e.g. the column names from the result which is of type sql.inspect.Columns to a list or a pandas dataframe.
    n
    • 2
    • 2
  • t
    best regards
  • m
    When i run the attached code i got the error:
  • m
    %%sql SELECT * FROM id_map AS i JOIN building_structure AS s ON i.building_id=building_structure.building_id WHERE district_id = 4 Limit 5
  • m
    Running query in 'sqlite:////home/jovyan/nepal.sqlite'
    Copy code
    RuntimeError: (sqlite3.OperationalError) no such column: building_structure.building_id
[SQL: SELECT *
FROM id_map AS i
JOIN building_structure AS s ON i.building_id=building_structure.building_id
WHERE district_id = 4
Limit 5]
(Background on this error at: <https://sqlalche.me/e/20/e3q8>)
If you need help solving this issue, send us a message: <https://ploomber.io/community>
  • m
    How can it be recified?
    d
    • 2
    • 1
  • c
    Hi there! For some reason, jupysql can't find my dataframe, even though I used exactly the example in the docs... I'm using jupyterlab
    Copy code
    Catalog Error: Table with name df does not exist!
    e
    • 2
    • 9
  • e
    Maybe this channel is more appropriate..
    n
    • 2
    • 4
  • e
    Copy code
    %config SqlMagic.dsn_filename = "connections.ini"
    is there a way to change this without using the magic?
  • e
    use case being.. I'm building this as part of a sagemaker custom kernel and 
    /home/sagemaker-user/.jupysql/
    gets trashed on first start-up so there's no way to have the file in there unless I use a lifecycle policy to symlink it at bootup, it would be easier if could just change the config
  • e
    answer: you can drop it in your 
    ipython_kernel_config.py
    :
    Copy code
    c.IPKernelApp.exec_lines = ["%config SqlMagic.dsn_filename = '/connections.ini'"]
  • e
    actually no you can't... because you can't change the dsn_filename until you've done 
    %load_ext sql
    , which means it's always going to fail
  • i
    Hi, I am writing a SQL query with a Jupyter Notebook (with serverless SQL) I got this error: "
    Copy code
    RuntimeError: (sqlite3.OperationalError) database is locked
[SQL: CREATE TABLE assessments (
    AssessmentId INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    Assessment_Officer VARCHAR(100),
    Officer_Reg INTEGER,
    water_level NUMERIC(10,1)
);]
(Background on this error at: <https://sqlalche.me/e/20/e3q8>)
    "Please help me resolve this.
    d
    • 2
    • 2
  • k
    Hello, could you help me clarify if I can get a hold of the connection object? The reason is I need to pass this connection object to other processes. Here is the use case:
    Copy code
    %load_ext sql
%config SqlMagic.autopolars = True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False

conn = duckdb.connect()
%sql conn --alias duckdb
    Now, I suppose that jupysql is using the defined connection. I also pass this connection to PyIceberg to load some tables
    Copy code
    from pyiceberg.catalog import load_catalog
catalog = load_catalog(**credential)
catalog.load_table((namespace, "table")).scan().to_duckdb("table", conn)
    Now, I can query this table using the 
    conn
    object, so this works
    Copy code
    conn.sql("""
SELECT *
FROM table
LIMIT 1
""")
    But this does not work, complaining that the table does not exist
    Copy code
    %%sql
SELECT *
FROM table
LIMIT 1
    Is there any way we can let jupysql work with the connection in 
    conn
    ?
    e
    e
    • 3
    • 6
  • s
    Hello, I'm trying to work with Oracle (Autonomous) DB and I have two questions: 1. It looks like 
    %%sql
    doesn't support running PL/SQL block. Is there any plan to implement this?
    Copy code
    %%sql
begin
    dbms_session.sleep(5);
end;
    2. Is it not possible to execute multiple statements in one cell one by one using 
    %%sql
    ? Like the below:
    Copy code
    %%sql
statement1;
statement2;
...
    Thanks.
    e
    • 2
    • 2
  • m
    I would like to know if there have been some relatively recent changes that have made jupysql incompatible with Jupyter Lab. I have been trying to install and use jupysql to help manage a personal database running on a local installation of mariadb. I use pipx to manager and run Jupyter Lab in its own environment. I have run pipx inject to place the jupysql package (0.10.17) under the jupyterlab environment (4.3.5). When I attempt to load it via '%load_ext sql' the system responds with 'The sql module is not an IPython extension.'. (%reload_ext sql gives no error message, but also doesn't load the module.) The instructions on the web site https://jupysql.ploomber.io/ don't offer any help..
    e
    • 2
    • 6
  • m
    Hi! I'm seeing odd behavior. I have a table 
    sales_data
    that shows up in 
    %sqlcmd tables
    and I can query from it via 
    %sql
    but it says there is not table when i use 
    %sqlcmd columns
    even though it is listed right there in the 
    Did you mean:
    Anyone else encounter this? edit: same problem when i try 
    %sqlcmd profile
    for example
    e
    • 2
    • 3
  • a
    I am also struggling with loading the %sql extension. I have tried with both Python 3.13 & Python 3.12. I have worked with this in the past, but had to reset my windows system recently. Any assistance is greatly appreciated. Thanks !
    e
    • 2
    • 3
  • a
    I got difficulty connecting my jupyter to sqlite3 outside of the WQU
  • e
    Heya, we're running into some odd type conversion issues using jupysql with redshift. Given the following query:
    Copy code
    %%sql
CREATE EXTERNAL TABLE transformed_data.test_table4
PARTITIONED BY (year)
STORED AS parquet
LOCATION '<s3://foo-us-development/test_folder4>'
AS (
	SELECT
	'Bill'::varchar(100) as name,
	2025 as year
);
INSERT INTO transformed_data.test_table2
(
	SELECT
	'Hello'::varchar(100),
	2026
);
    we receive the error:
    Copy code
    ValueError: invalid literal for int() with base 10: b'INSERT'
    yet the stack trace shows:
    Copy code
    File /opt/conda/lib/python3.11/site-packages/redshift_connector/core.py:2109, in Connection.handle_COMMAND_COMPLETE(self, data, cursor)
   2107 _logger.debug("command=%s", command)
   2108 if command in self._commands_with_count:
-> 2109     row_count: int = int(values[-1])
   2110     if cursor._row_count == -1:
   2111         cursor._row_count = row_count
    is this error in the redshift_connector or how in jupysql interprets the error?
  • e
    archived the channel