def add_dataset_group_permission_id(configs_dir, db_dataset_id,
dataset_permission_group_type, permission,
accessee, view_dataset, view_name):
try:
# Create the query string:
query_string = "insert into existing_dataset_permissions(" \
"existing_google_big_query_dataset_id, existing_dataset_permission_group_type," \
"existing_dataset_permission_permission, existing_dataset_permission_accessor," \
"existing_dataset_permission_view_dataset, existing_dataset_permission_view_view_name) " \
"values ({}, '{}', '{}', '{}', '{}', '{}')".format(db_dataset_id, dataset_permission_group_type,
permission, accessee, view_dataset, view_name)
print(query_string)
db_config = read_db_config(
filename='{}/mysql_config.ini'.format(configs_dir))
db_connection = MySQLConnection(**db_config)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(query_string)
if cursor.lastrowid:
return cursor.lastrowid
else:
print('last insert id not found')
db_connection.commit()
db_connection.close()
except Error as e:
print(e)
finally:
if db_connection.is_connected():
db_connection.close()
cursor.close()
print("MySQL connection is closed")
def add_existing_google_cloud_project_id(configs_dir, project_name):
try:
# Create the query string:
query_string = "insert into existing_google_cloud_projects (existing_google_cloud_project_name) " \
"values ('{}')".format(project_name)
db_config = read_db_config(
filename='{}/mysql_config.ini'.format(configs_dir))
db_connection = MySQLConnection(**db_config)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(query_string)
if cursor.lastrowid:
return cursor.lastrowid
else:
print('last insert id not found')
db_connection.commit()
db_connection.close()
except Error as e:
print(e)
finally:
if db_connection.is_connected():
db_connection.close()
cursor.close()
print("MySQL connection is closed")
def openDbConnection():
global vezaSaBazom, cursor
vezaSaBazom = MySQLConnection(host='localhost', port="3333",
database='emd',
user='******', password='******')
cursor = vezaSaBazom.cursor()
vezaSaBazom.autocommit = True
def query_database(self, sql_query, data, type):
types = ("insert", "select")
if type in types:
try:
conn = MySQLConnection(user=db_user,
password=db_password,
host=db_host,
database=db_bd)
conn.autocommit = True
cursor = conn.cursor()
try:
cursor.execute(sql_query, data)
if (type == "insert"):
# Возврат идентификатора добавленной строки
return cursor.lastrowid
elif (type == "select"):
# Возврат кортеж значения запроса
rows = cursor.fetchall()
return rows
except Exception as e:
log.info("Ошибка запроса: %s. Конец программы", e)
exit(6)
finally:
cursor.close()
conn.close()
except Exception as e:
log.info(
"Ошибка подключения к базе данных: %s. Конец программы", e)
exit(6)
else:
log.info(
"Вы не правильно указали тип SQL - запроса. Должно быть select или insert. Конец программы."
)
exit(5)
def insert_arch(mass, wmo_id):
try:
print("attempt to write to the table")
cnx = MySQLConnection(user='******',
password='******',
host='localhost',
database='python_mysql')
cnx.autocommit = False
cursor = cnx.cursor()
query = QUERY_INSERT1 + str(wmo_id) + QUERY_INSERT2
cursor.executemany(query, mass)
cnx.commit()
if cnx.is_connected():
print('connection established')
else:
print('connection failed')
except Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
finally:
cursor.close()
cnx.close()
print('connection closed.')
def get_existing_dataset_view_permission_id(configs_dir, db_dataset_id,
view_dataset_data,
view_table_name_data):
try:
# Create the query string:
query_string = "select existing_dataset_permission_id " \
"from existing_dataset_permissions " \
"where existing_google_big_query_dataset_id = {}" \
" and existing_dataset_permission_view_dataset = '{}' " \
" and existing_dataset_permission_view_view_name = '{}'".format(db_dataset_id,
view_dataset_data,
view_table_name_data)
print(query_string)
db_config = read_db_config(
filename='{}/mysql_config.ini'.format(configs_dir))
db_connection = MySQLConnection(**db_config)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(query_string)
records = cursor.fetchall()
for row in records:
return row[0]
except Error as e:
print('{} for SQL: {}'.format(e, query_string))
finally:
if db_connection.is_connected():
db_connection.close()
cursor.close()
print("MySQL connection is closed")
def ads():
website = request.args.get('website', 'earthtravelers.com')
user = app.config['DB_LOGIN']
password = app.config['DB_PW']
host = app.config['DB_HOST']
database = app.config['DB_NAME']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
args = (website,)
try:
cursor.callproc('AdMania.prc_GetAds', args)
except Error as e:
print e
# In order to handle multiple result sets being returned from a database call,
# mysql returns results as a list of lists.
# Therefore, even if there is only one result set, you still have to get it from the list of lists.
for result in cursor.stored_results():
row_set = result.fetchall()
result_set = []
for row in row_set:
result_set.append(row[0].decode().replace('##DOMAIN_ID##', '7782886'))
cursor.close()
conn.close()
return render_template('T1.html',resultsSET=result_set)
def add_existing_google_cloud_dataset_id(configs_dir, dataset_name, project_id,
dataset_self_link, dataset_full_id,
dataset_last_modified_time,
dataset_creation_time,
dataset_location, dataset_etag,
dataset_id):
try:
# Create the query string:
query_string = "INSERT INTO existing_google_big_query_datasets \
( \
existing_google_big_query_dataset_name, \
existing_google_big_query_project_id, \
existing_google_big_query_dataset_self_link, \
existing_google_big_query_dataset_full_id, \
existing_google_big_query_dataset_last_modified_time, \
existing_google_big_query_dataset_creation_time, \
existing_google_big_query_dataset_location, \
existing_google_big_query_dataset_etag, \
existing_google_big_query_dataset_dataset_id \
) \
VALUES \
( \
'{}', \
{}, \
'{}', \
'{}', \
'{}', \
'{}', \
'{}', \
'{}', \
'{}' \
)".format(dataset_name, project_id, dataset_self_link,
dataset_full_id, dataset_last_modified_time,
dataset_creation_time, dataset_location,
dataset_etag, dataset_id)
db_config = read_db_config(
filename='{}/mysql_config.ini'.format(configs_dir))
db_connection = MySQLConnection(**db_config)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(query_string)
if cursor.lastrowid:
return cursor.lastrowid
else:
print('last insert id not found')
db_connection.commit()
db_connection.close()
except Error as e:
print(e)
finally:
if db_connection.is_connected():
db_connection.close()
cursor.close()
print("MySQL connection is closed")
def select_database(sql, data):
try:
conn = MySQLConnection(user = db_user, password = db_password, host = db_host, database = db_bd)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql, data)
rows = cursor.fetchall()
cursor.close()
conn.close()
return rows
except Error as e:
print("Ошибка:", e)
def database_update():
config=Config().config
user = config['DB_LOGIN']
password = config['DB_PW']
host = config['DB_HOST']
database = config['DB_NAME']
cjauth = config['CJ_AUTH']
cjurl = config['CJ_URL']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
page_number = 0
records_per_page = 100 # this is the max number allowed by the affiliate api per call.
records_returned = records_per_page
headers = {'authorization': cjauth}
while records_returned == records_per_page:
page_number += 1
params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}
result = requests.get(cjurl, headers=headers, params=params)
result_xml = result.text
root = ET.fromstring(result_xml.encode('utf8'))
records_returned = int(root.find('links').get('records-returned'))
for link in root.iter('link'):
link_code_html = html.fromstring(link.find('link-code-html').text)
height = int(link_code_html.xpath('//img/@height')[0])
width = int(link_code_html.xpath('//img/@height')[0])
mysql_args = (
link.find('link-id').text,
link.find('advertiser-id').text,
link.find('advertiser-name').text,
link.find('category').text,
'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
height,
width,
link.find('link-code-html').text)
try:
cursor.callproc('AdMania.prc_UpdateAd',mysql_args)
except Error as e:
print e
cursor.close()
conn.close()
def update_database(sql, data):
try:
conn = MySQLConnection(user = db_user, password = db_password, host = db_host, database = db_bd)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql, data)
lastrowid = cursor.lastrowid
cursor.close()
conn.close()
return lastrowid
except Error as e:
print("Ошибка:", e)
def api_call_logger(google_api_action, internal_google_cloud_project_id,
internal_google_bigquery_dataset_id,
api_call_logger_message):
try:
# Get the config file directory for the MySQL connection:
configs_dir = get_dir_location('configs')
# Create the MySQL config filepath:
mysql_config_filepath = os.path.join(configs_dir, 'mysql_config.ini')
# Create the query
log_query = "insert into permissions_history.api_dataset_actions(" \
" api_dataset_action_datetime," \
" api_dataset_action_action_id," \
" internal_project_id," \
" internal_dataset_id," \
" api_action_state_description" \
")" \
"values(" \
" now()," \
" (select a.action_id from permissions_history.actions a where action_name = '{}')," \
" {}," \
" {}," \
" '{}'" \
")".format(google_api_action, internal_google_cloud_project_id,
internal_google_bigquery_dataset_id, api_call_logger_message)
# Set the connection properties:
db_config = read_db_config(filename=mysql_config_filepath)
# Create the connection
db_connection = MySQLConnection(**db_config)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(log_query)
if cursor.lastrowid:
return cursor.lastrowid
else:
print('last insert id not found')
db_connection.commit()
db_connection.close()
except Error as e:
print(e)
finally:
if db_connection.is_connected():
db_connection.close()
cursor.close()
def insert_database(sql, data):
"""
Метод для выполнения insert запроса к СУБД
:param sql:
:param data:
:return:
"""
try:
conn = MySQLConnection(user = db_user, password = db_password, host = db_host, database = db_bd)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql, data)
lastrowid = cursor.lastrowid
cursor.close()
conn.close()
return lastrowid
except Error as e:
print("Ошибка insert", e)
def select_database(sql, data):
"""
Метод для выполнения select запроса к СУБД
:param sql:
:param data:
:return:
"""
try:
conn = MySQLConnection(user = db_user, password = db_password, host = db_host, database = db_bd)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql, data)
rows = cursor.fetchall()
cursor.close()
conn.close()
return rows
except Error as e:
print("Ошибка select:", e)
def connect(credentials):
"""connect to the database"""
try:
conn = MySQLConnection(**credentials)
if conn.is_connected():
conn.autocommit = True
return conn, conn.cursor(prepared=True)
else:
print('mysql connection failed.')
sys.exit(1)
except Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
sys.exit(2)
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
sys.exit(3)
else:
print(err)
sys.exit(4)
from config import Config
from mysql.connector import MySQLConnection, Error
config=Config().config
user = config['DB_LOGIN']
password = config['DB_PW']
host = config['DB_HOST']
database = config['DB_NAME']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
args = (1111,2222,'2015-06-06','2015-06-07','HHHTTTMMMLLO')
cursor.callproc("prc_UpdateAd", args)
cursor.execute("select * from Ad;")
rows = cursor.fetchall()
for row in rows:
print row[0],row[1]
cursor.close()
conn.close()
intents = discord.Intents.all()
# Load the bot's Discord Token
load_dotenv()
TOKEN = os.getenv('DISCORD_TOKEN')
print(f'{TOKEN} has connected to Discord!')
# Setup a mysql DB Connection
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
if conn.is_connected():
print("Database Connection Successful.")
else:
print("Database Connection Failed.")
exit
conn.autocommit = True
cursor = conn.cursor()
# Setup Command Prefix
bot = commands.Bot(command_prefix='!sb ', intents=intents)
@bot.command(name='echo', help='Echos the message back to a user')
async def echo(ctx, *args):
# Echo the message back to the user
response = ""
for arg in args:
response = response + " " + arg
response = ctx.message.content
response = response.replace('!sb echo', '', 1)
await ctx.send(response)
from PyQt5.QtGui import QDrag
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QPushButton, QWidget, QApplication, QGridLayout, QScrollArea, QMainWindow, QSlider, QLabel, QFileDialog, QInputDialog
import math
import csv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from mysql.connector import MySQLConnection, Error
#Otvori vezu sa bazom
vezaSaBazom = MySQLConnection(host='localhost', port="3333",
database='emd',
user='******', password='******')
vezaSaBazom.autocommit = True
#Incijalizacija cursor objekta
cursor = vezaSaBazom.cursor()
class Stroj:
def __init__(self, rok, naziv, trajanje):
self.rok = rok
self.naziv = naziv
self.trajanje = trajanje
class Button(QPushButton):
def __init__(self, title, parent):
def database_update():
logger.debug('Database Update Starting')
config=Config()
user = config['DB_LOGIN']
password = config['DB_PW']
host = config['DB_HOST']
database = config['DB_NAME']
cjauth = config['CJ_AUTH']
cjurl = config['CJ_URL']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
page_number = 0
total_records_returned = 0
records_per_page = 100 # this is the max number allowed by the affiliate api per call.
records_returned = records_per_page
headers = {'authorization': cjauth}
while records_returned == records_per_page:
page_number += 1
params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}
result = requests.get(cjurl, headers=headers, params=params)
result_xml = result.text
root = ET.fromstring(result_xml.encode('utf8'))
records_returned = int(root.find('links').get('records-returned'))
total_matched = int(root.find('links').get('total-matched'))
total_records_returned += records_returned
logger.info('Total Matched: {}, Records Returned: {}, Total Records Returned: {}'.format(total_matched, records_returned, total_records_returned))
link_number = 0
for link in root.iter('link'):
link_number+= 1
percent_complete = ((total_records_returned - records_returned + link_number) * 100) / total_matched
logger.debug('Page Number: {}, Link Number: {}, Percent Complete: {}%'.format(page_number, link_number, percent_complete))
link_code_html = html.fromstring(link.find('link-code-html').text)
height = int(link_code_html.xpath('//img/@height')[0])
width = int(link_code_html.xpath('//img/@height')[0])
mysql_args = (
link.find('link-id').text,
link.find('advertiser-id').text,
link.find('advertiser-name').text,
link.find('category').text,
'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
height,
width,
link.find('link-code-html').text.replace('7782886', '##DOMAIN_ID##'))
try:
cursor.callproc('AdMania.prc_UpdateAd',mysql_args)
except Error as e:
print e
cursor.close()
conn.close()
logger.debug('Database Update Complete')
