user: 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.

user: 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')
```