You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
124 lines
3.5 KiB
124 lines
3.5 KiB
import csv |
|
import io |
|
import urllib.request |
|
import urllib.parse |
|
import ssl |
|
|
|
def runsql(sql: str) -> csv.DictReader: |
|
ssl._create_default_https_context = ssl._create_unverified_context |
|
sql_escaped = urllib.parse.urlencode({'sql': sql}) |
|
URL = 'https://driver-observer.thi.de/retrieve_arbitrary_sql.php' |
|
|
|
req = urllib.request.Request('{}?{}'.format(URL, sql_escaped)) |
|
req.add_header('Auth', 'Qnb7jfeGZM') |
|
|
|
with urllib.request.urlopen(req) as res: |
|
return csv.DictReader(io.StringIO(res.read().decode('utf-8'), newline='')) |
|
#return (io.StringIO(res.read().decode('utf-8'), newline='')) |
|
|
|
|
|
############################### |
|
|
|
import pandas as pd |
|
from sqlalchemy import create_engine |
|
from sshtunnel import SSHTunnelForwarder |
|
import urllib.parse |
|
|
|
server = SSHTunnelForwarder( |
|
('driver-observer.thi.de', 22) |
|
, ssh_password="GP$Tracker@THI"#urllib.parse.quote_plus("GP$Tracker@THI")#"GP$Tracker@THI"#"GP$Tracker@THI"# |
|
, ssh_username="ziegmann" |
|
, remote_bind_address=('localhost', 3306) |
|
) |
|
server.start() |
|
|
|
engine = create_engine( |
|
f'mysql+pymysql://observer:Zieg7+89@localhost/observer:{server.local_bind_port}' |
|
, echo=True |
|
) |
|
dbs = engine.execute('SHOW DATABASES;') |
|
for db in dbs: |
|
print(db) |
|
|
|
server.stop() |
|
|
|
|
|
|
|
|
|
|
|
|
|
from sshtunnel import SSHTunnelForwarder |
|
import MySQLdb as db |
|
import pandas as pd |
|
|
|
host = "driver-observer.thi.de" |
|
localhost = "127.0.0.1" |
|
ssh_username = "ziegmann" |
|
ssh_password = "GP$Tracker@THI" |
|
|
|
# databas variabler |
|
user = "observer" |
|
password = "Zieg7+89" |
|
database = "computeddata" |
|
|
|
def query(q): |
|
# use ssh_pkey instead of ssh_password |
|
with SSHTunnelForwarder( |
|
(host, 22), |
|
ssh_username=ssh_username, |
|
ssh_password=ssh_password, |
|
remote_bind_address=(localhost, 3306) |
|
) as server: |
|
conn = db.connect(host=localhost, |
|
port=server.local_bind_port, |
|
user=user, |
|
passwd=password, |
|
db=database) |
|
return pd.read_sql_query(q, conn) |
|
|
|
begin ="""2018-01-03""" |
|
end = """2018-01-04""" |
|
|
|
#run sql query |
|
sql = """select collection_type,sum(order_amount) from pz_orders where order_date between {} and {} and print = 'y' group by collection_type""".format(början,slut) |
|
df = query(sql) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
from sshtunnel import SSHTunnelForwarder |
|
import MySQLdb as db |
|
import pandas as pd |
|
import urllib.parse urllib.parse.quote_plus("") |
|
|
|
|
|
|
|
def query(q): |
|
with SSHTunnelForwarder( |
|
('driver-observer.thi.de', 22) |
|
, ssh_password="GP$Tracker@THI"#urllib.parse.quote_plus("") |
|
, ssh_username="ziegmann" |
|
, remote_bind_address=('localhost', 3306) |
|
# (host, 22), |
|
# ssh_username=ssh_username, |
|
# ssh_private_key=ssh_private_key, |
|
# remote_bind_address=(localhost, 3306) |
|
) as server: |
|
conn = db.connect(host="localhost" |
|
, port=server.local_bind_port |
|
, user="ziegmann" |
|
, password = "Zieg7+89"#urllib.parse.quote_plus("Zieg7+89") |
|
, db='mysql -p observer' |
|
) |
|
# user=user, |
|
# passwd=password, |
|
# db=database) |
|
return pd.read_sql_query(q, conn) |
|
|
|
query("select distinct setup_id from computeddata order by setup_id asc") |
|
|
|
|