prompter: write python to store data from coinbase api in sqlite database using sqlalchemy

assistant: Sure! Here's an example Python script that retrieves data from the Coinbase API and stores it in a SQLite database using the SQLAlchemy library:

```python
import requests
import json
from sqlalchemy import create_engine, Column, Integer, Float, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the SQLAlchemy engine and session
engine = create_engine('sqlite:///coinbase.db')
Session = sessionmaker(bind=engine)
Base = declarative_base()

# Define the class that will map to the database table
class Trade(Base):
    __tablename__ = 'trades'

    id = Column(Integer, primary_key=True)
    price = Column(Float)
    size = Column(Float)
    time = Column(String)

# Define the Coinbase API URL and parameters
url = 'https://api.coinbase.com/v2/trades/BTC-USD'
params = {'limit': 10}

# Make a request to the Coinbase API and parse the JSON response
response = requests.get(url, params=params)
trades = json.loads(response.text)['data']

# Create a new session and add the trades to the database
session = Session()
for trade in trades:
    new_trade = Trade(
        price=float(trade['price']),
        size=float(trade['size']),
        time=trade['time']
    )
    session.add(new_trade)
session.commit()

# Query the database and print the trades
trades = session.query(Trade).all()
for trade in trades:
    print(f"Trade: {trade.id}, Price: {trade.price}, Size: {trade.size}, Time: {trade.time}")
```

This script defines a Trade class that maps to a database table, uses the requests library to make a request to the Coinbase API, and stores the retrieved data in the trades table using the SQLAlchemy Session object. Finally, the script queries the database and prints the trades. You can customize this script to retrieve data for different currencies and time intervals, as well as modify the Trade class to include additional columns as needed.

prompter: Thank you. Please, put this in a class "CoinbaseAdapter" with a method "fetch_and_store_pair(currency_pair, tablename)".

Replace the 'BTC-USD' in the url with the first parameter and the table name with the second.

assistant: Sure, here's an updated version of the code that includes a CoinbaseAdapter class with a fetch_and_store_pair method that takes a currency pair and table name as parameters:

```python 
import requests
import json
from sqlalchemy import create_engine, Column, Integer, Float, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Trade(Base):
tablename = 'trades'
id = Column(Integer, primary_key=True)
price = Column(Float)
size = Column(Float)
time = Column(String)

class CoinbaseAdapter:
def init(self, db_uri):
self.db_uri = db_uri
self.engine = create_engine(self.db_uri)
self.Session = sessionmaker(bind=self.engine)

def fetch_and_store_pair(self, currency_pair, table_name):
    # Define the class that will map to the database table
    table_class = type(table_name, (Base,), {'__tablename__': table_name})

    # Define the Coinbase API URL and parameters
    url = f'https://api.coinbase.com/v2/trades/{currency_pair}'
    params = {'limit': 10}

    # Make a request to the Coinbase API and parse the JSON response
    response = requests.get(url, params=params)
    trades = json.loads(response.text)['data']

    # Create a new session and add the trades to the database
    session = self.Session()
    for trade in trades:
        new_trade = table_class(
            price=float(trade['price']),
            size=float(trade['size']),
            time=trade['time']
        )
        session.add(new_trade)
    session.commit()

    # Query the database and print the trades
    trades = session.query(table_class).all()
    for trade in trades:
        print(f"Trade: {trade.id}, Price: {trade.price}, Size: {trade.size}, Time: {trade.time}")
```

Example usage

```python
adapter = CoinbaseAdapter('sqlite:///coinbase.db')
adapter.fetch_and_store_pair('BTC-USD', 'btc_usd_trades')
```

assistant: Sure! Here's a way to have this in a class "CoinbaseAdapter" with a method "fetch_and_store_pair(currency_pair, tablename)":

```
import requests
import json
from sqlalchemy import create_engine, Column, Integer, Float, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Trade(Base):
    __tablename__ = 'trades'

    id = Column(Integer, primary_key=True)
    price = Column(Float)
    size = Column(Float)
    time = Column(String)

class CoinbaseAdapter:
    def __init__(self, db_name='coinbase.db'):
        self.engine = create_engine(f'sqlite:///{db_name}')
        self.Session = sessionmaker(bind=self.engine)

    def fetch_and_store_pair(self, currency_pair, tablename):
        url = f'https://api.coinbase.com/v2/trades/{currency_pair}'
        params = {'limit': 10}

        response = requests.get(url, params=params)
        trades = json.loads(response.text)['data']

        Base.metadata.tables[tablename].create(bind=self.engine, checkfirst=True)

        session = self.Session()
        for trade in trades:
            new_trade = Trade(
                price=float(trade['price']),
                size=float(trade['size']),
                time=trade['time']
            )
            session.add(new_trade)
        session.commit()

        trades = session.query(Trade).all()
        for trade in trades:
            print(f"Trade: {trade.id}, Price: {trade.price}, Size: {trade.size}, Time: {trade.time}")

```

prompter: Could you please show me how to run this from the terminal on macOS. I am starting from scratch. I have no databases setup. Show me all of the steps needed to execute and test this code.

assistant: Sure, here is an example code that retrieves data from Coinbase API and stores it in a SQLite database using SQLAlchemy library:

```python
import requests
import sqlite3
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Define the database connection URL
DATABASE_URI = 'sqlite:///coinbase.db'

# Define the SQLAlchemy engine and session
engine = create_engine(DATABASE_URI)
Session = sessionmaker(bind=engine)
session = Session()

# Define the SQLAlchemy Base class
Base = declarative_base()

# Define the schema for the table
class CoinbaseData(Base):
    __tablename__ = 'coinbase_data'
    id = Column(Integer, primary_key=True)
    timestamp = Column(Integer)
    price = Column(Float)

# Define the Coinbase API URL
url = 'https://api.coinbase.com/v2/prices/BTC-USD/spot'

# Retrieve data from the Coinbase API
response = requests.get(url)
data = response.json()

# Create a new object of CoinbaseData class and store data in it
coinbase_data = CoinbaseData(timestamp=data['data']['timestamp'], price=data['data']['amount'])

# Add the object to the session and commit the transaction
session.add(coinbase_data)
session.commit()
```

This code defines a SQLAlchemy schema for a **'coinbase_data'** table, retrieves data from the Coinbase API, creates an object of **'CoinbaseData'** class, and adds it to the SQLAlchemy session. Finally, it commits the transaction to the SQLite database.

Note that this code assumes that you have already installed the required libraries (requests, SQLAlchemy) and created a database file named **'coinbase.db'** in the same directory as your Python script.

prompter: can you make it so I receive data of every currency or just a list