def testhana():
# Assume HANA host id is linux-n5es and instance no is 00.
# Using SYSTEM user with the password defined at HANA install
conn = dbapi.connect('linux-n5es', 30015, 'SYSTEM', 'Sus3r0ck5')
# Check if database connection was successful or not
print 'Connected:',conn.isconnected()
# Create a schema and table
c = conn.cursor()
stmnt = 'create schema TEST_SCHEMA'
c.execute(stmnt)
stmnt = 'create column table TEST_SCHEMA.TABLE1 (ID integer, NAME varchar(10))'
c.execute(stmnt)
print 'Schema and table created'
# Insert some data into table
stmnt = 'insert into TEST_SCHEMA.TABLE1 values (?,?)'
c.execute(stmnt, (1,'A'))
c.execute(stmnt, (2,'B'))
c.execute(stmnt, (3,'C'))
print '3 records inserted into table'
# Disconnect from database
conn.commit()
conn.close()
# Check if database connection was successful or not
print 'Connected:',conn.isconnected()
# Assume HANA host id is linux-n5es and instance no is 00.
# Using SYSTEM user with the password defined at HANA install
conn = dbapi.connect('linux-n5es', 30015, 'SYSTEM', 'Sus3r0ck5')
# Check if database connection was successful or not
print 'Connected:',conn.isconnected()
# Count the number of rows in the table
c = conn.cursor()
stmnt = 'SELECT Count(*) FROM TEST_SCHEMA.TABLE1'
c.execute(stmnt)
result = c.fetchall()
for row in result:
reccount = row[0]
print reccount,'records are in the table'
# Delete the schema and table
stmnt = 'drop schema TEST_SCHEMA CASCADE'
c.execute(stmnt)
print 'Schema and table deleted'
return;
def test_connection_params(expected_err,
ip='127.0.0.1',
port=5000,
database='master',
user='******',
password='******',
clustered=False,
use_ssl=False):
try:
dbapi.connect(ip, port, database, user, password, clustered,
use_ssl)
except Exception as e:
if expected_err not in repr(e):
raise Exception("bad error message")
def connect_dbapi(clustered=False, use_ssl=False):
port = (3109 if use_ssl else 3108) if clustered else (
5001 if use_ssl else 5000)
return dbapi.connect(ip, port, 'master', 'sqream', 'sqream', clustered,
use_ssl)
def connect_dbapi(clustered=False, use_ssl=False):
args = sys.argv
ip = args[1] if len(args) > 1 else '127.0.0.1'
port = (3109 if use_ssl else 3108) if clustered else (5001 if use_ssl else 5000)
return dbapi.connect(ip, port, 'master', 'sqream', 'sqream', clustered, use_ssl)
def __init__(self, sys, port, user, pwd):
self.user = user
try:
self.conn = dbapi.connect(sys, port, user, pwd)
self.err = None
except dbapi.Error as e:
self.conn = None
self.err = "Login error: %s" % e[1]
def __connect(self):
"""Connect to the database"""
self.connection = Database.connect(
make_connection_string(self.settings_dict),
self.command_timeout
)
connection_created.send(sender=self.__class__)
return self.connection
def __init__(self, ip, port, user, password):
self.info = (ip, port, user, password)
self.connection = None
print('Start connect hana: {} {} {}'.format(*self.info[:3]))
try:
self.connection = dbapi.connect(*self.info)
except Exception as e:
print e
def _cursor(self):
if self.connection is None:
self.connection = Database.connect(
make_connection_string(self.settings_dict),
self.command_timeout
)
connection_created.send(sender=self.__class__)
return Database.Cursor(self.connection)
def __init__(self, ip, port, user, password):
self.info = (ip, port, user, password)
self.connection = None
print('Start connect hana: {} {} {}'.format(*self.info[:3]))
try:
self.connection = dbapi.connect(*self.info)
except Exception as e:
print e
def test_cursor_through_clustered(self):
con_clustered = dbapi.connect(pytest.ip,
3108,
'master',
'sqream',
'sqream',
clustered=True)
cur = con_clustered.cursor()
assert cur.execute("select 1").fetchall()[0][0] == 1
def get_connection():
"""
(NoneType) -> (Object)
Method that will return connection to the database using given credentials.
"""
return dbapi.connect(credentials.SERVER,\
credentials.PORT,\
credentials.USER,\
credentials.PASSWORD)
def get_connection(self): """ (obj) -> (obj) Method that will return connection to the database using given credentials. """ return dbapi.connect(credentials.SERVER,\ credentials.PORT,\ credentials.USER,\ credentials.PASSWORD)
def get_connection(): """ (NoneType) -> (Object) Method that will return connection to the database using given credentials. """ return dbapi.connect(credentials.SERVER,\ credentials.PORT,\ credentials.USER,\ credentials.PASSWORD)
def connection(self):
if self.db_type == "mysql":
import mysql.connector
return mysql.connector.connect(user=self.user,
database=self.database,
host=self.host)
elif self.db_type == "hana":
import dbapi
return dbapi.connect(address=self.host,
port=int(self.port),
user=self.user,
password=self.password)
else:
raise Exception(
"Unknown database type setting in application configuration.")
def connect(self, hostname, instance, user, password):
if not hostname:
self.printUtil.warn("input needed", "You must supply a hostname to use for a connection")
return False
if not instance:
self.printUtil.warn("input needed", "You must supply a instance to use for a connection")
return False
if not user:
self.printUtil.warn("input needed", "You must supply a user to use for a connection")
return False
if not password:
self.printUtil.warn("input needed", "You must supply a password to use for a connection")
return False
#Create the user and grant access to monitoring views
try:
#Create the database connetion to HANA
self.connection = dbapi.connect(hostname, int('3' + instance + '15'), user, password)
#get a cursor
self.cursor = self.connection.cursor()
if not self.testConnection():
self.printUtil.err("CONNECTION", "Test query failed")
self.disconnect()
sys.exit(1)
except dbapi.Error as e:
self.printUtil.err("CONNECTION", "Unable to connect to DB")
print '"' + e[1] + '"'
sys.exit(1)
return True
#!/usr/bin/python
import dbapi
import smtplib
from datetime import datetime
from email.mime.text import MIMEText
try:
con = dbapi.connect('localhost',30015, '<HANAUSERNAME>', '<HANAPASSWORD>')
cur = con.cursor()
cur.execute('select * from metric2.m2_outgoing_email WHERE sent is not null')
email = cur.fetchall()
s = smtplib.SMTP('smtp.1and1.com', 587)
s.login('<SMTPUSERNAME>', '<SMTPPASSWORD>')
for item in email:
msg = MIMEText(item[4])
msg['Subject'] = item[3]
msg['From'] = item[2]
msg['To'] = item[1]
s.sendmail(item[2], item[1], msg.as_string())
strSQL = "UPDATE metric2.m2_outgoing_email SET sent = '" + str(datetime.now()) + "' WHERE id = " + str(item[0])
cur.execute(strSQL)
except Exception, error:
print str(error)
finally:
s.close()
from dbapi import connect
from lxml.etree import parse
s = connect('dbi://*****:*****@localhost:5050/test')
g = s.cursor()
g.execute('for $item in doc("ot")//v'+\
' where contains($item,"begat") return $item')
items = []
for z in g.fetchall():
items.append(parse(z))
g = z[0]
for name,value in g.docinfo.__dict__.items():
print name,value
import time
#arguments
import numpy as np, cv
import cv2
#SERVER_ADRESS='hdbspatial.wdf.sap.corp'
#SERVER_ADRESS='10.18.206.186'
SERVER_ADRESS='10.68.88.71'
SERVER_PORT=30015
USER_NAME='System'
#PASSWORD='******'
PASSWORD='******'
#connect to hana database
conn=dbapi.connect(SERVER_ADRESS,SERVER_PORT,USER_NAME,PASSWORD)
cursor=conn.cursor()
#SCHEME = "ESA_VECTOR_TEST"
SCHEME = "ESA_EXP"
#SCHEME = "ESA_VECTOR_EXP_NEW"
VECTOR_DATA = "NDVI_VECTOR"
#META = "NDVI_META"
BLOB_DATA = "NDVI_BLOB"
PRODUCT_DATA = "PRODUCT_DATA"
def exec_query(query):
# print query
try:
cursor.execute(query)
except Exception,ex:
import dbapi
import flask
from flask import request, jsonify
app = flask.Flask(__name__)
app.config["DEBUG"] = True
# alustetaan tietokanta temperature-oliolla demoa varten
temperature = {'name': 'temperature', 'value': 0, 'unit': 'Celsius'}
dbapi.connect()
dbapi.create(temperature)
# Kaikkien sensoreiden luettelo.
@app.route('/api/sensors', methods=['GET', 'HEAD', 'OPTIONS'])
def api_sensors():
if request.method == 'GET':
result = dbapi.findAll()
return jsonify(result)
elif request.method == 'HEAD':
return jsonify(success=True)
elif request.method == 'OPTIONS':
resp = flask.Response()
resp.headers['Allow'] = 'GET,HEAD,OPTIONS'
return jsonify(resp)
# Yksittäisen sensorin tietueen haku (tässä tapauksessa) nimen perusteella
# Huomaa URL-kuvion muuttuja <sensor_name>, joka yhdistetään routeen liitetyn
# funktion sensor_name-parametriin
@app.route('/api/sensors/<sensor_name>', methods=['GET', 'PUT', 'OPTIONS'])
import dbapi
# Assume HANA host id is linux-n5es and instance no is 00.
# Using SYSTEM user with the password defined at HANA install
conn = dbapi.connect('linux-n5es', 30015, 'SYSTEM', 'Sus3r0ck5')
# Check if database connection was successful or not
print conn.isconnected()
#!/usr/bin/python
import json
import datetime
import os
import dbapi
filename="3.json"
###sap hana server information
serverAddress='10.128.84.28'
serverPort=30015
userName='******'
passWord='******'
#connect to hana database
conn=dbapi.connect(serverAddress,serverPort,userName,passWord)
#query for questions
query_q="UPSERT STOF.QUESTIONS(QID,CREATE_DATE,TITLE,BODY,SCORE,ANSWER_COUNT,DOWN_VOTE_COUNT,UP_VOTE_COUNT,IS_ANSWERED,ACCEPTED_ANSWER_ID,COMMENT_COUNT,VIEW_COUNT,USERID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) WITH PRIMARY KEY"
query_tag="UPSERT STOF.TAGS(POSTID,IDTYPE,TAG) VALUES(?,?,?) WITH PRIMARY KEY"
query_owner="UPSERT STOF.USERS(USERID,USER_TYPE,REPUTATION,ACCEPT_RATE,PROFILE_IMAGE,DISPLAY_NAME,LINK) values(?,?,?,?,?,?,?) with primary key"
query_comment="UPSERT STOF.COMMENTS(COMMENT_ID,OWNERID,CREATION_DATE,POST_ID,BODY,SCORE,REPLY_TO_USER) values(?,?,?,?,?,?,?) with primary key"
query_answer="UPSERT STOF.ANSWERS(ANSWER_ID,QUESTION_ID,TITLE,BODY,CREATION_DATE,SCORE,IS_ACCEPTED,DOWN_VOTE_COUNT,UP_VOTE_COUNT,COMMENT_COUNT,USERID) VALUES(?,?,?,?,?,?,?,?,?,?,?) with PRIMARY KEY"
#######################usert a user########################
def upsertuser(user):
if user.has_key("reputation"):
reputation=user["reputation"]
else:
reputation=0
if user.has_key("accept_rate"):
accept_rate=user["accept_rate"]
else:
accept_rate=0
try:
def _cursor(self):
if self.connection is None:
self.connection = Database.connect(make_connection_string(self.settings_dict), self.command_timeout)
connection_created.send(sender=self.__class__)
return Database.Cursor(self.connection)
def connection_tests(build_dir=None, ip=None):
print("Restart the server when a connection is open within 30 seconds")
con = connect_dbapi(False, False)
'''
print("stopped sqream server, sleeping 5 seconds")
start_stop('stop', build_dir, ip)
sleep(5)
start_stop('start', build_dir, ip)
print("started sqream server, trying to run a statement")
try:
con.execute("select 1")
except Exception as e:
if "SQreamd connection interrupted" not in repr(e):
raise Exception("bad error message")
# '''
def test_connection_params(expected_err,
ip='127.0.0.1',
port=5000,
database='master',
user='******',
password='******',
clustered=False,
use_ssl=False):
try:
dbapi.connect(ip, port, database, user, password, clustered,
use_ssl)
except Exception as e:
if expected_err not in repr(e):
raise Exception("bad error message")
print("Connection tests - wrong ip")
# test_connection_params('123.4.5.6', 5000, 'master', 'sqream', 'sqream', False, False), "perhaps wrong IP?")
try:
dbapi.connect('123.4.5.6', 5000, 'master', 'sqream', 'sqream', False,
False)
except Exception as e:
if "perhaps wrong IP?" not in repr(e):
raise Exception("bad error message")
print("Connection tests - wrong port")
try:
dbapi.connect('127.0.0.1', 6000, 'master', 'sqream', 'sqream', False,
False)
except Exception as e:
if "Connection refused" not in repr(e):
raise Exception("bad error message")
print("Connection tests - wrong database")
try:
dbapi.connect('127.0.0.1', 5000, 'wrong_db', 'sqream', 'sqream', False,
False)
except Exception as e:
if "Database 'wrong_db' does not exist" not in repr(e):
raise Exception("bad error message")
print("Connection tests - wrong username")
try:
dbapi.connect('127.0.0.1', 5000, 'master', 'wrong_username', 'sqream',
False, False)
except Exception as e:
if "role 'wrong_username' doesn't exist" not in repr(e):
raise Exception("bad error message")
print("Connection tests - wrong password")
try:
dbapi.connect('127.0.0.1', 5000, 'master', 'sqream', 'wrong_pw', False,
False)
except Exception as e:
if "wrong password for role 'sqream'" not in repr(e):
raise Exception("bad error message")
print("Connection tests - close() function")
con = connect_dbapi()
con.close()
try:
con.execute('select 1')
except Exception as e:
if "Connection has been closed" not in repr(e):
raise Exception("bad error message")
print("Connection tests - close_connection() function")
con = connect_dbapi()
con.close_connection()
try:
con.execute('select 1')
except Exception as e:
if "Connection has been closed" not in repr(e):
raise Exception("bad error message")
print(
"Connection tests - Trying to close a connection that is already closed with close()"
)
con = connect_dbapi()
con.close()
try:
con.close()
except Exception as e:
if "Trying to close a connection that's already closed" not in repr(e):
raise Exception("bad error message")
print(
"Connection tests - Trying to close a connection that is already closed with close_connection()"
)
con = connect_dbapi()
con.close_connection()
try:
con.close_connection()
except Exception as e:
if "Trying to close a connection that's already closed" not in repr(e):
raise Exception("bad error message")
print("Connection tests - negative test for use_ssl=True")
try:
dbapi.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', False,
True)
except Exception as e:
if "Using use_ssl=True but connected to non ssl sqreamd port" not in repr(
e):
raise Exception("bad error message")
print("Connection tests - positive test for use_ssl=True")
con = connect_dbapi(False, True)
res = con.execute('select 1').fetchall()[0][0]
if res != 1:
if f'expected to get 1, instead got {res}' not in repr(e):
raise Exception("bad error message")
print("Connection tests - negative test for clustered=True")
try:
dbapi.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', True,
False)
except Exception as e:
if "Connected with clustered=True, but apparently not a server picker port" not in repr(
e):
raise Exception("bad error message")
print("Connection tests - positive test for clustered=True")
con = connect_dbapi(True, False)
res = con.execute('select 1').fetchall()[0][0]
if res != 1:
if f'expected to get 1, instead got {res}' not in repr(e):
raise Exception("bad error message")
print("Connection tests - both clustered and use_ssl flags on True")
con = connect_dbapi(True, True)
res = con.execute('select 1').fetchall()[0][0]
if res != 1:
if f'expected to get 1, instead got {res}' not in repr(e):
raise Exception("bad error message")
# PostGISDroid - upload android location to PostGIS database
'''
PostGISDroid - script to upload android location to PostGIS database.
Released under BSD license
Copyright Tom Holderness 2011.
version 1.0
'''
import android, time, dbapi
droid = android.Android()
conn = dbapi.connect(host="192.168.0.8", user="******", password="******", database="android")
cursor = conn.cursor()
cursor.execute("SELECT id, provider, accuracy, altitude, speed FROM droidtrack")
data = cursor.fetchall()
cursor.close()
conn.close()
print data
exit(0)
import gc
import re
import gzip
import json
import datetime
import dbapi
import pickle
def deal_num(s):
while len(s) < 8:
s = '0' + s
return s
start = datetime.datetime.now()
client = dbapi.connect('10.1.0.160',30015,'SYSTEM','Xia550505')
cursor = client.cursor()
#ret=cursor.execute('create table sessions( matrix CLOB, ttt varchar(10)) ')
client2 = dbapi.connect('10.1.0.160',30015,'SYSTEM','Xia550505')
cursor2 = client2.cursor()
urls = {}
n = 0
kinds = open('/home/workload/workload/analysis/base_url.txt', 'r+')
for line in kinds:
now = line.split()
urls[now[0]] = n
n += 1
kinds.close()
session = {}
