16862

Exporting SQL query results to pandas dataframe

Question:

id date temp prcp 1 2015-01-01 -27.18 0 1 2015-01-02 -25.9 1.03 1 2015-01-03 -17.89 9.44 1 2015-01-04 -17.89 9.44 1 2015-01-05 -17.89 9.44 import dataset import pandas as pd db = dataset.connect(path_to_database_on_AWS) res = db.query(SELECT * FROM tbl WHERE id=1 and date >= '2015-01-03' and date <= '2015-01-05') pd.read_sql(res, con=db)

In the above code, I am using the <a href="https://dataset.readthedocs.io/en/latest/api.html#dataset.Database.query" rel="nofollow">query</a> method from the python dataset library to read in from a table and then want to export the results as a pandas dataframe, however, I get this bug:

*** AttributeError: 'Database' object has no attribute 'cursor'

How do I export query results to pandas dataframe?

Answer1:

You could do something like this:

import sqlite3 import pandas as pd con = sqlite3.connect('path_to_your_sql') myFrames = pd.read_sql_query('your query', con)

Edit: for non sqlite db you could use this for the connection:

from sqlalchemy import create_engine con = create_engine('dialect+driver://username:password@host:port/database')

<a href="http://docs.sqlalchemy.org/en/latest/core/engines.html" rel="nofollow">docs for create_engine</a>

Recommend

  • PostgreSQL - make two transactions run at the same time
  • Will changing column from timestamp to timestamptz lock the table?
  • How to get average values for time intervals in Postgres
  • How to resolve permission denied maybe missing internet permission?
  • Dynamically switching connect in Modelica
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • Redux Form - Not able to type anything in input
  • Query to find the duplicates between the name and number in table
  • Access variable of ScriptContext using Nashorn JavaScript Engine (Java 8)
  • How Lists (specifically, RecyclerView with CardViews) in Android work
  • Code in Job's Script Block after Start-Process Does not Execute
  • How can I extract results of aggregate queries in slick?
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Play WS (2.2.1): post/put large request
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Get data from AJAX - How to
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Initializer list vs. initialization method
  • Spring security and special characters
  • Trying to switch camera back to front but getting exception
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • SQL merge duplicate rows and join values that are different
  • Acquiring multiple attributes from .xml file in c#
  • Free memory of cv::Mat loaded using FileStorage API
  • How do you join a server to an Active Directory (domain)?
  • Angular 2 constructor injection vs direct access
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • Programmatically clearing map cache
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal