Edward Lance Lorilla

【PYTHON】Sqlite3

Posted: 06 Jun 2021 09:04 AM PDT

import sqlite3

connection = sqlite3.connect('chinook.db')

cursor = connection.cursor()

# list out tables -- .table does not work from Python sqlite3

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

cursor.fetchall()

# get table information (column names, types, settings)

cursor.execute('PRAGMA table_info(artists);')

cursor.fetchall()

# SELECT the first 5 rows of artists

cursor.execute('SELECT * FROM artists LIMIT 5;')

cursor.fetchall()

# especially for longer queries, it helps to format them like this, with each SQL command on a separate line

query = """

SELECT *

FROM artists

LIMIT 5;

"""

cursor.execute(query)

cursor.fetchall()

# get table information (column names, types, settings)

cursor.execute('PRAGMA table_info(invoices);')

cursor.fetchall()

# save table column names in a list

cursor.execute('PRAGMA table_info(invoices);')

results = cursor.fetchall()

column_names = [r[1] for r in results]

column_names

cursor.execute('SELECT * FROM invoices LIMIT 5;')

cursor.fetchall()

# ORDER BY

cursor.execute('SELECT Total, InvoiceDate from invoices ORDER BY Total DESC LIMIT 5;')

cursor.fetchall()

# WHERE statement

cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == "Canada" LIMIT 5;')

cursor.fetchall()

# WHERE using an inserted argument

cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == ? LIMIT 5;', ('Canada',))

cursor.fetchall()

# LIKE command

cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry LIKE "%can%" LIMIT 5;')

cursor.fetchall()

# GROUP BY statement

cursor.execute('SELECT SUM(Total), BillingCountry from invoices GROUP BY BillingCountry ORDER BY SUM(Total) DESC LIMIT 5;')

cursor.fetchall()

# examine column names for invoice_items table

cursor.execute('PRAGMA table_info(invoice_items);')

cursor.fetchall()

# examine a sample of the data

cursor.execute('SELECT * FROM invoice_items LIMIT 5;')

cursor.fetchall()

# aliases can be used to rename columns and tables

# according to some SQL style guides, it's not best practice to alias a table

cursor.execute('SELECT i.TrackID as tid, i.UnitPrice as up FROM invoice_items as i LIMIT 5;')

cursor.fetchall()

# DISTINCT

cursor.execute('SELECT DISTINCT UnitPrice FROM invoice_items;')

cursor.fetchall()

# JOIN

# get tracks that were purchased and combine with the country

query = """

SELECT invoices.BillingCountry, invoice_items.TrackId

FROM invoices

JOIN invoice_items

ON invoices.InvoiceId = invoice_items.InvoiceId

LIMIT 5;

"""

cursor.execute(query)

cursor.fetchall()

# get number of purchased tracks for each track by country, sorted by the top-most purchased

query = """

SELECT invoice_items.TrackId, COUNT(invoice_items.TrackId), invoices.BillingCountry

FROM invoices

JOIN invoice_items

ON invoices.InvoiceId = invoice_items.InvoiceId

GROUP BY invoices.BillingCountry

ORDER BY COUNT(invoice_items.TrackId) DESC

LIMIT 5;

"""

cursor.execute(query)

cursor.fetchall()

# multiple JOINs

query = """

SELECT tracks.Name, COUNT(invoice_items.TrackId), invoices.BillingCountry

FROM invoices

JOIN invoice_items

ON invoices.InvoiceId = invoice_items.InvoiceId

JOIN tracks

ON tracks.TrackId = invoice_items.TrackId

GROUP BY invoices.BillingCountry

ORDER BY COUNT(invoice_items.TrackId) DESC

LIMIT 5;

"""

cursor.execute(query)

cursor.fetchall()

# this same command as above can also be done with a subquery like this, but is easier with multiple joins

query = """

SELECT tracks.Name, invoice_merged.track_count, invoice_merged.BillingCountry

FROM

(SELECT ii.TrackId, COUNT(ii.TrackId) as track_count, i.BillingCountry

FROM invoices as i

JOIN invoice_items as ii

ON i.InvoiceId = ii.InvoiceId

GROUP BY BillingCountry) as invoice_merged

JOIN tracks

ON tracks.TrackId = invoice_merged.TrackId

ORDER BY track_count DESC

LIMIT 5;

"""

cursor.execute(query)

cursor.fetchall()

# be sure to close the connection when done

connection.close()

【GAMEMAKER】Text Display

Posted: 06 Jun 2021 09:02 AM PDT

Information about object: objTalk

Sprite: sprController
Solid: false
Visible: true
Depth: 0
Persistent: false
Parent:
Children:
Mask:

No Physics Object

Create Event:

execute code:

ShowText=0;
for (i=0; i<15; i+=1) {
  line[i]="";
}

Keyboard Event for <any key> Key:

set variable ShowText to 0

Draw Event:

execute code:

if ShowText=0 then exit;

pen_color=c_black;
pen_size=5;
brush_color=c_blue;
font_name="Comic Sans MS";
font_color=c_red;
font_size=12;

sx=screen_width/4;
ex=sx*3;
sy=screen_height/4;
ey=sy*3;

draw_rectangle(sx,sy,ex,ey);
for (i=0; i<15; i+=1) {
  if line[i]!="" {
    draw_text(sx+5,sy+(i*15),line[i]);
  }
}
Information about object: objAddText
Sprite: sprController
Solid: false
Visible: true
Depth: 
0
Persistent: false
Parent: 
Children: 
Mask: 

No Physics Object

Create Event:
execute code:

objTalk.line[0]="This is a test, line 0";
objTalk.line[1]="This is a test, line 1";
objTalk.line[2]="This is a test, line 2";
objTalk.line[3]="This is a test, line 3";
objTalk.line[4]="This is a test, line 4";
objTalk.line[5]="This is a test, line 5";
objTalk.line[6]="This is a test, line 6";
objTalk.line[7]="This is a test, line 7";
objTalk.line[8]="This is a test, line 8";
objTalk.line[9]="This is a test, line 9";
objTalk.line[10]="This is a test, line 10";
objTalk.line[11]="This is a test, line 11";
objTalk.line[12]="This is a test, line 12";
objTalk.line[13]="This is a test, line 13";
objTalk.line[14]="This is a test, line 14";
Mouse Event for Left Button:
execute code:

if objTalk.ShowText=0 {
  objTalk.ShowText=1;
} else {
  objTalk.ShowText=0;
}
io_clear();

e-techbytes

Monday, June 7, 2021

Edward Lance Lorilla

Edward Lance Lorilla

No comments:

Post a Comment