def __init__(self, config):
"""NetServer initialisation method.
Args:
config (Configuration): The parsed configuration object
"""
log.info("Initialising the server")
self.config = config
self.protocol = {'lora': LoraInterface(self)}
self.message_cache = []
self.otarange = set(
xrange(self.config.otaastart, self.config.otaaend + 1))
self.task = {}
self.commands = []
self.adrprocessing = False
self.band = eval(self.config.freqband)()
# Establish database connection
(driver, host, user, password, database) = self.config.database
Registry.DBPOOL = adbapi.ConnectionPool(driver,
host=host,
user=user,
password=password,
database=database)
def connectionForURI(uri):
"""Return a Twisted adbapi connection pool for a given database URI."""
schema, user, password, host, port, db = parseURI(uri)
try:
module = db_modules[schema]
except KeyError:
raise ValueError("Database scheme '%s' is not supported." % schema)
# Reconnecting is safe since we don't use transactions.
# the following code prefers MySQLdb native reconnect if it's available,
# falling back to twisted's cp_reconnect.
# mysql's reconnect is preferred because it's better tested than twisted's
# MySQLdb reconnect just works with version 1.2.2 it has been removed after
args = ()
kwargs = {}
if module == 'MySQLdb':
MySQLdb = reflect.namedModule(module)
if MySQLdb.version_info[:3] == (1, 2, 2):
kwargs.setdefault('reconnect', 1)
kwargs.setdefault('host', host or 'localhost')
kwargs.setdefault('port', int(port or '3306'))
kwargs.setdefault('user', user or '')
kwargs.setdefault('passwd', password or '')
kwargs.setdefault('db', db)
if 'reconnect' not in kwargs:
# note that versions other than 1.2.2 of MySQLdb don't provide reconnect parameter.
# hopefully, if underlying reconnect was enabled, twisted will never see
# a disconnect and its reconnection code won't interfere.
kwargs.setdefault('cp_reconnect', 1)
kwargs.setdefault('cp_noisy', False)
pool = adbapi.ConnectionPool(module, *args, **kwargs)
pool.schema = schema
return pool
def _open_db(self):
log.info("open db")
self.db = adbapi.ConnectionPool('sqlite3',
self.cache_file,
check_same_thread=False)
def create_tables(transaction):
transaction.execute("create table if not exists blocks (" +
" block_height integer, " +
" block_hash text, " +
" claim_txid text)")
transaction.execute("create table if not exists claims (" +
" claimid text, " + " name text, " +
" txid text," + " n integer)")
transaction.execute("create table if not exists metadata (" +
" claimid text, " + " txid text, " +
" value text)")
transaction.execute("create table if not exists stream_size (" +
" sd_hash text, " +
" total_bytes integer)")
transaction.execute("create table if not exists blob_attempts (" +
" hash text, " + " num_tries integer)")
return self.db.runInteraction(create_tables)
def __init__(self):
try:
conn = MySQLdb.connect(host='localhost',
user='******',
passwd='',
db='wd_cloth',
port=3306)
cur = conn.cursor()
cur.execute('select count(*) from s_shopinfo')
cur.close()
conn.close()
log.startLogging(sys.stdout)
self.dbpool = adbapi.ConnectionPool(
'MySQLdb',
host='127.0.0.1',
db='wd_cloth',
user='******',
passwd='',
cursorclass=MySQLdb.cursors.DictCursor,
charset='utf8',
use_unicode=True)
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def __init__(self, service, plugins):
self.nb_local_avatars = 6
dirname = os.path.join(service.settings['plugins-libdir'], self.name())
self.database = os.path.join(dirname, 'auth.sqlite')
exists = os.path.exists(self.database)
if not exists:
if not os.path.exists(dirname):
os.mkdir(dirname)
import sqlite3
db = sqlite3.connect(self.database)
c = db.cursor()
c.execute("CREATE TABLE players ( "
" id INTEGER PRIMARY KEY, "
" name VARCHAR(255) "
"); ")
c.execute("CREATE INDEX players_idx ON players (name); ")
db.commit()
db.close()
self.db = adbapi.ConnectionPool("sqlite3",
database=self.database,
cp_noisy=True,
check_same_thread=False)
log.msg('plugin auth initialized with ' + self.database)
def __init__(self, domain, db, user, passwd, host, port):
self.domain = domain.decode('gbk')
self.db = db.decode('gbk')
self.user = user.decode('gbk')
self.passwd = passwd.decode('gbk')
self.host = host.decode('gbk')
self.port = int(port)
self.dbpool = adbapi.ConnectionPool(
'MySQLdb',
db=self.db,
user=self.user,
passwd=self.passwd,
cursorclass=MySQLdb.cursors.DictCursor,
charset='utf8',
use_unicode=True,
host=self.host,
port=self.port,
)
self.docs = []
self.index2term_dict = {}
self.term2index_dict = {}
self.termId2termName = {}
self.cutted_documents = []
def __init__(self, hash_announcer, blob_dir, db_dir):
"""
This class stores blobs on the hard disk,
blob_dir - directory where blobs are stored
db_dir - directory where sqlite database of blob information is stored
"""
DHTHashSupplier.__init__(self, hash_announcer)
self.announce_head_blobs_only = conf.settings[
'announce_head_blobs_only']
self.blob_dir = blob_dir
self.db_file = os.path.join(db_dir, "blobs.db")
self.db_conn = adbapi.ConnectionPool('sqlite3',
self.db_file,
check_same_thread=False)
self.blob_type = BlobFile
self.blob_creator_type = BlobFileCreator
# TODO: consider using an LRU for blobs as there could potentially
# be thousands of blobs loaded up, many stale
self.blobs = {}
self.blob_hashes_to_delete = {
} # {blob_hash: being_deleted (True/False)}
def process_item(self, item, spider):
mysql_config = item.pop('__mysql__', None) # 存储时自动删除配置
if mysql_config and item:
if type(mysql_config) is dict:
table = mysql_config.pop('table', None)
db = mysql_config.get('db', None) or 'vrequest'
mysql_config.setdefault('charset', 'utf8mb4')
mysql_config.setdefault('db', db)
dbk = hmac.new(
b'',
json.dumps(mysql_config, sort_keys=True).encode(),
'md5').hexdigest()
if dbk not in self.dbn:
self.dbn[dbk] = adbapi.ConnectionPool(
'pymysql', **mysql_config)
self.init_database(self.dbn[dbk], mysql_config, db, table,
item)
self.dbn[dbk].runInteraction(self.insert_item, db, table, item)
return item
else:
raise TypeError('Unable Parse mysql_config type:{}'.format(
type(mysql_config)))
else:
return item
def main():
"""
Run a simple echo pb server to test the checker. It defines a custom query
for dealing with sqlite special quoting, but otherwise it's a
straightforward use of the object.
You can test it running C{pbechoclient.py}.
"""
import sys
from twisted.python import log
log.startLogging(sys.stdout)
import os
if os.path.isfile('testcred'):
os.remove('testcred')
from twisted.enterprise import adbapi
pool = adbapi.ConnectionPool('pysqlite3.dbapi2', 'testcred')
# Create the table that will be used
query1 = """CREATE TABLE user (
username string,
password string
)"""
# Insert a test user
query2 = """INSERT INTO user VALUES ('guest', 'guest')"""
def cb(res):
pool.runQuery(query2)
pool.runQuery(query1).addCallback(cb)
checker = DBCredentialsChecker(pool.runQuery,
query="SELECT username, password FROM user WHERE username = ?")
from twisted.cred.portal import Portal
import pbecho
from twisted.spread import pb
portal = Portal(pbecho.SimpleRealm())
portal.registerChecker(checker)
reactor.listenTCP(pb.portno, pb.PBServerFactory(portal))
def insert_db(self, tx, item):
values = (
item['url'],
item['city'],
item['skill'],
item['welfare'],
item['salary'],
item['education'],
item['search_word'],
item['sub_job_type'],
item['job_type'],
time.strftime("%Y/%m/%d %H:%M:%S"),
time.strftime("%Y/%m/%d %H:%M:%S"),
)
sql = 'INSERT INTO job (`url`,`city`,`skill`,`welfare`,`salary`,`education`,`search_word`,`sub_job_type`,`job_type`,`create_time`,`update_time`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
tx.execute(sql, values)
except:
# todo:数据库断开连接
logging.error('error for mysql %s' % item['url'])
traceback.print_exc()
self.dbpool = adbapi.ConnectionPool('pymysql', host=self.HOST, port=self.PORT, user=self.USER,
passwd=self.PASSWD, db=self.MYSQL_DB_NAME, charset='utf8mb4')
pass
def __init__(self, query):
settings = get_project_settings() #获取settings配置，设置需要的信息
dbparams = dict(
host=settings['MYSQL_HOST'], #读取settings中的配置
db=settings['MYSQL_DBNAME'],
user=settings['MYSQL_USER'],
passwd=settings['MYSQL_PASSWD'],
charset='utf8', #编码要加上，否则可能出现中文乱码问题
cursorclass=pymysql.cursors.DictCursor,
use_unicode=True,
)
#**表示将字典扩展为关键字参数,相当于host=xxx,db=yyy....
if (query == False):
dbpool = adbapi.ConnectionPool('pymysql', **dbparams)
self.dbpool = dbpool
else:
connect = pymysql.connect(host='127.0.0.1',
user='******',
passwd='19900317',
port=3306,
db='pw',
charset='utf8')
self.cur = connect.cursor()
def config(self):
if not get_config().getboolean('stats', 'enabled', True):
return defer.succeed(None)
self.db_backend = get_config().getstring('stats', 'db_backend',
'sqlite3')
self.db_conn_param = get_config().getstring('stats', 'db_conn_param',
':memory:').split(';')
self.db_conn_kw = eval(get_config().getstring('stats', 'db_conn_kw',
'{}'))
self.db_operation = get_config().getstring(
'stats', 'db_operation',
'INSERT INTO CONF_CHANGES (username, timestamp, cores,'
'disk, memory, number_of_vms, last_known_credit) '
'VALUES (%s, %s, %s, %s, %s, %s, %s)')
self._db = adbapi.ConnectionPool(self.db_backend, *self.db_conn_param,
**self.db_conn_kw)
self._db.noisy = get_config().getboolean('debug', 'stats_debug', False)
if get_config().getstring('stats', 'db_init', None):
return self.initdb()
return defer.succeed(None)
def __setstate__(self, state):
self.__dict__ = state
self.info['password'] = getpass.getpass('Database password for %s: ' %
(self.info['user'], ))
self.dbpool = adbapi.ConnectionPool(**self.info)
del self.info['password']
def __init__(self, info):
self.info = info
self.dbpool = adbapi.ConnectionPool(**self.info)
def __init__(self):
dbargs = settings.get('DB_CONNECT')
db_server = settings.get('DB_SERVER')
dbpool = adbapi.ConnectionPool(db_server, **dbargs)
self.dbpool = dbpool
from __future__ import absolute_import
from twisted.enterprise import adbapi
from twistar.registry import Registry
CONNECTION = Registry.DBPOOL = adbapi.ConnectionPool('MySQLdb',
user="******",
passwd="",
host="localhost",
db="twistar")
def initDB(testKlass):
def runInitTxn(txn):
txn.execute("""CREATE TABLE users (id INT AUTO_INCREMENT,
first_name VARCHAR(255), last_name VARCHAR(255), age INT, dob DATE, PRIMARY KEY (id))"""
)
txn.execute(
"""CREATE TABLE avatars (id INT AUTO_INCREMENT, name VARCHAR(255),
color VARCHAR(255), user_id INT, PRIMARY KEY (id))""")
txn.execute(
"""CREATE TABLE pictures (id INT AUTO_INCREMENT, name VARCHAR(255),
size INT, user_id INT, PRIMARY KEY (id))""")
txn.execute(
"""CREATE TABLE comments (id INT AUTO_INCREMENT, subject VARCHAR(255),
body TEXT, user_id INT, PRIMARY KEY (id))""")
txn.execute(
"""CREATE TABLE favorite_colors (id INT AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY (id))"""
)
txn.execute(
"""CREATE TABLE favorite_colors_users (favorite_color_id INT, user_id INT, palette_id INT)"""
# 异步测试
from twisted.internet import reactor, defer
from twisted.enterprise import adbapi
import threading
# 建立数据库连接
# adbapi.ConnectionPool 方法可以创建一个数据库连接池对象，其中包含多个连接对象，每个连接对象在独立的线程工作。
# adbapi 只是提供了异步访问数据库的编程框架，在其内部依然使用MySQLdb，sqlite3 这样的库访问。
# ConnectionPool 方法第1个参数就是用来指定使用哪个库访问数据。其他参数在创建连接对象时使用。
dbpool = adbapi.ConnectionPool('MySQLdb',
host='localhost',
database='scrapy_db',
user='******',
password='******')
def insert_db(tx, item):
print('In Thread:', threading.get_ident())
sql = 'INSERT INTO person VALUES (%s,%s,%s)'
tx.execute(sql, item)
for i in range(1000):
item = ('person%s' % i, 25, 'M')
# dbpool.runInteraction(insert_db,item) 以异步方式调用 insert_db 函数，dbpool 会选择连接池中的一个连接对象在独立线程中
# 调用 insert_db，其中 参数 item 会被传给 insert_db 的第二个参数，传给 insert_db 的第一个参数是一个 Transaction 对象，
# 其接口 与 Cursor 对象类似，可以调用 execute 方法 执行 SQL 语句，insert_db 执行完后，连接对象会自动调用 commit 方法
dbpool.runInteraction(insert_db, item)
reactor.run()
def get(self):
try:
p = adbapi.ConnectionPool('psycopg2',
database='postgres',
host=self.host,
port=self.port,
user=self.user,
password=self.password)
cols = (
('xact_commit', 'commits'),
('xact_rollback', 'rollbacks'),
('blks_read', 'disk.read'),
('blks_hit', 'disk.cache'),
('tup_returned', 'returned'),
('tup_fetched', 'selects'),
('tup_inserted', 'inserts'),
('tup_updated', 'updates'),
('tup_deleted', 'deletes'),
('deadlocks', 'deadlocks')
)
keys, names = zip(*cols)
q = yield p.runQuery(
'SELECT datname,numbackends,%s FROM pg_stat_database' % (
','.join(keys))
)
for row in q:
db = row[0]
threads = row[1]
if db not in ('template0', 'template1'):
self.queueBack(self.createEvent('ok',
'threads: %s' % threads,
threads,
prefix='%s.threads' % db)
)
for i, col in enumerate(row[2:]):
self.queueBack(self.createEvent('ok',
'%s: %s' % (names[i], col),
col,
prefix='%s.%s' % (db, names[i]),
aggregation=Counter64)
)
yield p.close()
defer.returnValue(self.createEvent('ok', 'Connection ok', 1,
prefix='state'))
except exceptions.ImportError:
log.msg('tensor.sources.database.postgresql.PostgreSQL'
' requires psycopg2')
defer.returnValue(None)
except Exception, e:
defer.returnValue(self.createEvent('critical',
'Connection error: %s' % str(e).replace('\n',' '),
0, prefix='state')
)
# encoding: utf-8
# Example 8-1. db_test.py
from twisted.internet import reactor
from twisted.enterprise import adbapi
dbpool = adbapi.ConnectionPool("sqlite3", "users.db", check_same_thread=False)
def getName(email):
return dbpool.runQuery("SELECT name FROM users WHERE email = ?",
(email,))
def printResults(results):
for elt in results:
print elt[0]
def finish():
dbpool.close()
reactor.stop()
d = getName("*****@*****.**")
d.addCallback(printResults)
reactor.callLater(1, finish)
reactor.run()
# When using adbapi with SQLite, if you encounter an error of the form:
# sqlite3.ProgrammingError: SQLite objects created in a thread
# can only be used in that same thread.The object was created in
# thread id 5972 and this is thread id 4916
# you’ll need to create your ConnectionPool with
# check_same_thread=False, as in:
# dbpool = adbapi.ConnectionPool("sqlite3", "users.db",
# check_same_thread=False)
# See Twisted ticket 3629 for details.
def __init__(self):
dbparams = config._Dbparams
self.dbpol = adbapi.ConnectionPool(
'pymysql', **dbparams) #根據不同DB lib 建立連接池 (建立對應的conn物件)
self._Insertsql = None
def build_db_pool(self):
name = self.db_config["name"]
return adbapi.ConnectionPool(name,
cp_reactor=self.get_reactor(),
**self.db_config.get("args", {}))
from twisted.enterprise import adbapi
from twisted.internet.task import LoopingCall
from twisted.python import log
import sys
import os
sys.path.insert(0, os.getcwd())
import secret
import re
import datetime
import pyiem.nwnformat as nwnformat
from pyiem.observation import Observation
from pyiem.network import Table as NetworkTable
NT = NetworkTable(("KCCI", "KIMT", "KELO"))
DBPOOL = adbapi.ConnectionPool("psycopg2",
database='iem',
host=secret.dbhost,
cp_reconnect=True)
DB = {}
class NWNClientFactory(hubclient.HubClientProtocolBaseFactory):
"""
Simple client to NWN server
"""
maxDelay = 60.0
factor = 1.0
initialDelay = 60.0
def processData(self, data):
"""
implemented callback
__author__ = 'lunner'
from twisted.enterprise import adbapi
from twisted.internet import reactor
dbpool = adbapi.ConnectionPool("sqlite3", 'ISU_server.db')
def _createUsersTable(transaction, users):
transaction.execute("CREATE TABLE users (email TEXT, name TEXT)")
for email, name in users:
transaction.execute("INSERT INTO users (email, name) VALUES(?, ?)", (email, name))
def createUsersTable(users):
return dbpool.runInteraction(_createUsersTable, users)
def getByName(name):
return dbpool.runQuery("SELECT * FROM users WHERE ISUname = ?", (name, ))
def printResults(results):
for elt in results:
print elt
def printFailure(failure):
print failure
def finish():
dbpool.close()
reactor.stop()
users = [("*****@*****.**", "jane"), ("*****@*****.**", "joel")]
#d = createUsersTable(users)
#d.addCallback(lambda x: getByName("lunner"))
def __init__(self):
self.dbpool = adbapi.ConnectionPool('MySQLdb', db='crawl_cic',
user='******', passwd='12345', cursorclass=MySQLdb.cursors.DictCursor,
charset='utf8', use_unicode=True)
def adb_connect_db(db_type, **kwargs):
'''
db_type-->"MySQLdb"
'''
dbpool = adbapi.ConnectionPool(db_type, **kwargs)
return dbpool
def init(config):
global dbpool
dbpool = adbapi.ConnectionPool(config['dbmodule'], host=config['host'], port=config['port'],
user=config['user'], passwd=config['password'], db=config['dbname'], autoreconnect=True)
def makeService(self, options):
if options['conf']:
settings.conf = settings.load(options['conf'])
# we better add
reactor.suggestThreadPoolSize(settings.conf['threadpool size'])
if options['memdebug']:
from twisted.internet import task
import objgraph
import gc
def stats():
print gc.collect()
print
print '\n'.join(map(str, objgraph.most_common_types(limit=10)))
task.LoopingCall(stats).start(2)
def connection_callback(conn):
# switch client charset to utf8 and enable hstore
conn.set_client_encoding("UTF8")
psycopg2.extras.register_hstore(conn)
cp = adbapi.ConnectionPool(
settings.conf['database']['module'],
host=settings.conf['database']['host'],
database=settings.conf['database']['db'],
user=settings.conf['database']['user'],
password=settings.conf['database']['password'],
port=settings.conf['database']['port'],
cp_min=5,
cp_max=30,
cp_reconnect=True,
cp_openfun=connection_callback)
if options['subscribe']:
subscribe(cp, settings)
# create a single republisher to send the data out on
http_repub = republisher.ReResource(cp)
websocket_repub = republisher.WebSocketRepublishResource(cp)
if settings.conf['mongo']['enabled']:
mongo_repub = republisher.MongoRepublisher(cp)
else:
mongo_repub = None
if settings.conf['database']['republish']:
print "enabling pg republishing"
pg_repub = republisher.PostgresEndpoint(cp)
else:
pg_repub = None
service = MultiService()
for svc in settings.conf['server']:
scfg = settings.conf['server'][svc]
site = getSite(cp,
resources=scfg['resources'],
http_repub=http_repub,
websocket_repub=websocket_repub,
mongo_repub=mongo_repub,
pg_repub=pg_repub)
if not len(scfg['ssl']) > 1:
service.addService(
internet.TCPServer(scfg['port'],
site,
interface=scfg['interface']))
else:
service.addService(
internet.SSLServer(scfg['port'],
site,
SslServerContextFactory(scfg['ssl']),
interface=scfg['interface']))
return service
from twisted.internet import reactor
from twisted.enterprise import adbapi
dbpool = adbapi.ConnectionPool("MySQLdb", host="dbhost", db='dbname', user='******', passwd='password')
def getNickname(player_id):
return dbpool.runQuery("SELECT nickname FROM players WHERE id=%(id)s", {"id": player_id})
def printResult(l):
if l:
print "Players nickname is ", l[0][0]
else:
print "No such player"
getNickname(1).addCallback(printResult)
reactor.run()
# CLOUD CODE
#==========================================================
# ******A global variable for usr and pw is needed so i can use string formating
#*** |HINT| look at BABY.py DBvalue = [] global variable
#usr = []
#pwd = []
#print usr,pwd
dbpool = adbapi.ConnectionPool("MySQLdb",
db = "CLOUD_home_storage",
user = "******",
passwd = "Thulan12",
cp_reconnect = True ) # cp_reconnect = True %(usr,pw))
class CLOUD_DATABASE():
# NOTE comment this and see what happens
def __init__(self):
self.DBvalue = []
#---|ALARM update|
#!/usr/bin/env python
#####################################################
##
## asynchronise access of database
##
#####################################################
from twisted.enterprise import adbapi
from twisted.internet import reactor
dbpool = adbapi.ConnectionPool("psycopg2",database = "f2", user =
"******", password = "******")
def getId():
return dbpool.runQuery("SELECT id FROM robot_commandstreams")
def printResult(l):
if l:
print l
else:
print "no such result"
def insertCommand():
q = "INSERT INTO robot_commandstreams (feed_id,stream_id,updated_at,current_value) VALUES ('53dde5e6a52633d704000003','c_left_speed','2014-08-20 09:10:18',10);"
reactor.callLater(1,insertCommand)
dbpool.runOperation(q)
if __name__ == "__main__":
getId().addCallback(printResult)
