{"id":46810,"date":"2024-08-29T00:00:00","date_gmt":"2024-08-29T07:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/pandas-with-python-griddb-sql-queries\/"},"modified":"2026-03-30T13:40:57","modified_gmt":"2026-03-30T20:40:57","slug":"pandas-with-python-griddb-sql-queries","status":"publish","type":"post","link":"https:\/\/griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/","title":{"rendered":"Pandas with Python GridDB SQL Queries"},"content":{"rendered":"

We have written before about how to pair pandas<\/a> dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB<\/a>. In there, we read from our GridDB database via the python API (which uses TQL under the hood) and convert the resulting rows of data to a dataframe. If you’re unfamilar with dataframes, they are the main purpose of using a library like Pandas and can argued as being a superior data structure for analysis and data science.<\/p>\n

In this article, we want to again visit converting rows of GridDB data into dataframes, but would like to showcase using SQL with JDBC during the querying portion of our code. The reason one might want to use SQL instead of TQL is two fold:<\/p>\n

    \n
  1. You can conduct more intricate queries with SQL because of TQL’s limited functionality<\/a><\/li>\n
  2. Partitioned tables<\/a> are sometimes not available to be read by TQL, meaning SQL can be the only option for those specific containers<\/li>\n<\/ol>\n

    So, in this article, we will showcase how to connect to GridDB and make SQL queries with Python and directly feed those results into a pandas dataframe. And please note, we are not simply using JayDeBeApi as we have showcased in our previous article: Using Python to interface with GridDB via JDBC with JayDeBeApi<\/a>, because the results of those<\/em> sql queries are not in a valid datatype to be read by pandas.<\/p>\n

    Prerequisites<\/h2>\n

    The code for this article has been containerized into a Docker container. To run it, simply install Docker and build and run the project. The source code can be found in the GridDBnet github:<\/p>\n

    $ git clone https:\/\/github.com\/griddbnet\/Blogs.git --branch sql-pandas<\/code><\/p>\n

    You can take a look at the Dockerfile contained in the repo to see how to run this in baremetal — essentially you just need to install Python and the appropriate SQL\/pandas libraries. You will also need java installed as Java is what is used to make connections with JDBC (Java Database Connection).<\/p>\n

    Python Libraries<\/h2>\n

    As hinted above, we will need to find and use a JDBC python library which produces rows of data that can fed into pandas’ read_sql<\/code> method call. According to the Pandas docs, the connection fed into the read_sql<\/code> method needs to be either: “ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection”.<\/p>\n

    This, of course, rules out JayDeBeApi but we were able to find a fork of the popular SQLAlchemy<\/a> library which allows for generic connections to any database which can connect via JDBC; that library can be found here<\/a> and is what allows this entire premise to work. Other than that, we will of course also need the pandas and numpy libraries to conduct our data analysis.<\/p>\n

    Making SQL Connection with SQLAlchemy<\/h2>\n

    Reading the docs for SQLAlchemy JDBC Generic: https:\/\/pypi.org\/project\/sqlalchemy-jdbc-generic\/<\/a> along with the docs for GridDB JDBC: https:\/\/github.com\/griddb\/docs-en\/blob\/master\/manuals\/GridDB_JDBC_Driver_UserGuide.md<\/a> allowed for us to ascertain the proper way of building out the JDBC connection string — again, note that it’s not<\/em> the same process as building out the connection string with the JayDeBeApi library. Having set the table, here is how to create that connection string:<\/p>\n

    \n
    from sqlalchemy.engine.url import URL\n\neng_url = URL.create(\n    drivername='sqlajdbc',\n    host='\/myCluster\/public',\n    query={\n        '_class': 'com.toshiba.mwcloud.gs.sql.Driver',\n        '_driver': 'gs',\n        'user': 'admin',\n        'password': 'admin',\n        'notificationMember': 'griddb-server:20001',\n        '_jars':  '\/app\/lib\/gridstore-jdbc-5.6.0.jar'\n    }\n)<\/code><\/pre>\n<\/div>\n
    First, the drivername must<\/em> be set as sqlajdbc<\/code>, this is the name of the generic JDBC driver. Next, the connection order might seem a bit backwards, but this is the correct way of building the URL. One other thing, the _jars<\/code> option expects the library jar so please make sure the path points to where you keep your GridDB JDBC jar file. If you are using the included Dockerfile, it already points to the correct path.<\/p>\n
    One last gotcha<\/em> when trying to make this connection is that before you feed in the connection details and try to make the connection to GridDB, you will need to start the JVM (Java Virtual Machine) like so:<\/p>\n
    \n
    import jpype\njpype.startJVM(jpype.getDefaultJVMPath(), \"-ea\", \"-Djava.class.path=\/app\/lib\/gridstore-jdbc-5.6.0.jar\")<\/code><\/pre>\n<\/div>\n
    With this information all set, you can now make the connection and run some queries to be saved into dataframes:<\/p>\n
    \n
    from sqlalchemy import create_engine\neng = create_engine(eng_url)\nwith eng.connect() as c:\n    print(\"Connected\")\n    df = pd.read_sql(\"SELECT * FROM LOG_agent_intrusion WHERE exploit = True\", c)\n\n    print(df.head())<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from our GridDB database via the python API (which uses TQL under the hood) and convert the resulting rows of data to a dataframe. If you’re unfamilar with dataframes, they […]<\/p>\n","protected":false},"author":1,"featured_media":30214,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[121],"tags":[],"class_list":["post-46810","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"yoast_head":"\nPandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT<\/title>\n<meta name=\"description\" content=\"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT\" \/>\n<meta property=\"og:description\" content=\"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\" \/>\n<meta property=\"og:site_name\" content=\"GridDB: Open Source Time Series Database for IoT\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/griddbcommunity\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-08-29T07:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-30T20:40:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/griddb.net\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1536\" \/>\n\t<meta property=\"og:image:height\" content=\"1536\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Israel\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@GridDBCommunity\" \/>\n<meta name=\"twitter:site\" content=\"@GridDBCommunity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Israel\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\"},\"author\":{\"name\":\"Israel\",\"@id\":\"https:\/\/griddb.net\/en\/#\/schema\/person\/c8a430e7156a9e10af73b1fbb46c2740\"},\"headline\":\"Pandas with Python GridDB SQL Queries\",\"datePublished\":\"2024-08-29T07:00:00+00:00\",\"dateModified\":\"2026-03-30T20:40:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\"},\"wordCount\":661,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/griddb.net\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage\"},\"thumbnailUrl\":\"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg\",\"articleSection\":[\"Blog\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\",\"url\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\",\"name\":\"Pandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT\",\"isPartOf\":{\"@id\":\"https:\/\/griddb.net\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage\"},\"thumbnailUrl\":\"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg\",\"datePublished\":\"2024-08-29T07:00:00+00:00\",\"dateModified\":\"2026-03-30T20:40:57+00:00\",\"description\":\"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage\",\"url\":\"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg\",\"contentUrl\":\"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg\",\"width\":1536,\"height\":1536},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/griddb.net\/en\/#website\",\"url\":\"https:\/\/griddb.net\/en\/\",\"name\":\"GridDB: Open Source Time Series Database for IoT\",\"description\":\"GridDB is an open source time-series database with the performance of NoSQL and convenience of SQL\",\"publisher\":{\"@id\":\"https:\/\/griddb.net\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/griddb.net\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/griddb.net\/en\/#organization\",\"name\":\"Fixstars\",\"url\":\"https:\/\/griddb.net\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/griddb.net\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/griddb.net\/wp-content\/uploads\/2019\/04\/fixstars_logo_web_tagline.png\",\"contentUrl\":\"https:\/\/griddb.net\/wp-content\/uploads\/2019\/04\/fixstars_logo_web_tagline.png\",\"width\":200,\"height\":83,\"caption\":\"Fixstars\"},\"image\":{\"@id\":\"https:\/\/griddb.net\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/griddbcommunity\/\",\"https:\/\/x.com\/GridDBCommunity\",\"https:\/\/www.linkedin.com\/company\/griddb-by-toshiba\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/griddb.net\/en\/#\/schema\/person\/c8a430e7156a9e10af73b1fbb46c2740\",\"name\":\"Israel\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/griddb.net\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4df8cfc155402a2928d11f80b0220037b8bd26c4f1b19c4598d826e0306e6307?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4df8cfc155402a2928d11f80b0220037b8bd26c4f1b19c4598d826e0306e6307?s=96&d=mm&r=g\",\"caption\":\"Israel\"},\"url\":\"https:\/\/griddb.net\/en\/author\/israel\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT","description":"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/","og_locale":"en_US","og_type":"article","og_title":"Pandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT","og_description":"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from","og_url":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/","og_site_name":"GridDB: Open Source Time Series Database for IoT","article_publisher":"https:\/\/www.facebook.com\/griddbcommunity\/","article_published_time":"2024-08-29T07:00:00+00:00","article_modified_time":"2026-03-30T20:40:57+00:00","og_image":[{"width":1536,"height":1536,"url":"https:\/\/griddb.net\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg","type":"image\/jpeg"}],"author":"Israel","twitter_card":"summary_large_image","twitter_creator":"@GridDBCommunity","twitter_site":"@GridDBCommunity","twitter_misc":{"Written by":"Israel","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#article","isPartOf":{"@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/"},"author":{"name":"Israel","@id":"https:\/\/griddb.net\/en\/#\/schema\/person\/c8a430e7156a9e10af73b1fbb46c2740"},"headline":"Pandas with Python GridDB SQL Queries","datePublished":"2024-08-29T07:00:00+00:00","dateModified":"2026-03-30T20:40:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/"},"wordCount":661,"commentCount":0,"publisher":{"@id":"https:\/\/griddb.net\/en\/#organization"},"image":{"@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage"},"thumbnailUrl":"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg","articleSection":["Blog"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/","url":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/","name":"Pandas with Python GridDB SQL Queries | GridDB: Open Source Time Series Database for IoT","isPartOf":{"@id":"https:\/\/griddb.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage"},"image":{"@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage"},"thumbnailUrl":"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg","datePublished":"2024-08-29T07:00:00+00:00","dateModified":"2026-03-30T20:40:57+00:00","description":"We have written before about how to pair pandas dataframes with GridDB before in our article: Using Pandas Dataframes with GridDB. In there, we read from","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.griddb.net\/en\/blog\/pandas-with-python-griddb-sql-queries\/#primaryimage","url":"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg","contentUrl":"\/wp-content\/uploads\/2024\/08\/Gemini_Generated_Image_pfefuhpfefuhpfef.jpg","width":1536,"height":1536},{"@type":"WebSite","@id":"https:\/\/griddb.net\/en\/#website","url":"https:\/\/griddb.net\/en\/","name":"GridDB: Open Source Time Series Database for IoT","description":"GridDB is an open source time-series database with the performance of NoSQL and convenience of SQL","publisher":{"@id":"https:\/\/griddb.net\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/griddb.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/griddb.net\/en\/#organization","name":"Fixstars","url":"https:\/\/griddb.net\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/griddb.net\/en\/#\/schema\/logo\/image\/","url":"https:\/\/griddb.net\/wp-content\/uploads\/2019\/04\/fixstars_logo_web_tagline.png","contentUrl":"https:\/\/griddb.net\/wp-content\/uploads\/2019\/04\/fixstars_logo_web_tagline.png","width":200,"height":83,"caption":"Fixstars"},"image":{"@id":"https:\/\/griddb.net\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/griddbcommunity\/","https:\/\/x.com\/GridDBCommunity","https:\/\/www.linkedin.com\/company\/griddb-by-toshiba"]},{"@type":"Person","@id":"https:\/\/griddb.net\/en\/#\/schema\/person\/c8a430e7156a9e10af73b1fbb46c2740","name":"Israel","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/griddb.net\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4df8cfc155402a2928d11f80b0220037b8bd26c4f1b19c4598d826e0306e6307?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4df8cfc155402a2928d11f80b0220037b8bd26c4f1b19c4598d826e0306e6307?s=96&d=mm&r=g","caption":"Israel"},"url":"https:\/\/griddb.net\/en\/author\/israel\/"}]}},"_links":{"self":[{"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/posts\/46810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/comments?post=46810"}],"version-history":[{"count":2,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/posts\/46810\/revisions"}],"predecessor-version":[{"id":55142,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/posts\/46810\/revisions\/55142"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/media\/30214"}],"wp:attachment":[{"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/media?parent=46810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/categories?post=46810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/griddb.net\/en\/wp-json\/wp\/v2\/tags?post=46810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}