a python package to connect db and data sources like google sheets
Project description
zackdbtool
a python package to connect db and data sources like google sheets
Set up
make a json file that contains the database credentials.
see example: dbcredentials_example.json (memory and filedb are for sqlite quick usage, you can add more like test1)
{
"test1":{
"conn_engine":"mysql+pymysql",
"host":"192.168.0.12",
"port":3306,
"user":"test_user",
"passwd":"********",
"db":"public",
"testtable":"user"
},
"memory":{
"conn_engine":"sqlite",
"host":"/:memory:",
"port":"",
"user":"",
"passwd":"",
"db":"test",
"testtable":""
},
"filedb":{
"conn_engine":"sqlite",
"host":"/filedb.db",
"port":"",
"user":"",
"passwd":"",
"db":"test",
"testtable":""
}
}
save the file to a location, e.g. $HOME/.credentials/dbcredentials_example.json
save your google service account json file to a location e.g. $HOME/.credentials/service_account.json
add two environment variable for those two files:
DB_CREDENTIALS_PATH=$HOME/.credentials/dbcredentials_example.json
SERVICE_ACCOUNT_JSON_PATH=$HOME/.credentials/service_account.json
in linux/mac you can (NOTE: in vscode jupyter, it doesn't read the .bashrc, you can either add a .env or use the same file path below)
vi $HOME/.bashrc
inseart two rows to the end of the file and restart the terminal
export DB_CREDENTIALS_PATH=$HOME/.credentials/dbcredentials_example.json
export SERVICE_ACCOUNT_JSON_PATH=$HOME/.credentials/service_account.json
if you run your app as a systemd service:
vi /etc/systemd/system/YOURSERVICENAME.service
add environment variable
[Service]
Environment=DB_CREDENTIALS_PATH=$HOME/.credentials/dbcredentials.json
Environment=SERVICE_ACCOUNT_JSON_PATH=$HOME/.credentials/ga-service-account.json
then reload daemon
systemctl daemon-reload
in windows, use the search bar to search "environment variable" add the two variables to user/system variables
if you want to build app in docker
docker run -e DB_CREDENTIALS_PATH=dbcredentials.json -e SERVICE_ACCOUNT_JSON_PATH=ga-service-account.json dockerimagename
download the repository, and
pip install .
or through pipy
pip install zackdbtool
Connect a database
from zackdbtool import db_engine
import pandas as pd
dbsource= 'mydb'
engine = db_engine(dbsource, db='test')
df = pd.read_sql(f'SELECT * FROM user limit 10', engine)
print(df)
if you don't have a database running you can use memory or filedb to create a testing database without any setup
from zackdbtool import db_engine
import pandas as pd
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
dbsource= 'memory'
engine = db_engine(dbsource)
Base = declarative_base()
sesson = sessionmaker(bind=engine)
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
Base.metadata.create_all(engine)
ed_user = User(name="ed", fullname="Ed Jones")
session.add(ed_user)
session.commit()
with engine.connect() as c:
data = c.execute('select * from users').fetch_all()
print(data)
use google services
view_id = 'your site view id'
metrics = ['ga:users','ga:newUsers','ga:pageViews','ga:sessions']
# find more setting at https://ga-dev-tools.web.app/query-explorer/
dimensions = ['ga:month', 'ga:year']
start_date = '2000-01-01'
df = gareports(view_id, metrics, dimensions, start_date=start_date)
google sheet
SAMPLE_SPREADSHEET_ID_PV = 'your google sheet id'
SAMPLE_RANGE_NAME_PV = 'your sheet name'
dedupCol = 'idcolname'
skiprows = 0
dfpv = readgooglesheets(SAMPLE_SPREADSHEET_ID_PV, SAMPLE_RANGE_NAME_PV,dedupCol = dedupCol, skiprows = skiprows)
print(dfpv.head())
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.