<|im_start|>system
You are a helpful AI assistant.<|im_end|>
<|im_start|>user
write python to store data from coinbase api in sqlite database using sqlalchemy<|im_end|>
<|im_start|>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.<|im_end|>
<|im_start|>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.<|im_end|>
<|im_start|>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')
```<|im_end|>
<|im_start|>user
can you make it so I receive data of every currency or just a list<|im_end|>
<|im_start|>assistant
Yes, you can modify the code to retrieve data for every currency or a list of currencies by changing the Coinbase API URL to include the currency symbols. Here is an example code that retrieves data for a list of currencies and stores it in the SQLite database:

```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**

```python
DATABASE_URI = 'sqlite:///coinbase.db'
```

**Define the SQLAlchemy engine and session**

```python
engine = create_engine(DATABASE_URI)
Session = sessionmaker(bind=engine)
session = Session()
```

**Define the SQLAlchemy Base class**

```python
Base = declarative_base()
```

**Define the schema for the table**

```python
class CoinbaseData(Base):
tablename = 'coinbase_data'
id = Column(Integer, primary_key=True)
timestamp = Column(Integer)
currency = Column(String)
price = Column(Float)
```

**Define the list of currencies to retrieve**

```python
currencies = ['BTC-USD', 'ETH-USD', 'LTC-USD']
```

**Loop through the list of currencies and retrieve data from the Coinbase API**

```Python
for currency in currencies:
url = f'https://api.coinbase.com/v2/prices/{currency}/spot'
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'], currency=currency, price=data['data']['amount'])

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

This code defines a 'coinbase_data' table schema with three columns: timestamp, currency, and price. It retrieves data for the list of currencies defined in the 'currencies' list, creates a new object of 'CoinbaseData' class for each currency, and stores it in the SQLite database using SQLAlchemy.<|im_end|>