def searchplatformbyname():
print("-" * 40)
print(" SEARCH PLATFORM BY ITS NAME ")
print("-" * 40)
platformname = input(
"Type some character of the platform you want to search: ")
if platformname == "": #Nothing typed. Showing the whole platform library
with con:
cur = con.cursor()
cur.execute("SELECT * FROM PlatformDetails")
tb = from_db_cursor(cur)
tb.field_names = ["Platform Name", "Platform Account"]
print(tb.get_string(title="Match(es) Found"))
_ = input("Press a key to exit")
main()
else:
with con:
cur = con.cursor()
cur.execute(
"SELECT * FROM PlatformDetails WHERE PlatformName LIKE ?",
('%{}%'.format(platformname), ))
tb = from_db_cursor(cur)
tb.field_names = ["Platform Name", "Platform Account"]
print(tb.get_string(title="Match(es) Found"))
_ = input("Press a key to exit")
main()
def listautok(word=False):
"""
Lists the autok pairs defined for a word, or all the autok
:param word: word to return value for or everything
:return: table with autok stored
"""
logger = logging.getLogger(__name__)
if word:
# if word is provided, return the alias for that word
string = (word,)
sql = "SELECT * FROM autokarma WHERE key='%s' ORDER by key ASC;" % string
dbsql(sql)
try:
# Get value from SQL query
text = "Defined autokarma triggers for word %s:\n" % word
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
except:
# Value didn't exist before
text = "%s has no trigger autokarma" % word
else:
sql = "select * from autokarma ORDER BY key ASC;"
dbsql(sql)
text = "Defined autokarma triggers:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning autokarma %s for word %s" % (text, word))
return text
def searchgamebyname():
print("-" * 40)
print(" SEARCH GAME BY ITS NAME ")
print("-" * 40)
gamename = input("Type some character of the game you want to search: ")
if gamename == "": #Nothing typed. Showing the whole game library
with con:
cur = con.cursor()
cur.execute("SELECT * FROM GameLibrary")
tb = from_db_cursor(cur)
tb.field_names = [
"Game ID", "Game Name", "Platform #1", "Platform #2",
"Platform #3", "Platform #4", "Platform #5", "Metascore", "DLC"
]
tb.align["Metascore"] = "r"
print(tb.get_string(title="Games Found"))
_ = input("Press a key to exit")
main()
else:
with con:
cur = con.cursor()
cur.execute("SELECT * FROM GameLibrary WHERE GameName LIKE ?",
('%{}%'.format(gamename), ))
tb = from_db_cursor(cur)
tb.field_names = [
"Game ID", "Game Name", "Platform #1", "Platform #2",
"Platform #3", "Platform #4", "Platform #5", "Metascore", "DLC"
]
tb.align["Metascore"] = "r"
print(tb.get_string(title="Games Found"))
_ = input("Press a key to exit")
main()
def print_all_activities():
cur.execute(
"SELECT aname, nickname, qty_start, qty_end, pts FROM IntervalActivity NATURAL JOIN Activity WHERE username = %s;",
(username, ))
int_activities = from_db_cursor(cur)
if len(int_activities._rows) != 0:
print "\nInterval Activities for %s are:" % username
print int_activities
cur.execute(
"SELECT aname, nickname, qty, pts FROM ScaleQtyActivity NATURAL JOIN Activity WHERE username = %s;",
(username, ))
sc_activities = from_db_cursor(cur)
if len(sc_activities._rows) != 0:
print "\nScaled Activities for %s are:" % username
print sc_activities
cur.execute(
"SELECT aname, nickname, yes_pts, no_pts FROM YesNoActivity NATURAL JOIN Activity WHERE username = %s;",
(username, ))
yn_activities = from_db_cursor(cur)
if len(yn_activities._rows) != 0:
print "\nYesNo Activities for %s are:" % username
print yn_activities
if len(int_activities._rows) == 0 and len(
sc_activities._rows) == 0 and len(yn_activities._rows) == 0:
print "No Activities for %s" % username
def get_highscore():
cur.execute("SELECT max(points) FROM DayPoints WHERE username = %s;",
(username, ))
result = cur.fetchall()
assert (len(result) <= 1)
maxp = result[0][0] if len(result) == 1 else 0
conn.commit()
cur.execute(
"SELECT adate as date, points FROM DayPoints WHERE points = %s AND username = %s;",
(maxp, username))
print "\nThe highscore(s) till now is(are):"
print from_db_cursor(cur)
def hemorrhagic_query(self):
self.create_nominal_data()
self.create_table_incidence_data()
query_buffer_DF = "SELECT case_date, \
fever FROM incidence_data WHERE fever = 'FD'"
self.cur.execute(query_buffer_DF)
table_incidence = prettytable.from_db_cursor(self.cur)
table_incidence.set_style(prettytable.PLAIN_COLUMNS)
# print table_incidence
table_incidence.header = True
table_incidence.align = "l"
table_incidence.left_padding_width = 0
table_incidence.right_padding_width = 1
string = table_incidence.get_string()
string = str(string)
result_DF = [
tuple(filter(None, map(str.strip, split_line)))
for line in string.splitlines()
for split_line in [line.split(" ")] if len(split_line) > 1
]
with open('./data/incidence_data_DF.csv', 'wb') as outcsv:
writer = csv.writer(outcsv)
writer.writerows(result_DF)
#
# frecuency per day counting
#
query_buffer_DHF = "SELECT case_date, \
fever FROM incidence_data WHERE fever = 'FHD'"
self.cur.execute(query_buffer_DHF)
table_incidence = prettytable.from_db_cursor(self.cur)
table_incidence.set_style(prettytable.PLAIN_COLUMNS)
# print table_incidence
table_incidence.header = True
table_incidence.align = "l"
table_incidence.left_padding_width = 0
table_incidence.right_padding_width = 1
string = table_incidence.get_string()
string = str(string)
result_DHF = [
tuple(filter(None, map(str.strip, splitline)))
for line in string.splitlines() for splitline in [line.split(" ")]
if len(splitline) > 1
]
with open('./data/incidence_data_DHF.csv', 'wb') as outcsv:
writer = csv.writer(outcsv)
writer.writerows(result_DHF)
self.result_DHF = result_DHF
self.result_DF = result_DF
return result_DF, result_DHF
def dumpdb():
''' Purely for debugging - prints out the users and answers tables '''
import prettytable
c = database.cursor()
ret = str(prettytable.from_db_cursor(c.execute("SELECT * FROM users"))) + "\n\n"
ret += str(prettytable.from_db_cursor(c.execute("SELECT * FROM answers"))) + "\n\n"
c.execute("SELECT * FROM extraData")
table = prettytable.PrettyTable()
table.field_names = [col[0] for col in c.description]
for row in c.fetchall():
newRow = list(row)
newRow[1] = pickle.loads(row[1])
table.add_row(newRow)
ret += str(table)
return ret
def findPokemonWithSpecies(_conn, _species, _type):
try:
if _type == "all":
sql = """SELECT *
from Pokemon
where species = ?"""
args = [_species]
else:
sql = """SELECT *
from Pokemon
where species = ?
and (type1 = ? or type2 = ?)"""
args = [_species, _type, _type]
cur = _conn.cursor()
cur.execute(sql, args)
row = cur.fetchone()
if row == None:
print("There are no results for this query")
return
cur.execute(sql, args)
mytable = from_db_cursor(cur)
print(mytable)
except Error as e:
_conn.rollback()
print(e)
def findStrongestPokemon(_conn, _type):
try:
args = [_type, _type]
if _type == "all":
sql = """select Pokemon.pokeName, max(attack + defense)
from Pokemon, Stats
where type2 = 'NULL'
and Stats.pokeName = Pokemon.pokeName;"""
cur = _conn.cursor()
cur.execute(sql)
else:
sql = """select Pokemon.pokeName, max(attack + defense)
from Pokemon, Stats
where (type1 = ? or type2 = ?)
and Stats.pokeName = Pokemon.pokeName;"""
cur = _conn.cursor()
cur.execute(sql, args)
row = cur.fetchone()
if row == None:
print("There are no results for this query")
return
if _type == "all":
cur.execute(sql)
else:
cur.execute(sql, args)
mytable = from_db_cursor(cur)
print(mytable)
except Error as e:
_conn.rollback()
print(e)
def findPokemonWithLocation(_conn, _locationID):
try:
args = [_locationID]
if _locationID == "all":
sql = """SELECT
Location.locationId, Location.name, Pokemon.pokeName
FROM Pokemon, Location
WHERE Pokemon.locationIndex = Location.locationId;"""
cur = _conn.cursor()
cur.execute(sql)
else:
sql = """SELECT
Location.locationId, Location.name, Pokemon.pokeName
FROM Pokemon, Location
WHERE Pokemon.locationIndex = Location.locationId
and Location.locationId = ?"""
cur = _conn.cursor()
cur.execute(sql, args)
row = cur.fetchone()
if row == None:
print("There are no results for this query")
return
if _locationID == "all":
cur.execute(sql)
else:
cur.execute(sql, args)
mytable = from_db_cursor(cur)
print(mytable)
except Error as e:
_conn.rollback()
print(e)
def findMoveWithType(_conn, _move, _type):
try:
if _move == "all":
sql = """SELECT
type, count(move)
FROM Move
where type = ?
GROUP BY type;"""
cur = _conn.cursor()
args = [_type]
else:
sql = """SELECT
type, count(move)
FROM Move
WHERE type = ?
and move = ?
GROUP BY type;"""
cur = _conn.cursor()
args = [_type, _move]
cur.execute(sql, args)
row = cur.fetchone()
if row == None:
print("There are no results for this query")
return
cur.execute(sql, args)
mytable = from_db_cursor(cur)
print(mytable)
except Error as e:
_conn.rollback()
print(e)
def listalias(word=False):
"""
Lists the alias defined for a word, or all the aliases
:param word: word to return value for or everything
:return: table with alias stored
"""
logger = logging.getLogger(__name__)
if word:
# if word is provided, return the alias for that word
string = (word,)
sql = "SELECT * FROM alias WHERE key='%s' ORDER by key ASC;" % string
dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "%s has an alias %s" % (word, value)
else:
sql = "select * from alias ORDER BY key ASC;"
dbsql(sql)
text = "Defined aliases:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning aliases %s for word %s" % (text, word))
return text
def showconfig(key=False):
"""
Shows configuration in database for a key or all values
:param key: key to return value for
:return: Value stored
"""
logger = logging.getLogger(__name__)
if key:
# if word is provided, return the config for that key
string = (key,)
sql = "SELECT * FROM config WHERE key='%s';" % string
dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "%s has a value of %s" % (key, value)
else:
sql = "select * from config ORDER BY key ASC;"
dbsql(sql)
text = "Defined configurations:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning config %s for key %s" % (text, key))
return text
def __init__(self, query, cursor, error): self.query = query self.cursor = cursor self.error = error self.menumode = False if cursor is not None and not self.has_error(): self.table_view = prettytable.from_db_cursor(cursor)
def listautok(word=False, gid=0):
"""
Lists the autok pairs defined for a word, or all the autok
:param gid: filter to group id
:param word: word to return value for or everything
:return: table with autok stored
"""
logger = logging.getLogger(__name__)
wordtext = ""
if not word:
sql = "select key,value from autokarma ORDER BY key ASC;"
else:
string = (word, gid)
sql = "SELECT key,value FROM autokarma WHERE key='%s' AND gid='%s' ORDER by key ASC;" % string
wordtext = _("for word %s for gid %s") % (word, gid)
cur = stampy.stampy.dbsql(sql)
try:
# Get value from SQL query
text = _("Defined autokarma triggers %s:\n") % wordtext
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
except:
# Value didn't exist before
text = _("%s has no trigger autokarma") % word
logger.debug(msg=_L("Returning autokarma %s for word %s") % (text, word))
return text
def get_report_fys(self, command_text):
"""
Формирвоание статистики кого сколько раз послали нахуй
:command_text -who выведется статистика кто сколько раз отпарвил команду
:return: текст статисттики
"""
command_text = command_text.split(" ")[1:]
if '@' in command_text:
command_text = command_text.split('@')[0]
column = "user_id"
if '-who' in command_text:
column = "who_send"
sql = f"""
SELECT u.username,
count(fys.id)
FROM public.users u
LEFT JOIN public.fuck_your_selfs fys ON fys.{column} = u.id
AND extract(YEAR
FROM fys.date_fuck_your_self) = extract(YEAR
FROM now())
GROUP BY u.username
ORDER BY count(fys.id) DESC"""
mytable = from_db_cursor(self._pg_execute(sql))
text = f"<code>Количество посыланий нахуй:\n{mytable}</code>"
return text
def rateMenu(self, cid, bill_id):
self.bill_id = bill_id
self.cid = cid
print("This is our product list:")
try:
cur.execute("SELECT Product_Name, Price, GST FROM Product")
y = from_db_cursor(cur)
print(y)
cls()
except Error as e:
print(e)
buy = input("Want to buy? (y/n): ").lower()
if buy == 'y':
if 1 != 0: # do condition of do while loop implementation
cls()
self.currentBuy(cid, bill_id)
cls()
while True:
buyMore = input("Want to buy more ? (y/n): ").lower()
if buyMore == 'y':
cls()
self.currentBuy(cid, bill_id)
continue
else:
break
else:
pass
else:
pass
if buy == 'y':
self.bill(cid, bill_id)
else:
print("Nothing ordered")
def showconfig(key=False, gid=0):
"""
Shows configuration in database for a key or all values
:param gid: group ID to check
:param key: key to return value for
:return: Value stored
"""
logger = logging.getLogger(__name__)
if key:
# if word is provided, return the config for that key
string = (key, )
sql = "SELECT key,value FROM config WHERE key='%s' AND id='%s';" % (
string, gid)
cur = stampy.stampy.dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = _("%s has a value of %s for id %s") % (key, value, gid)
else:
sql = "select key,value from config WHERE id='%s' ORDER BY key ASC;" % gid
cur = stampy.stampy.dbsql(sql)
text = _("Defined configurations for gid %s:\n") % gid
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg=_L("Returning config %s for key %s for id %s") %
(text, key, gid))
return text
def file():
c.execute('SELECT * FROM HOTEL3')
mytable = from_db_cursor(c)
file = open('HOTELDATABASE.txt', 'w')
file.write(tabulate(mytable))
toaddr = J3.get()
fromaddr = "*****@*****.**"
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = toaddr
msg['Subject'] = "HOTEL DATABASE PYTHON PROJECT"
body = "OPEN ATTACHMENT FOR HOTELDATABASE"
msg.attach(MIMEText(body, 'plain'))
filename = "textfile.txt"
attachment = open('HOTELDATABASE.txt', 'rb')
part = MIMEBase('application', 'octet-stream')
part.set_payload((attachment).read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename=%s" % filename)
msg.attach(part)
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(fromaddr, "797@dhiren")
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit()
os.system('C:/Users/dhire/Desktop/HOTELDATABASE.txt')
IC63.config(text="EMAIL SENT")
def setUp(self):
self.conn = sqlite3.connect(":memory:")
self.cur = self.conn.cursor()
self.cur.execute(
"CREATE TABLE cities (name TEXT, area INTEGER, population INTEGER, rainfall REAL)"
)
self.cur.execute(
"INSERT INTO cities VALUES (\"Adelaide\", 1295, 1158259, 600.5)"
)
self.cur.execute(
"INSERT INTO cities VALUES (\"Brisbane\", 5905, 1857594, 1146.4)"
)
self.cur.execute(
"INSERT INTO cities VALUES (\"Darwin\", 112, 120900, 1714.7)")
self.cur.execute(
"INSERT INTO cities VALUES (\"Hobart\", 1357, 205556, 619.5)")
self.cur.execute(
"INSERT INTO cities VALUES (\"Sydney\", 2058, 4336374, 1214.8)"
)
self.cur.execute(
"INSERT INTO cities VALUES (\"Melbourne\", 1566, 3806092, 646.9)"
)
self.cur.execute(
"INSERT INTO cities VALUES (\"Perth\", 5386, 1554769, 869.4)")
self.cur.execute("SELECT * FROM cities")
self.x = from_db_cursor(self.cur)
def view():
limit = request.args.get('limit', default=0, type=int)
wkey = request.args.get('wkey', default=None, type=str)
wvalue = request.args.get('wvalue', default=None, type=str)
console = request.args.get('console', default=False, type=bool)
try:
cnx = connection.get_connector()
cursor = cnx.cursor()
query = connection.get_view_log_query(limit, wkey, wvalue)
cursor.execute(query)
if (console):
mytable = from_db_cursor(cursor)
ret = mytable.get_string()
else:
tbody = ""
for row in cursor:
tbody += "<tr><td>"
tbody += "</td><td>".join(map(str, row))
tbody += "</td></tr>"
ret = render_template("view.html", tbody=tbody)
cursor.close()
cnx.close()
return ret
except Exception as e:
return str(e)
def show_count():
cur.execute(
'''
select datetime, SUM(count_of_water) as count
from water_balance
where datetime = ?''', (datetime.datetime.now().strftime('%d.%m.%Y'), ))
print(prettytable.from_db_cursor(cur))
def deletenote():
print('----------DELETE----------------')
db = sqlite3.connect('noter')
sql = "SELECT * from notes;"
cur = db.cursor()
cur.execute(sql)
x = from_db_cursor(cur)
print(x)
db.close()
pick = input('Choose ID:')
int(pick)
db = sqlite3.connect('noter')
sql = ("DELETE FROM notes where id=" + (pick))
print(sql)
cur = db.cursor()
cur.execute(sql)
print('--------------------------')
try:
db.commit()
print("one record updated successfully")
except:
print("error in operation")
db.rollback()
db.close()
def draw_view(url):
try:
html = ''
cur = get_cursor("""
SELECT industry, sub_industry
--, industry|| '[' || sub_industry || ']' as 名称
, sub_industry as 名称
,round(sum(cb_premium_id)/count(sub_industry)*100,2) as avg_premium
,round(sum(cb_premium_id)/count(sub_industry)*100,2) || '%' as 溢价率
,round(sum(cb_price2_id)/count(sub_industry),2) as 转债价格
,'0' as bond_code
, sum(cb_premium_id*100) as sum
, count(*) as count
from changed_bond group by sub_industry
order by industry, avg_premium,sub_industry
""")
table = from_db_cursor(cur)
html += '<br/><br/>' + generate_scatter_html_with_one_table(
table, title='可转债行业分布', click_maker=click_maker)
return '可转债行业价格&溢价率分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def listalias(word=False):
"""
Lists the alias defined for a word, or all the aliases
:param word: word to return value for or everything
:return: table with alias stored
"""
logger = logging.getLogger(__name__)
if word:
# if word is provided, return the alias for that word
string = (word, )
sql = "SELECT * FROM alias WHERE key='%s' ORDER by key ASC;" % string
dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "%s has an alias %s" % (word, value)
else:
sql = "select * from alias ORDER BY key ASC;"
dbsql(sql)
text = "Defined aliases:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning aliases %s for word %s" % (text, word))
return text
def showconfig(key=False):
"""
Shows configuration in database for a key or all values
:param key: key to return value for
:return: Value stored
"""
logger = logging.getLogger(__name__)
if key:
# if word is provided, return the config for that key
string = (key, )
sql = "SELECT * FROM config WHERE key='%s';" % string
dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "%s has a value of %s" % (key, value)
else:
sql = "select * from config ORDER BY key ASC;"
dbsql(sql)
text = "Defined configurations:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning config %s for key %s" % (text, key))
return text
def Search_Hour(): #工作量查询
conn = sqlite3.connect(filename) #连接到数据库
c = conn.cursor() #数据库指针
try:
info = c.execute(
"select ID as 工号,Name as 姓名,Total_Course_Hour as 总开课学时\
from Teachers_info inner join course_info\
On Teachers_info.ID=Course_info.T_ID\
Union\
select ID as 工号,Name as 姓名,Total_Course_Hour as 总开课学时\
from Teachers_info left outer join course_info\
On Teachers_info.ID=Course_info.T_ID\
ORDER by Total_Course_Hour DESC")
table = from_db_cursor(info)
if (len(list(table)) == 0):
print('\n暂无工作量信息!')
else:
print(table.get_string(title='开课信息'))
c.close()
conn.close()
except (sqlite3.Error):
c.close()
conn.close()
print('操作失败!')
time.sleep(1)
return False
'''
def addGame(curs, conn):
"""One of the two primary functions in gameGuru. Allows user to add games to
the database file."""
title = input("Enter name of game: ")
year = input("Enter year game was released: ")
genre = inputGenreData(title, year, curs, conn)
developer = input("Enter developer: ")
maxPlayers = input("Enter max number of players: ")
ageCap = determineAgeCap()
inputIsForData(title, year, curs, conn)
# inserts data into the Games table
curs.execute("INSERT INTO Games VALUES(?, ?, ?, ?, ?, ?);",
(title, year, genre, developer, maxPlayers, ageCap))
conn.commit()
# converts 'query' into correct database table name when they differ
if genre == 'Action-Adventure':
queryGenre = 'Action_Adventure'
elif genre == 'Role-Playing':
queryGenre = 'RPG'
else:
queryGenre = genre
print(queryGenre, title)
# show user the game that they added
curs.execute(
"SELECT * FROM Games NATURAL JOIN isFor NATURAL JOIN " + queryGenre +
" WHERE title = ? AND year = ?", (title, year))
pt = from_db_cursor(curs)
print(pt)
def report(self, table):
"""Generate a prettytable object for table."""
# Here we don't use the query() method above because prettytable has a
# that will create the pretty string directly from the database cursor.
conn = self.connect()
c = conn.cursor()
# Regarding psycopg2's cursor.execute() method we have from
# http://initd.org/psycopg/docs/usage.html#query-parameters:
#
# "Only variable values should be bound via this method: it
# shouldn’t be used to set table or field names. For these
# elements, ordinary string formatting should be used before
# running execute()."
# grab the whole table
c.execute("select * from %s;" % table)
# produce the prettyprint object
x = prettytable.from_db_cursor(c)
conn.close()
# Now we can set some options. If they don't apply
# to the current table it's no big deal.
x.align['name'] = 'l'
x.align['winner'] = 'l'
x.align['loser'] = 'l'
x.align['id'] = 'r'
return x
def showconfig(key=False):
if key:
# if word is provided, return the config for that key
string = (key,)
sql = "SELECT * FROM config WHERE key='%s';" % string
cur.execute(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "%s has a value of %s" % (key, value)
else:
sql = "select * from config ORDER BY key DESC;"
cur.execute(sql)
text = "Defined configurations:\n"
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
log(facility="config", verbosity=9,
text="Returning config %s for key %s" % (text, key))
return text
def search_show(conn, title):
"""Search for a tvshow in the database"""
cur = conn.cursor()
row = cur.execute("SELECT * FROM tvshows WHERE title = ?", (title,))
tvshow = from_db_cursor(row)
return tvshow
def printgamesdb():
#Choice #3 of the main menu. Printing games library ordered by Platform
print("-" * 40)
print(" PRINTING GAMES LIBRARY ")
print(" Ordered by Platform Name")
print("-" * 40)
with con:
cur = con.cursor()
#Prompt the user whether wants to include DLCs in the report
includedlc = input(
"Do you want to include DLCs in the report (Y/N)?: ")
if includedlc == "y" or includedlc == "Y":
stringsql = ("SELECT * FROM GameLibrary ORDER By PlatformName1")
cur.execute(stringsql)
elif includedlc == "n" or includedlc == "N":
stringsql = (
"SELECT * FROM GameLibrary WHERE IsDLC = 'n' or IsDLC = 'Y' ORDER BY PlatformName1"
)
cur.execute(stringsql)
else:
print("Unrecognized choice.")
printgamesdb()
tb = from_db_cursor(cur)
tb.field_names = [
"Game ID", "Game Name", "Platform #1", "Platform #2", "Platform #3",
"Platform #4", "Platform #5", "Metascore", "DLC"
]
tb.align["Metascore"] = "r"
print(tb.get_string(title="Your Games Library ordered by Platform"))
_ = input("Press a key to exit")
main()
def AttendanceCheck(uid):
'''
Function to print all attendance entries of a person.
uid is the username of whose attendance is being checked, It must be verified that it exists before this function is run.
Function creates a list of tables who are named as date_DDMMYYYY
Then Queries each of the dates for attendance of uid.
Creates a prettytable table using the data obtained and prints the final attendance.
'''
conn = sqlite3.connect("Data/maindatabase.db")
c = conn.cursor()
datelist = []
count = 0
command = '''SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'date_%';'''
for row in c.execute(command):
datelist.append(str(row[0]))
count += 1
for i in range(count):
print("Attendace of : {}".format(datelist[i][5:]))
command = "SELECT * FROM " + datelist[i] + ''' WHERE ID="{}"'''.format(
uid) + ";"
c.execute(command)
x = from_db_cursor(c)
print(x)
conn.close()
callpause()
def filter_by_day(conn, day):
"""Return all the the tvhsows airing at a given day."""
cur = conn.cursor()
rows = cur.execute("SELECT * FROM tvshows where airing_day = ?", (day,))
tvshows = from_db_cursor(rows)
return tvshows
def psycop_query(self, db_name, db_user, db_passwd, db_host, db_port, db_query):
try:
pg_conn = psycopg2.connect(dbname=db_name, user=db_user, host=db_host, password=db_passwd, port=db_port)
pg_conn.autocommit = True;
cur = pg_conn.cursor()
cur.execute(db_query)
pt = from_db_cursor(cur)
if pt != None:
print(pt)
except psycopg2.Error as e:
print('--------------------')
print('Error:', e)
print('--------------------')
except psycopg2.Warning as w:
print('--------------------')
print('Warning:', w)
print('--------------------')
except psycopg2.InterfaceError as e:
print('--------------------')
print('Error:', e)
print('--------------------')
except psycopg2.DatabaseError as e:
print('--------------------')
print('Error:', e)
print('--------------------')
def listhilight(uid, word=False):
"""
Lists the hilight defined for a gid or all
:param uid: filter to group id
:param word: word to return value for or everything
:return: table with hilight stored
"""
logger = logging.getLogger(__name__)
wordtext = ""
if not word:
sql = "select word from hilight WHERE gid='%s' ORDER BY word ASC;" % uid
else:
string = (word, uid)
sql = "SELECT word FROM hilight WHERE word='%s' AND gid='%s' ORDER by word ASC;" % string
wordtext = _("for word %s for uid %s") % (word, uid)
cur = stampy.stampy.dbsql(sql)
try:
# Get value from SQL query
text = _("Defined hilight triggers %s:\n") % wordtext
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
except:
# Value didn't exist before
text = _("%s has no trigger hilight") % word
logger.debug(msg=_L("Returning hilight %s for word %s") % (text, word))
return text
def display_tvshows(conn):
"""List all the tvshows in the database"""
cur = conn.cursor()
rows = cur.execute("SELECT * FROM tvshows")
tvshows = from_db_cursor(rows)
return tvshows
def show_tables(db_name):
mydb = mysql.connect(host=host, user=user, passwd=passwd, db=db)
cur = mydb.cursor()
cur.execute("SHOW TABLES FROM " + db_name)
ptables = from_db_cursor(cur)
cur.close()
mydb.close()
print ptables
def show_databases():
mydb = mysql.connect(host=host, user=user, passwd=passwd)
cur = mydb.cursor()
cur.execute("show databases")
ptable = from_db_cursor(cur)
cur.close()
mydb.close()
print ptable
def describe_table(table_name):
mydb = mysql.connect(host=host, user=user, passwd=passwd, db=db)
cur = mydb.cursor()
cur.execute("DESCRIBE %s" % table_name)
ptable = from_db_cursor(cur)
cur.close()
mydb.close()
print ptable
def test():
print 'dziala'
conn = sqlite3.connect('db.sqlite')
c = conn.cursor()
c.execute("select * from contacts");
tab = from_db_cursor(c)
t = tab.get_string()
print t.encode('utf-8')
def sql_email(x): conn = pymysql.connect(host= creds[5], port=3306, user= creds[6], passwd= creds[7], db=creds[8]) cur = conn.cursor() cur.execute(x) pt = from_db_cursor(cur) send_mail(pt.get_html_string()) cur.close() conn.close()
def execute_sql(sql,raw=False) :
conn = DatabaseConnection()
cursor = conn.cursor
cursor.execute(sql)
affected_records=cursor.rowcount
if not raw:
pt = from_db_cursor(cursor,print_empty=True)
else:
pt = cursor.fetchall()
return pt,affected_records
def mk_query(i, parameters, prt_gen_all = 0):
"""从模板和参数表生成query，并输出结果
第一步：
定义所有的参数
第二步：
记录所有可能用到的模板
第三部：
执行查询，并按照三种模式中的一种，输出数据
"""
table_name, device_id, DATE, DATE1, DATE2 = (parameters["table_name"],
parameters["device_id"],
parameters["DATE"],
parameters["DATE1"],
parameters["DATE2"])
# 根据device_id获取全部信息
query0 = \
"""
SELECT * FROM %s WHERE device_id = '%s'
""" % (table_name, device_id)
# 查看所有的device_id
query1 = \
"""
SELECT DISTINCT device_id FROM %s
""" % table_name
# 根据device_id查看某一天的power总和
query2 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE, DATE)
# 根据device_id查看某个时间区间的power总和
query3 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE1, DATE2)
c.execute(eval("query%s" % i))
if prt_gen_all == 0: # 打印ascii二维表
print(from_db_cursor(c))
elif prt_gen_all == 1: # 生成器模式生成行
return iterC(c)
elif prt_gen_all == 2: # 返回整个二维表数据
return c.fetchall()
else:
prt_all(c)
def showstats(type=False):
if type:
sql = "select * from stats WHERE type='%s' ORDER BY count DESC" % type
else:
sql = "select * from stats ORDER BY count DESC"
cur.execute(sql)
table = from_db_cursor(cur)
text = "Defined stats:\n"
text = "%s\n```%s```" % (text, table.get_string())
log(facility="stats", verbosity=9,
text="Returning stats %s" % text)
return text
def get_content(backup_dir):
# getting the content from the contacts database
con = lite.connect(backup_dir + '/contacts2.db')
cur = con.cursor()
cur.execute("SELECT contacts._id AS _id,contacts.custom_ringtone AS custom_ringtone, name_raw_contact.display_name_source AS display_name_source, name_raw_contact.display_name AS display_name, name_raw_contact.display_name_alt AS display_name_alt, name_raw_contact.phonetic_name AS phonetic_name, name_raw_contact.phonetic_name_style AS phonetic_name_style, name_raw_contact.sort_key AS sort_key, name_raw_contact.phonebook_label AS phonebook_label, name_raw_contact.phonebook_bucket AS phonebook_bucket, name_raw_contact.sort_key_alt AS sort_key_alt, name_raw_contact.phonebook_label_alt AS phonebook_label_alt, name_raw_contact.phonebook_bucket_alt AS phonebook_bucket_alt, has_phone_number, name_raw_contact_id, lookup, photo_id, photo_file_id, CAST(EXISTS (SELECT _id FROM visible_contacts WHERE contacts._id=visible_contacts._id) AS INTEGER) AS in_visible_group, status_update_id, contacts.contact_last_updated_timestamp, contacts.last_time_contacted AS last_time_contacted, contacts.send_to_voicemail AS send_to_voicemail, contacts.starred AS starred, contacts.pinned AS pinned, contacts.times_contacted AS times_contacted, (CASE WHEN photo_file_id IS NULL THEN (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||contacts._id|| '/photo' END) ELSE 'content://com.android.contacts/display_photo/'||photo_file_id END) AS photo_uri, (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||contacts._id|| '/photo' END) AS photo_thumb_uri, 0 AS is_user_profile FROM contacts JOIN raw_contacts AS name_raw_contact ON(name_raw_contact_id=name_raw_contact._id)")
pt = from_db_cursor(cur)
con.close()
print pt
'''
def example3():
"""打印数据库SELECT的结果
"""
import sqlite3
print("{:=^40}".format("example3"))
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE employee (name TEXT, age INTEGER)")
c.executemany("INSERT INTO employee VALUES (?,?)", [("bob", 32), ("jack", 21)])
c.execute("SELECT * FROM employee")
x = from_db_cursor(c)
print(x)
def setUp(self):
self.conn = sqlite3.connect(":memory:")
self.cur = self.conn.cursor()
self.cur.execute("CREATE TABLE cities (name TEXT, area INTEGER, population INTEGER, rainfall REAL)")
self.cur.execute("INSERT INTO cities VALUES (\"Adelaide\", 1295, 1158259, 600.5)")
self.cur.execute("INSERT INTO cities VALUES (\"Brisbane\", 5905, 1857594, 1146.4)")
self.cur.execute("INSERT INTO cities VALUES (\"Darwin\", 112, 120900, 1714.7)")
self.cur.execute("INSERT INTO cities VALUES (\"Hobart\", 1357, 205556, 619.5)")
self.cur.execute("INSERT INTO cities VALUES (\"Sydney\", 2058, 4336374, 1214.8)")
self.cur.execute("INSERT INTO cities VALUES (\"Melbourne\", 1566, 3806092, 646.9)")
self.cur.execute("INSERT INTO cities VALUES (\"Perth\", 5386, 1554769, 869.4)")
self.cur.execute("SELECT * FROM cities")
self.x = from_db_cursor(self.cur)
def showstats(type=False):
"""
Shows stats for defined type or all if missing
:param type: user or chat or empy for combined
:return: table with the results
"""
logger = logging.getLogger(__name__)
if type:
sql = "select * from stats WHERE type='%s' ORDER BY count DESC" % type
else:
sql = "select * from stats ORDER BY count DESC"
dbsql(sql)
table = from_db_cursor(cur)
text = "Defined stats:\n"
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning stats %s" % text)
return text
def query(self,keyword='',q=''):
funcname = 'db.query'
l = ml.mylogger(logfile,logfilelevel,funcname)
if q in ['title','tag','source','author','mail','time']:
cmd = 'select title,link from bookmark where '+q+' like "%'+keyword+'%" order by title'
elif keyword =='' and q =='':
cmd = 'select title,link from bookmark'
else:
l.error('Missing keyword')
sys.exit()
conn = sqlite3.connect(self.dbfile)
cursor = conn.cursor()
cursor.execute(cmd)
v = from_db_cursor(cursor)
v.align['title']='l'
cursor.close()
conn.close()
return v
def srank(word=False):
"""
Search for rank for word
:param word: word to search in database
:return: table with the values for srank
"""
logger = logging.getLogger(__name__)
if getalias(word):
word = getalias(word)
text = ""
if word is False:
# If no word is provided to srank, call rank instead
text = rank(word)
else:
string = "%" + word + "%"
sql = "SELECT * FROM karma WHERE word LIKE '%s' LIMIT 10;" % string
dbsql(sql)
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning srank for word: %s" % word)
return text
def exec_query(query, bool_return_prettytable=False):
conn = get_mysql_connection()
cur = conn.cursor()
try:
with conn:
cur.execute("USE {0};".format(db_name))
cur.execute(query)
if bool_return_prettytable:
results = from_db_cursor(cur)
else:
results = cur.fetchall()
return results
except Exception as err:
print(str(err))
finally:
conn.commit()
cur.close()
conn.close()
def tableData2Html(tableName):
htmlcode = """<html><head><title>%s</title><link href="/static/style.css" rel="stylesheet"></head>
<body><div style="align:center; font:bold 10pt Verdana; width:100%%;">%s</div>""" % (tableName,tableName)
try:
con = sqlite3.connect(PATH_GRANJA_DB)
con.row_factory = sqlite3.Row
db_cur = con.cursor()
db_cur.execute('SELECT * FROM %s;' % (tableName))
pt = from_db_cursor(db_cur)
con.close()
pt.float_format = 1.3
htmlcode += pt.get_html_string(attributes = {"id": "sort", "class": "sort"})
htmlcode += """<script src='/static/tablesort.min.js'></script><script src='/static/tablesort.number.js'></script><script>new Tablesort(document.getElementById('sort'));</script></body></html>"""
htmlcodemin = htmlmin.minify(htmlcode, remove_empty_space = True)
htmlcodemin = htmlcodemin.replace('<tr><th>', '<thead><tr><th>')
htmlcodemin = htmlcodemin.replace('</th></tr>', '</th></tr></thead>')
htmlcodemin = htmlcodemin.replace('<th>', '<th class="sort-header">')
htmlcodemin = re.sub(r'\bNone\b', '0', htmlcodemin)
except sqlite3.Error, e:
if con:
con.rollback()
logging.error("Error %s:" % e.args[0])
def describe_table(self, table_name):
"""
Use describe_table if, for instance, you forget the names of the columns in your table or what types they have.
Example:
>>> cnx = using_mysql(credentials.DB['username'], credentials.DB['password'], credentials.DB['host'], 'test_customer_DB')
>>> table_definition = ("`user_ID` int(11) NOT NULL AUTO_INCREMENT,"
... "`email` varchar(100) NOT NULL,"
... "`password_hash` varchar(300) NOT NULL,"
... "`status` enum('Active','Disabled','Inactive') DEFAULT 'Active',"
... "PRIMARY KEY (`user_ID`)")
>>> cnx.create_table('users_for_test', table_definition)
Creating table 'users_for_test': Done
>>> cnx.describe_table('users_for_test')
+---------------+--------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------------------------+------+-----+---------+----------------+
| user_ID | int(11) | NO | PRI | None | auto_increment |
| email | varchar(100) | NO | | None | |
| password_hash | varchar(300) | NO | | None | |
| status | enum('Active','Disabled','Inactive') | YES | | Active | |
+---------------+--------------------------------------+------+-----+---------+----------------+
>>> cnx.delete_table('users_for_test', verbose=False)
'users_for_test' has been permanently deleted
"""
assert type(table_name) == str, "table_name is not a string"
self.__open()
try:
self.__session.execute('DESCRIBE {}'.format(table_name))
table = from_db_cursor(self.__session)
print(table)
except mysql.connector.Error as err:
print('ERROR: {}'.format(err.msg))
self.__close()
def rank(word=False):
"""
Outputs rank for word or top 10
:param word: word to return rank for
:return:
"""
logger = logging.getLogger(__name__)
if getalias(word):
word = getalias(word)
if word:
# if word is provided, return the rank value for that word
string = (word,)
sql = "SELECT * FROM karma WHERE word='%s';" % string
dbsql(sql)
value = cur.fetchone()
try:
# Get value from SQL query
value = value[1]
except:
# Value didn't exist before, return 0 value
value = 0
text = "`%s` has `%s` karma points." % (word, value)
else:
# if word is not provided, return top 10 words with top karma
sql = "select * from karma ORDER BY value DESC LIMIT 10;"
text = "Global rankings:\n"
dbsql(sql)
table = from_db_cursor(cur)
text = "%s\n```%s```" % (text, table.get_string())
logger.debug(msg="Returning karma %s for word %s" % (text, word))
return text
def print_query_result(self, MySQL_query):
"""
Example:
>>> cnx = using_mysql(credentials.DB['username'], credentials.DB['password'], credentials.DB['host'], 'test_customer_DB')
>>> table_definition = ("`user_ID` int(11) NOT NULL AUTO_INCREMENT,"
... "`email` varchar(100) NOT NULL,"
... "`password_hash` varchar(300) NOT NULL,"
... "`status` enum('Active','Disabled','Inactive') DEFAULT 'Active',"
... "PRIMARY KEY (`user_ID`)")
>>> cnx.create_table('users_for_test', table_definition)
Creating table 'users_for_test': Done
>>> cnx.insert('users_for_test', email = '*****@*****.**', password_hash = 12345)
>>> cnx.insert('users_for_test', email = '*****@*****.**', password_hash = 12345)
>>> cnx.print_query_result("SELECT * FROM users_for_test")
+---------+----------------+---------------+--------+
| user_ID | email | password_hash | status |
+---------+----------------+---------------+--------+
| 1 | [email protected] | 12345 | Active |
| 2 | [email protected] | 12345 | Active |
+---------+----------------+---------------+--------+
>>> cnx.delete_table('users_for_test', verbose=False)
'users_for_test' has been permanently deleted
"""
assert type(MySQL_query) == str, "MySQL_query is not a string"
self.__open()
try:
self.__session.execute(MySQL_query)
table = from_db_cursor(self.__session)
print(table)
except mysql.connector.Error as err:
print('ERROR: {}'.format(err.msg))
self.__close()
# Good module import
try:
import cx_Oracle
except ImportError, info:
print "Import Error:", info
sys.exit()
if cx_Oracle.version < '3.0':
print "Very old version of cx_Oracle :", cx_Oracle.version
sys.exit()
# Connect to database
try:
db = cx_Oracle.connect (login, password, server + '/' + SID)
cursor = db.cursor()
quere = SQL_Report
# quere = "select to_char(sysdate, 'YYYY') from dual"
cursor.execute(quere)
db.commit()
pt = prettytable.from_db_cursor(cursor)
print pt.get_string()
db.close()
except cx_Oracle.Error, error:
print str(error).decode('utf8')
import mysql.connector
from prettytable import from_db_cursor
cnx = mysql.connector.connect (user="******", password="******", host="127.0.0.1", database="ucb")
cursor = cnx.cursor()
cursor.execute ("show tables")
tb = [i[0] for i in cursor]
with open ("C:\\Users\\Ilay\\Desktop\\test.txt", "w") as writer:
for i in tb:
cursor.execute ("EXPLAIN %s" % i)
writer.write ("Table name: %s\n\n%s\n\n\n\n" % (i, from_db_cursor(cursor).get_string (fields=["Field", "Type", "Key"])))
"""cursor.execute ("EXPLAIN node")
tb = from_db_cursor(cursor)
tb.add_column("Notes", [" " * 10 for i in list(tb)])
print (tb.get_string(fields=["Field", "Type", "Key", "Notes"]))"""
def list_stock (connection) :
cursor = connection.cursor()
cursor.execute("SELECT * FROM trackedStockTable;")
ptCursor = prettytable.from_db_cursor(cursor)
print(ptCursor)
def print_results(cursor_object):
"""Print results from database in nice table"""
table = from_db_cursor(cursor_object)
table.align = 'l'
print table
