def get_mysql_connection():
db_settings = secret_settings.get(
'DATABASE'
)['default'] if settings.IS_DEV else settings.DATABASES['default']
db = None
ssl = None
if 'OPTIONS' in db_settings and 'ssl' in db_settings['OPTIONS']:
ssl = db_settings['OPTIONS']['ssl']
if settings.IS_APP_ENGINE_FLEX:
# Connecting from App Engine
db = connect(host='localhost',
unix_socket=settings.DB_SOCKET,
db=db_settings['NAME'],
user=db_settings['USER'],
password=db_settings['PASSWORD'])
else:
db = connect(host=db_settings['HOST'],
port=db_settings['PORT'],
db=db_settings['NAME'],
user=db_settings['USER'],
passwd=db_settings['PASSWORD'],
ssl=ssl)
return db
def connect(driver,
database,
user,
password,
host,
port,
session_cfg={},
auth=""):
if driver == "mysql":
# NOTE: use MySQLdb to avoid bugs like infinite reading:
# https://bugs.mysql.com/bug.php?id=91971
from MySQLdb import connect
return connect(user=user,
passwd=password,
db=database,
host=host,
port=int(port))
elif driver == "hive":
from impala.dbapi import connect
conn = connect(user=user,
password=password,
database=database,
host=host,
port=int(port),
auth_mechanism=auth)
conn.default_db = database
conn.session_cfg = session_cfg
return conn
elif driver == "maxcompute":
from sqlflow_submitter.maxcompute import MaxCompute
return MaxCompute.connect(database, user, password, host)
raise ValueError("unrecognized database driver: %s" % driver)
def connect_with_data_source(driver_dsn):
driver, dsn = driver_dsn.split("://")
if driver == "mysql":
# NOTE: use MySQLdb to avoid bugs like infinite reading:
# https://bugs.mysql.com/bug.php?id=91971
from MySQLdb import connect
user, passwd, host, port, database, config = parseMySQLDSN(dsn)
conn = connect(user=user,
passwd=passwd,
db=database,
host=host,
port=int(port))
elif driver == "hive":
from impala.dbapi import connect
user, passwd, host, port, database, auth, session_cfg = parseHiveDSN(
dsn)
conn = connect(user=user,
password=passwd,
database=database,
host=host,
port=int(port),
auth_mechanism=auth)
conn.session_cfg = session_cfg
conn.default_db = database
elif driver == "maxcompute":
from sqlflow_submitter.maxcompute import MaxCompute
user, passwd, address, database = parseMaxComputeDSN(dsn)
conn = MaxCompute.connect(database, user, passwd, address)
else:
raise ValueError(
"connect_with_data_source doesn't support driver type {}".format(
driver))
conn.driver = driver
return conn
def deregister(args, conf):
# Drop the user's data table from the user's account
username = args.getfirst("user")
passwd = args.getfirst("pass")
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = username, \
passwd = passwd, \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
table_name = username.replace("`", "``") + "_data"
cur.execute("DROP TABLE `" + table_name + "`;")
cur.close()
db.close()
# Connect with a different account for dropping the user
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = conf["GATE_KEEPER_USERNAME"], \
passwd = conf["GATE_KEEPER_PASSWD"], \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
db.close()
return ServiceResult("application/json", \
jsonsaves({"success": True, "message": "Dropped user from system"}))
def _connect_(self, times=3):
"""
:param times: 连接次数
:return: 返回连接状态
"""
if self.default:
for i in range(times):
try:
if doThing == 1:
"""使用模块 mysql.connector"""
# self.config["allowNativePasswords"] = True
self.connect = connect(**
self.config) # mysql.connector
elif doThing == 2:
"""使用模块 MySQLdb"""
self.mysql_db()
self.connect = connect(**self.config) # MySQLdb
else:
return False
return True
except Exception as e:
print(e)
if i + 1 == times:
return False
else:
return False
def get_mysql_connection():
env = os.getenv('SERVER_SOFTWARE')
db_settings = secret_settings.get('DATABASE')['default']
db = None
ssl = None
if 'OPTIONS' in db_settings and 'ssl' in db_settings['OPTIONS']:
ssl = db_settings['OPTIONS']['ssl']
if env and env.startswith(
'Google App Engine/'): # or os.getenv('SETTINGS_MODE') == 'prod':
# Connecting from App Engine
db = connect(
unix_socket='/cloudsql/<YOUR-APP-ID>:<CLOUDSQL-INSTANCE>',
db='',
user='',
)
else:
db = connect(host=db_settings['HOST'],
port=db_settings['PORT'],
db=db_settings['NAME'],
user=db_settings['USER'],
passwd=db_settings['PASSWORD'],
ssl=ssl)
return db
def test_delete_profile(self):
"""
tests the storage.delete() method removes and commits obj to database
for an object from the Profile class
"""
# connect to MySQL database through MySQLdb and get initial count
db = connect(host=ION_MYSQL_HOST,
user=ION_MYSQL_USER,
passwd=ION_MYSQL_PWD,
db=ION_MYSQL_DB)
cur = db.cursor()
cur.execute("""SELECT * FROM profiles""")
objs_for_count1 = cur.fetchall()
# creates new instance of Profile
new_obj = Profile()
# tests that the new object is of type Profile
self.assertIs(type(new_obj), Profile)
# adds all attributes required for testing
# (id should be set by primary key)
# (created_at, updated_at should be set by datetime)
new_obj.name = "test_name"
new_obj.email = "*****@*****.**"
# save the object with BaseModel save method
# save method calls storage.new() and storage.save()
new_obj.save()
# closes connection to database and restarts connection with MySQLdb
cur.close()
db.close()
db = connect(host=ION_MYSQL_HOST,
user=ION_MYSQL_USER,
passwd=ION_MYSQL_PWD,
db=ION_MYSQL_DB)
cur = db.cursor()
cur.execute("""SELECT * FROM profiles""")
objs_for_count2 = cur.fetchall()
# tests that there is one more obj saved to profiles table in db
self.assertEqual(len(objs_for_count1) + 1, len(objs_for_count2))
# delete the object with BaseModel delete method
# delete instance method calls storage.delete() and storage.save()
new_obj.delete()
# closes connection to database and restarts connection with MySQLdb
cur.close()
db.close()
db = connect(host=ION_MYSQL_HOST,
user=ION_MYSQL_USER,
passwd=ION_MYSQL_PWD,
db=ION_MYSQL_DB)
cur = db.cursor()
cur.execute("""SELECT * FROM profiles""")
objs_for_count3 = cur.fetchall()
# tests that there is one less obj in profiles table in db
self.assertEqual(len(objs_for_count2) - 1, len(objs_for_count3))
self.assertEqual(len(objs_for_count1), len(objs_for_count3))
# closes the connection
cur.close()
db.close()
def __init__(self,
driver,
host,
port,
database,
username,
password,
logger):
'''
Initialize database driver: connect the database,
create connection and cusror objects.
'''
self.logger = logger
self.connection = None
self.cursor = None
self.driver = driver
if not database:
database = Database.default_database[self.driver]
# user supplied empty password -> prompt
if password == '':
password = getpass()
# user supplied no password -> query environment or use empty string
if password == None:
try:
password = os.environ['DATABASE_PASSWORD']
except KeyError:
password = ''
if self.driver == SQL.MYSQL:
from MySQLdb import connect
self.connection = connect(host=host,
port=port,
user=username,
passwd=password,
db=database)
elif self.driver == SQL.POSTGRESQL:
from psycopg2 import connect
self.connection = connect(host=host,
port=port,
user=username,
password=password,
database=database)
elif self.driver == SQL.SQLITE3:
from sqlite3 import connect
database = os.path.expanduser(database)
if (not os.path.isfile(database) or
not os.access(database, os.R_OK)):
raise RuntimeError('cannot read from file %s' % database)
self.connection = connect(database)
# the following fixes
# 'sqlite3.OperationalError: Could not decode to UTF-8'
self.connection.text_factory = str
else:
raise ValueError('unknown database driver %s.' % self.driver)
self.cursor = self.connection.cursor()
def _db_login(dbhost=CFG_DATABASE_HOST, relogin=0):
"""Login to the database."""
## Note: we are using "use_unicode=False", because we want to
## receive strings from MySQL as Python UTF-8 binary string
## objects, not as Python Unicode string objects, as of yet.
## Note: "charset='utf8'" is needed for recent MySQLdb versions
## (such as 1.2.1_p2 and above). For older MySQLdb versions such
## as 1.2.0, an explicit "init_command='SET NAMES utf8'" parameter
## would constitute an equivalent. But we are not bothering with
## older MySQLdb versions here, since we are recommending to
## upgrade to more recent versions anyway.
if CFG_MISCUTIL_SQL_USE_SQLALCHEMY:
return connect(
host=dbhost,
port=int(CFG_DATABASE_PORT),
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset="utf8",
)
else:
thread_ident = (os.getpid(), get_ident())
if relogin:
connection = _DB_CONN[dbhost][thread_ident] = connect(
host=dbhost,
port=int(CFG_DATABASE_PORT),
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset="utf8",
)
connection.autocommit(True)
return connection
else:
if _DB_CONN[dbhost].has_key(thread_ident):
return _DB_CONN[dbhost][thread_ident]
else:
connection = _DB_CONN[dbhost][thread_ident] = connect(
host=dbhost,
port=int(CFG_DATABASE_PORT),
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset="utf8",
)
connection.autocommit(True)
return connection
def _db_login(dbhost=CFG_DATABASE_HOST, relogin=0):
"""Login to the database."""
## Note: we are using "use_unicode=False", because we want to
## receive strings from MySQL as Python UTF-8 binary string
## objects, not as Python Unicode string objects, as of yet.
## Note: "charset='utf8'" is needed for recent MySQLdb versions
## (such as 1.2.1_p2 and above). For older MySQLdb versions such
## as 1.2.0, an explicit "init_command='SET NAMES utf8'" parameter
## would constitute an equivalent. But we are not bothering with
## older MySQLdb versions here, since we are recommending to
## upgrade to more recent versions anyway.
dbport = int(CFG_DATABASE_PORT)
if dbhost == CFG_DATABASE_SLAVE and CFG_DATABASE_SLAVE_PORT:
dbport = int(CFG_DATABASE_SLAVE_PORT)
if CFG_MISCUTIL_SQL_USE_SQLALCHEMY:
return connect(host=dbhost,
port=dbport,
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset='utf8')
else:
thread_ident = (os.getpid(), get_ident())
if relogin:
connection = _DB_CONN[dbhost][thread_ident] = connect(
host=dbhost,
port=dbport,
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset='utf8')
connection.autocommit(True)
return connection
else:
if _DB_CONN[dbhost].has_key(thread_ident):
return _DB_CONN[dbhost][thread_ident]
else:
connection = _DB_CONN[dbhost][thread_ident] = connect(
host=dbhost,
port=dbport,
db=CFG_DATABASE_NAME,
user=CFG_DATABASE_USER,
passwd=CFG_DATABASE_PASS,
use_unicode=False,
charset='utf8')
connection.autocommit(True)
return connection
def construct_db_connections():
hostname = socket.gethostname()
hostname_prefix = hostname.split('-')[0]
is_server = hostname_prefix in ('dev00', 'online00', 'online01')
if not is_server:
"""
In local environment, you should put a configuration file named '.db_config.ini' in the current directory.
The configuration file content format must be:
[DEFAULT]
user=<YOUR_DB_USERNAME>
password=<YOUR_PASSWORD>
schema=<YOUR_OWN_SCHEMA>
NOTE: Please do NOT commit the configuration file to svn
"""
pwd = os.getcwd()
db_config_file_path = os.path.join(pwd, '.db_config.ini')
assert os.path.exists(
db_config_file_path
), "db config file %s is not existed" % db_config_file_path
parser = configparser.ConfigParser()
parser.read(db_config_file_path)
user = parser['DEFAULT']['user']
password = parser['DEFAULT']['password']
zj_schema = parser['DEFAULT']['schema']
db_host = 'dev.zhijuninvest.com'
else:
user = '******'
password = '******'
db_host = '172.17.0.1'
zj_schema = 'zj_data'
jy_conn = connect(host=db_host,
port=3306,
user=user,
passwd=password,
db="JYDB",
charset="utf8mb4",
cursorclass=cursors.DictCursor)
zj_conn = connect(host=db_host,
port=3306,
user=user,
passwd=password,
db=zj_schema,
charset="utf8mb4",
cursorclass=cursors.DictCursor)
return jy_conn, zj_conn
def mysql_connect(db):
conn = connect(host = host,
user = user,
passwd = passwd,
db = db)
c = conn.cursor()
return c, conn
def update_diet_cost(res):
try:
print(res)
c=connect("127.0.0.1","root","asd","project1")
d=c.cursor()
print("challlllu")
# x=int(res['messid1'])
# print(x)
# d.execute("""select rollno from student where mess_id={}""".format(x))
# r=d.fetchall()
# print(r)
print(res['cost'])
d.execute("""alter table attendance modify Diet_cost double default {} """.format(res['cost']))
#alter table attendance modify Diet_cost double default {}
print("stop sdsssssss")
print("end")
c.commit()
c.close()
return True
except:
return False
def add_attendance_entries(res):
try:
print(res)
c=connect("127.0.0.1","root","asd","project1")
d=c.cursor()
print("challlllu")
x=int(res['messid1'])
print(x)
d.execute("""select rollno from student where mess_id={}""".format(x))
r=d.fetchall()
print(r)
print("stop sdsssssss")
#d.execute("""select vehicle_id from vehicle where registration_no='{}' """.format(key))
for e in r:
print(e[0])
print(res['date'])
d.execute("""insert into attendance(rollno,Date) values({},'{}')""".format(e[0],res['date']))
print("end")
c.commit()
c.close()
return True
except:
return False
def main():
#cfg = ConfigParser({'host': 'localhost', 'user': '******',
# 'password': '', 'database': 'mcx'})
cfg = ConfigParser({'host': 'mysql.internal', 'user': '******',
'password': '******', 'database': 'mcx'})
cfg.add_section('client')
cfg.read(join(expanduser('~'), '.mcdb', 'mcx.ini'))
db = connect(host = cfg.get('client', 'host'),
user = cfg.get('client', 'user'),
db = cfg.get('client', 'database'),
passwd = cfg.get('client', 'password'))
c = db.cursor()
c.execute('SELECT tracking_number, encrypted_pin, pin, rights_id, expiration_time FROM tracking_number')
for tracking_number, encrypted_pin, pin, rights_id, expiration_time in c.fetchall():
print "Processing row %s" % tracking_number
if rights_id:
pin_value = '';
if(pin):
pin_value = pin;
c.execute("insert into external_share (es_id, es_identity, es_identity_type, es_create_date_time) values (NULL,%s,%s,%s)",
(tracking_number + "/" + pin_value, "PIN", expiration_time))
es_id = db.insert_id();
print "migrated rights_id %s to es_id %s" % (rights_id, es_id)
c.execute("update rights set es_id = %s where rights_id = %s",(es_id,rights_id))
c.execute("update tracking_number set es_id = %s where tracking_number = %s",(es_id, tracking_number))
c.execute('alter table tracking_number drop column rights_id');
db.commit()
def init_white_host_engine():
global white_engine
white_engine = esm.Index()
conn = connect(host="180.96.26.186",
port=33966,
user="******",
passwd="jshb114@nj",
db="adp")
sql = "select a.usertags,a.host_set_object,a.plan_id from adp_group_info as a,adp_plan_info as b where a.plan_id=b.plan_id and a.enable =1 and b.enable=1 and a.mobile=2;"
cursor = conn.cursor()
cursor.execute(sql)
res = cursor.fetchall()
for it in res:
usertags = it[0]
json_host = json.loads(it[1])
host_list = json_host["_include_host"]
for host in host_list:
if len(host) > 4:
if host.startswith("*."):
host = host[1:]
elif host.startswith("*"):
host = host[1:]
if host.endswith("/*"):
host = host[0:-2]
elif host.endswith("*"):
host = host[0:-1]
elif host.endswith("/"):
host = host[0:-1]
white_engine.enter(host)
if len(host.split(".")) > 3:
if host.startswith('.'):
host_pattern.add(host[1:])
white_engine.fix()
conn.close()
def conn():
return connect(user='******',
password='******',
host='192.168.1.53',
port=3306,
db='webdb',
charset='utf8')
def init(cf_plugin,logging):
# Initializes the plugin
import MySQLdb
from MySQLdb import connect
global logger
logger=logging
logger.info('MYSQL - Configuring plugin...')
configure(cf_plugin)
logger.info('MYSQL - Checking connection to database...')
try:
db = connect(user=user,passwd=password, host=host, port=int(port))
except MySQLdb.Error as err:
logger.error('MYSQL - '+str(err[1])+'')
exit(1)
try:
db.select_db(database)
except MySQLdb.Error as err:
if err[0] == 1049: # no database
logger.info("MYSQL - Creating database...")
create_database(db)
else:
logger.error('MYSQL - '+str(err[1])+'')
exit(1)
logger.info('MYSQL - Plugin initialized!')
def check_message_format(rp, msg):
"""检查提交说明格式和JIRA_KEY的有效性
:param rp: 检查提交说明格式的正则表达式
:param msg: 提交说明
"""
m = rp.match(msg)
format_error = False
key_error = False
if m:
task_key = m.groups()[1]
if task_key:
conn = connect(host=jira_db_host, db=jira_db_name, user=jira_db_user, passwd=jira_db_passwd, charset="utf8")
cursor = conn.cursor()
check_sql = (
"select issue.issuenum, issue.SUMMARY from jiraissue issue join project pro where "
"issue.issuenum='%s' and issue.project=pro.id and pro.pkey='%s'"
% (task_key.split("-")[1], task_key.split("-")[0])
)
cursor.execute(check_sql)
row = cursor.fetchone()
if not row or not row[0]:
key_error = True
cursor.close()
conn.close()
else:
format_error = True
return format_error, key_error
def cargar_conexion_sql_local():
"""
Creates a pyobdc connection object pointing local Sql DB
:return: pyodbc connection object
"""
from pyodbc import connect
return connect('DRIVER={SQL Server};SERVER=LENOVO-PC\SQLEXPRESS;DATABASE=InfoAdex;Trusted_Connection=yes')
def cargar_conexion_as400_infoadex():
"""
Creates a pyobdc connection object pointing InfoAdex AS400 DB
:return: pyodbc connection object
"""
from pyodbc import connect
return connect('DSN=AS400;SYSTEM=192.168.1.101;UID=INFOADEX;PWD=INFOADEX')
def data_connect():
# establish connection to database
HOST = 'publicdb.cs.princeton.edu'
PORT = 3306
DATABASE = 'tzha'
USER = '******'
PASSWORD = '******'
host = HOST
if 'DB_SERVER_HOST' in environ:
host = environ['DB_SERVER_HOST']
connection = connect(host = host, port = PORT, user = USER, passwd = PASSWORD,
db = DATABASE)
global cursor
cursor = connection.cursor(cursors.DictCursor)
# find tower_count
cursor.execute('select max(celltower_oid) as celltower_oid from cellspan')
row = cursor.fetchone()
global tower_count
tower_count = row['celltower_oid']
# find person_count
cursor.execute('select max(person_oid) as person_oid from cellspan')
row = cursor.fetchone()
global person_count
person_count = row['person_oid']
def directFromMysql():
if db.packagePair.count() > 0:
print "packagePair exists"
return
mysqldb = connect(host="westlake.isr.cs.cmu.edu", port = 3306, user = "******", passwd = "luansong", db = "appanalysisraw")
cur1 = mysqldb.cursor()
cur2 = mysqldb.cursor()
cur1.execute("select * from (select packagename, permission, 3rd_party_package, is_external from test_permissionlist group by packagename, permission, 3rd_party_package, is_external) as Z;")
appDict = {}
for i in range(cur1.rowcount):
row = cur1.fetchone()
packagename, permission, third_party_package, is_external = row
print row
if is_external == 0:
purpose = "INTERNAL"
elif third_party_package != "NA":
cur2.execute("select apitype from labeled3rdparty where externalpack=%s", third_party_package)
purpose = cur2.fetchone()[0]
else:
continue
appEntry = appDict.get(packagename, {})
appEntry.update({permission: appEntry.get(permission, set()) | set([purpose])})
appDict[row[0]] = appEntry
cur1.close()
cur2.close()
for key in appDict:
db.packagePair.insert({"packagename": key, "pairs": appDict[key]})
def get_db():
"""Opens a new database connection if there is none yet for the
current application context.
"""
if not hasattr(g, 'db'):
g.db = connect(host='sql.mit.edu', db='lucid+sofar', user='******', passwd='KO01BAHjiKGkZ')
return g.db
def __init__(self, err, out):
self.err = err
self.out = out
# We need entirely different imports if this storage is used.
from django.db import connections
from MySQLdb import connect
self.connection = connections[DB_ALIAS]
db = settings.DATABASES[DB_ALIAS]
connection = connect(
db=db["NAME"], host=db["HOST"], user=db["USER"], passwd=db["PASSWORD"], charset="utf8", use_unicode=True
)
self.mysql_escape_string = connection.escape_string
self.out(
"""
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
"""
)
def do_sql(query, variables=None, db='nova'):
'''Returns a resultDict for a given sql query.'''
# Must be called and connected every query time, otherwise the MySQL
# server will have 'gone away' from stale connections:
db_mappings = {
'nova': {
'server': '<NOVA MYSQL SERVER HERE>',
'password': '******'
},
'keystone': {
'server': '<KEYSTONE MYSQL SERVER HERE>',
'password': '******'
}
}
conn = connect(host=db_mappings[db]['server'],
user=db,
db=db,
passwd=db_mappings[db]['password']
)
cursor = conn.cursor(cursors.DictCursor)
if variables:
cursor.execute(query, variables)
else:
cursor.execute(query)
result_dict = cursor.fetchall()
conn.close()
return result_dict
def get_trading_days(start, end):
conn = connect(host="dev.zhijuninvest.com",
user="******",
port=3306,
passwd="6GEL1YDeoPqPV4Fo",
db="JYDB",
charset="utf8mb4",
cursorclass=cursors.DictCursor,
autocommit=True)
cursor = conn.cursor()
try:
cursor.execute("""
SELECT TradingDate TradingDay
FROM QT_TradingDayNew
WHERE TradingDate >= '%s'
and TradingDate <= '%s'
AND IfTradingDay = 1
AND SecuMarket = 83
""" % (start, end))
tradingday = cursor.fetchall()
if not tradingday:
return None
tradingday = pd.DataFrame(list(tradingday), columns=['TradingDay'])
tradingday = tradingday.sort_values(by='TradingDay')
return tradingday
finally:
cursor.close()
def getconnection():
return connect(user='******',
password='******',
host='localhost',
port=3306,
db='mysite',
charset='utf8')
def connect_database(config: dict, local_infile=False) -> Connection:
"""Connect database."""
return connect(host=config['host'],
port=config['port'],
user=config['user'],
passwd=config['password'],
local_infile=local_infile)
def _db_connect(logger, config, name):
conn = connect(host=config['host'],
user=config['user'],
passwd=config['pass'],
db=name)
cursor = conn.cursor()
return (conn, cursor)
def main():
import logging
import yaml
from subspace.billing.server.biller import Biller
from subspace.billing.server.zone import Zone
from MySQLdb import connect, Error
logging.basicConfig(level=logging.DEBUG,
format="<%(threadName)25.25s > %(message)s")
zone_score_id = 1
zone_password = "******"
zone = Zone(('zone.aswz.org', 5000), zone_score_id, zone_password)
conf = yaml.load(open('/etc/chasm/biller.conf'))
db_conn = connect(**conf["db"])
biller = Biller(("0.0.0.0", conf["port"]), conf["name"], db_conn)
player = Player(biller, zone, 0, 'test_user','test_password')
print("Logging in with (usr=%s,pwd=%s)" % (player.name, player.password))
if player.login():
print("success (squad=%s)" % player.squad)
player.logout()
else:
print("failure")
player.password = "******"
print("Logging in with (usr=%s,pwd=%s)" % (player.name, player.password))
if player.login():
print("success (squad=%s)" % player.squad)
player.logout()
else:
print("failure")
db_conn.close()
def __init__(self):
self.__db__ = connect(host=Globle_Db.host,
user=Globle_Db.user,
passwd=Globle_Db.passwd,
db=Globle_Db.db,
charset='utf8')
self.__cursor__ = self.__db__.cursor()
def connect(user, passwd, host, port):
"""connect is a convenient shortcut to mysql.connector.connect. Also,
it makes it reaonable to import mysql.connector in this file, so
to make it self-complete as a template.
"""
return connect(user=user, passwd=passwd, host=host, port=port)
def register(args, conf):
# Check that sufficient arguments are available
if "user" not in args or "pass" not in args:
return ServiceResult(
"application/json", jsonsaves({"state": "failure", "message": "Need a username and password"})
)
db = connect(
host=conf["TRAIN_DB_HOST"],
user=conf["GATE_KEEPER_USERNAME"],
passwd=conf["GATE_KEEPER_PASSWD"],
db=conf["USER_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
username = args.getfirst("user")
passwd = args.getfirst("pass")
cur = db.cursor()
# Check that the user doesn't already exist
cur.execute("SELECT DISTINCT user FROM mysql.user WHERE user=%s", username)
if cur.fetchone() is not None:
cur.close()
db.close()
return ServiceResult("application/json", jsonsaves({"success": False, "message": "User already exists"}))
cur.execute("CREATE USER %s@%s IDENTIFIED BY %s", (username, conf["TRAIN_DB_WEB_HOST"], passwd))
# duplicate backticks for the table name
table_name = username.replace("`", "``") + "_data"
cur.execute("CREATE TABLE `" + table_name + "` (Latitude DOUBLE, Longitude DOUBLE, Time DATETIME);")
cur.execute("GRANT INSERT, DROP ON `" + table_name + "` TO %s@%s", (username, conf["TRAIN_DB_WEB_HOST"]))
cur.execute("GRANT SELECT ON `" + conf["TRAIN_DATABASE"] + "`.* TO %s@%s", (username, conf["TRAIN_DB_WEB_HOST"]))
cur.close()
db.close()
return ServiceResult("application/json", jsonsaves({"success": True, "message": "Created new user"}))
def add_config(project_id, config_path):
"""解析ApkVersion将其加入数据库中"""
cp = ConfigParser()
cp.read(config_path)
app_config = []
for app_name in cp.sections():
# print app_name
if app_name == 'ProjectInfo':
continue
if cp.get(app_name, 'support') == 'yes':
support = 'yes'
else:
support = 'no'
if cp.has_option(app_name, 'overrides') and cp.get(app_name, 'overrides') and cp.get(app_name, 'overrides') != 'null':
overrides = cp.get(app_name, 'overrides')
else:
overrides = ''
app_config.append((app_name, support, cp.get(app_name, 'version').strip(), overrides, project_id))
insert_myos_config = "INSERT INTO project_myos_app_config(app_name, support, app_version, overrides, project_id)" \
"VALUES (%s, %s, %s, %s, %s)"
con = connect(host=db_host, db=db_name, user=db_user, passwd=db_passwd, charset='utf8')
cursor = con.cursor()
cursor.executemany(insert_myos_config, app_config)
con.commit()
con.close()
def main():
confPath = path.abspath(argv[1])
confDir = path.dirname(confPath)
confName = path.splitext(path.basename(confPath))[0]
output = '%s/%s' % (confDir, confName)
tarPath = path.abspath(argv[2])
tarDir = path.dirname(tarPath)
tarName = path.splitext(path.basename(tarPath))[0]
source = '%s/%s' % (tarDir, tarName)
f = open(confPath, 'r')
conf = load(f)
f.close()
tar = tarfile.open(tarPath, 'r')
tar.extractall(path=tarDir)
tar.close()
db = connect(database['host'], database['user'], database['password'], database['database'])
fin = open('%s/%s.csv' % (source, tarName), 'r')
fin.readline()
try:
mkdir(output)
except WindowsError, err:
print err
def main(repo_dir_path):
"""将指定目录下的repo仓库路径，添加到数据库中"""
repos = []
for dir_name in os.listdir(repo_dir_path):
if dir_name.endswith('.git') and os.path.isdir(os.path.join(repo_dir_path, dir_name)):
repos.append(os.path.join(repo_dir_path, dir_name))
print repos
con = connect(host='192.168.33.7', db='tms', user='******', passwd='gms', charset='utf8')
cursor = con.cursor()
exits_sql = "SELECT path FROM repo_repo WHERE platform_id=89"
cursor.execute(exits_sql)
exists_repo = [tmp[0] for tmp in cursor.fetchall()]
print exists_repo
new_repo = []
for repo_path in repos:
if repo_path not in exists_repo:
new_repo.append((repo_path.split('/home/git/repositories/tinno/release/')[1].split('.git')[0].strip(),
repo_path, 89, 3))
print new_repo
insert_repo = "INSERT into repo_repo(name, path, platform_id, category) VALUES (%s, %s, %s, %s)"
cursor.executemany(insert_repo, new_repo)
con.commit()
con.close()
def main ():
# Assume a MySQL fresh install.
user = root
try:
db_conn = connect(user=user, passwd="", db="ichoppedthatvideo")
except:
print "** FATAL ERROR ** Could not connect to database. Aborting..."
raise
cursor = db_conn.cursor()
cursor.execute("SET PASSWORD FOR 'root'@'localhost' = PASSWORD('bleh')")
cursor.execute("SET PASSWORD FOR 'root'@'localhost.localdomain' = PASSWORD('bleh')")
cursor.execute("SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('bleh')")
cursor.execute("CREATE DATABASE ichoppedthatvideo")
cursor.execute("USE ichoppedthatvideo")
cursor.execute("create user 'ichoppedthatvideo_server'@'localhost' identified by 'bleh'")
cursor.execute("grant all privileges on ichoppedthatvideo.* to 'ichoppedthatvideo_server'@'localhost' with grant option")
cursor.execute("""
create table requests (id bigint unsigned not null auto_increment, client_id char(40) not null, server_id varchar(30) not null, child_id smallint unsigned not null, type varchar(20), browser varchar(40), opsys varchar(40), city varchar(127), country varchar(127), cur_timestamp timestamp(8), primary key (id), unique idx_request (client_id, server_id, child_id, cur_timestamp))
""")
cursor.execute("""
create table streams (id bigint(20) unsigned not null auto_increment, request_id bigint(20) unsigned not null, video_id int(10) unsigned not null, seconds int(10) unsigned not null, primary key (id));
""")
def UpdateTOIL(today, public_holiday, id):
oncalldb = connect(
host=os.environ['DB_HOSTNAME'], # your host, usually localhost
user=os.environ['DB_USERNAME'], # your username
passwd=os.environ['DB_PASSWORD'], # your password
db=os.environ['DB_DATABASE'],
)
# check if entry already exists
comment = f"ENTRY AUTO-UPDATED - TOIL earned for being oncall during {public_holiday} Public Holiday"
# convert datetime object "today" into a string
todaystr = datetime.strftime(today, '%Y-%m-%d')
# convert id to a string
id = str(id)
try:
with oncalldb.cursor() as cursor:
# insert the toil day for each user
cursor.execute(
"INSERT INTO toil (`toil_date`, `toil_earned_1x`, \
`toil_earned_1_5x`, `toil_earned_2x`, \
`toil_earned_2_5x`, `toil_taken`, `toil_total`, \
`toil_notes`, `person_name_id`) \
VALUES (%s,7.35,0,0,0,0,7.35,%s,%s)",
[todaystr, comment, id])
# commit the changes
oncalldb.commit()
return True
finally:
cursor.close()
return True
def query_cellspan():
# establish connection to database
HOST = 'publicdb.cs.princeton.edu'
PORT = 3306
DATABASE = 'tzha'
USER = '******'
PASSWORD = '******'
host = HOST
if 'DB_SERVER_HOST' in environ:
host = environ['DB_SERVER_HOST']
connection = connect(host = host, port = PORT, user = USER, passwd = PASSWORD,
db = DATABASE)
global cursor
cursor = connection.cursor(cursors.DictCursor)
for i in range(1, person_count + 1):
# open file
filename = "cellspan{0}-1".format(i)
file = open(filename, 'w+')
# make query
query = ('select * from cellspan where person_oid = ' + str(i)
+ ' and starttime > "' + str(sem_start1)
+ '" and starttime < "' + str(sem_end1) + '"')
cursor.execute(query)
# write to file
line = cursor.fetchone()
while (line):
pickle.dump(line, file)
line = cursor.fetchone()
def getconnection():
return connect(user='******',
password='******',
host='192.168.1.112',
port=3307,
db='mysite',
charset='utf8')
def connect(self):
db = connect(passwd=config("DB_PASSWORD"),
db=config("DB_NAME"),
user=config("DB_USER"),
host=config("DB_HOST"))
self.cursor = db.cursor()
self.db = db
def conn():
return connect(user='******',
password='******',
host='localhost',
port=3306,
db='webdb',
charset='utf8')
def getconnection():
return connect(user='******',
password='******',
host='192.168.1.138',
port=3306,
db='webdb',
charset='utf8')
def __init__():
global __cursor__, commit, rollback
connection = connect(host=DATABASE_HOST, user=DATABASE_USER, passwd=DATABASE_PASSWORD,
db=DATABASE_NAME, charset='utf8')
__cursor__ = connection.cursor()
commit = connection.commit
rollback = connection.rollback
def GetOncallMondayPH(today, yesterday):
oncall_uids = []
oncalldb = connect(
host=os.environ['DB_HOSTNAME'], # your host, usually localhost
user=os.environ['DB_USERNAME'], # your username
passwd=os.environ['DB_PASSWORD'], # your password
db=os.environ['DB_DATABASE'],
)
try:
with oncalldb.cursor() as cursor:
cursor.execute(
"SELECT person.id FROM person INNER JOIN roster \
ON (roster.oss_person_id=person.id \
or roster.nw_person_id=person.id \
or roster.es_person_id = person.id) \
WHERE roster_date=%s or roster_date=%s",
[today, yesterday])
results = cursor.fetchall()
for row in results:
oncall_people = row[0]
# 1 means no one has been assigned to the roster
if (not oncall_people == 1):
oncall_uids.append(oncall_people)
return oncall_uids
finally:
cursor.close()
return True
def load_rule():
conn = connect(host="180.96.26.186",
port=33966,
user="******",
passwd="jshb114@nj",
db="adp")
#sql = "select usertags,host_set_object from adp_group_info where enable = 1"
sql = "select a.usertags,a.host_set_object,a.plan_id from adp_group_info as a,adp_plan_info as b where a.plan_id=b.plan_id and a.enable =1 and b.enable=1 and a.group_id != 211 and a.group_id != 210 and a.mobile=2;"
cursor = conn.cursor()
cursor.execute(sql)
res = cursor.fetchall()
for it in res:
usertags = it[0]
json_host = json.loads(it[1])
host_list = json_host["_include_host"]
for host in host_list:
if len(host) > 4:
if host.startswith("*."):
host = host[2:]
host = ".*\." + host
elif host.startswith("*"):
host = host[1:]
host = ".*\." + host
if host.endswith("/*"):
host = host[0:-2]
host = host + '$'
elif host.endswith("*"):
host = host[0:-1]
host = host + '$'
elif host.endswith("/"):
host = host[0:-1]
host = host + '$'
pattern_init(host)
conn.close()
def __init__(self, hostname='localhost', user='******', password='******',
db='biom', table_basename='tmp_biomtable'):
self.con = connect(hostname, user, password, db)
self.db = db
self._tmp_tables = []
self._table_basename = table_basename
self._num_tmp_tables = self._tmp_table_count()
def connect_db(self):
self.db = connect(host=self.host,
port=self.port,
user=self.username,
passwd=self.password,
db=self.database,
local_infile=1)
def __create_connection(self):
return connect(
self.__host,
self.__username,
self.__password,
self.__database,
)
def file_to_table(self,file_name,table_name,RegEx):
self.db = connect(self.hostname,self.username,self.password,self.database )
self.cursor = self.db.cursor()
self.cursor.execute("USE %s" %(self.database))
self.cursor.execute("DROP TABLE IF EXISTS %s" %(table_name))
# Create table as per requirement
sql = """CREATE TABLE """+table_name+""" (File_Lines VARCHAR(2000) NOT NULL)"""
self.cursor.execute(sql)
f=open(file_name,"r")
g=f.readlines()
for i in g:
if search(RegEx,i)>0:
sql1 = """INSERT INTO %s VALUES ('%s')""" %(table_name,i)
try:
# Execute the SQL command
self.cursor.execute(sql1)
# Commit your changes in the database
self.db.commit()
except:
# Rollback in case there is any error
print "Error !"
self.db.rollback()
def conne():
return connect(user='******',
password='******',
host='192.168.1.111',
db='mysite',
port=3307,
charset='utf8')
def main():
confPath = path.abspath(argv[1])
confDir = path.dirname(confPath)
confName = path.splitext(path.basename(confPath))[0]
output = '%s/%s' % (confDir, confName)
tarPath = path.abspath(argv[2])
tarDir = path.dirname(tarPath)
tarName = path.splitext(path.basename(tarPath))[0]
source = '%s/%s' % (tarDir, tarName)
f = open(confPath, 'r')
conf = load(f)
f.close()
tar = tarfile.open(tarPath, 'r')
tar.extractall(path=tarDir)
tar.close()
db = connect(database['host'], database['user'], database['password'], database['database'])
fin = open('%s/%s.csv' % (source, tarName), 'r')
fin.readline()
try:
mkdir(output)
except WindowsError, err:
print err
def calfunction(res):
try:
print(res)
print("ererere")
c = connect("dg97.mysql.pythonanywhere-services.com", "dg97",
"github!1!1", "dg97$project")
d = c.cursor()
print("345345")
i = random.randint(1, 100000)
for key in res:
print(key, " -> ", res[key])
s = create_signature(res['password'])
print s, "hashed password"
d.execute(
"""insert into customer(customer_id,name,email,password,contact_no,occupation,address) values({},'{}','{}','{}','{}','{}','{}')"""
.format(i, res['name'], res['email'], s, res['contact'],
res['occ'], res['addr']))
print("end")
c.commit()
c.close()
return True
except:
return False
def connect(self, host=None, port=3306, sid='', user='', passw='', timeout=10):
"""Method connects to database
Args:
host (str): hostname
port (int): port
sid (str): db instance
user (str): username
passw (str): password
timeout (int): timeout
Returns:
bool: result
Raises:
event: dbi_before_connect
event: dbi_after_connect
"""
try:
message = '{0}/{1}@{2}:{3}/{4} timeout:{5}'.format(
user, passw, host, port, sid, timeout)
self._mh.demsg('htk_on_debug_info', self._mh._trn.msg(
'htk_dbi_connecting', message), self._mh.fromhere())
ev = event.Event(
'dbi_before_connect', host, port, sid, user, passw, timeout)
if (self._mh.fire_event(ev) > 0):
host = ev.argv(0)
port = ev.argv(1)
sid = ev.argv(2)
user = ev.argv(3)
passw = ev.argv(4)
timeout = ev.argv(5)
if (ev.will_run_default()):
self._host = host
self._port = port
self._sid = sid
self._user = user
self._passw = passw
self._client = connect(host=self._host, port=self._port, db=self._sid, user=self._user,
passwd=self._passw, connect_timeout=timeout)
self._is_connected = True
self._mh.demsg('htk_on_debug_info', self._mh._trn.msg(
'htk_dbi_connected'), self._mh.fromhere())
ev = event.Event('dbi_after_connect')
self._mh.fire_event(ev)
return True
except Error as ex:
self._mh.demsg(
'htk_on_error', 'database error: {0}'.format(ex), self._mh.fromhere())
return False
def operation_scan(config, _):
seg_repository = make_seg_repository(config)
repository_con = connect(db=config['database'], charset='utf8')
for domain, directory in config['directories'].iteritems():
repository = Repository(repository_con, domain)
scan_directory(repository, seg_repository, directory)
repository_con.commit()
def setup_mysql():
# set up db connection
from MySQLdb import connect
mysql_pwd = os.environ['MYSQL_PWD']
conn = connect(host="localhost",user="******",passwd=mysql_pwd,db="sentience")
cursor = conn.cursor()
conn.autocommit(True)
return cursor
def getDBConnection(self, dbdict):
"""Get the connection to Buffer database"""
try:
conn = connect(host=dbdict['host'], user=dbdict['user'],
passwd=dbdict['password'], db=dbdict['db'])
except OperationalError, (msg_id, msg):
logger.error('%d: %s' % (msg_id, msg))
conn = None
def start(self):
self.conn = connect(host=self.host, port=self.port,
user=self.user, passwd=self.password, db=self.db)
# order is important here
self.migrate_boards()
self.migrate_users()
self.migrate_topics()
self.migrate_posts()
def _mysql_ac(self):
root = connect('localhost', 'root', ROOT_SIFRE)
imlec = root.cursor()
imlec.execute('CREATE DATABASE IF NOT EXISTS phpmyadmin_db CHARACTER SET utf8 COLLATE utf8_general_ci')
imlec.execute('GRANT ALL ON phpmyadmin_db.* TO phpmyadmin@localhost IDENTIFIED BY \'' + ROOT_SIFRE + '\'')
imlec.execute('FLUSH PRIVILEGES')
root.commit()
root.close()
