Python 3home |
A relational database stores data in tabular form (columns and rows)
A database table is a tabular structure (like CSV) stored in binary form which can only be displayed through a database client or programmatically through a database driver (i.e., to allow a language like Python to read and write to it). The database client program is provided by the database and allows command-prompt access to the database. Every database provides its own client program. A database driver is a module that provides programmatic access to the database. Python has a full suite of drivers for most major databases (mariadb, oracle, postrgres, etc.) Common Databases SQLite (the system we will use in this course) is a file-based RDBMS that is extremely lightweight and requires no installation. It also supports many SQL statements although its data types are more limited. It is claimed to be the most-used database on the planet because it can work in very small environments (for example "internet of things" devices.) It is also ideal for learning. mariadb (formerly known as mysql, can be colloquially referred to as either although mysql is now owned by Oracle) is a production-quality RDBMS that is free and fully embraced by the industry. It supports most SQL statements and is very similar to Oracle, postgres, SQL Server, etc. postgres is another free and full-featured database system similar to mysql. Oracle, Microsoft SQL Server and IBM DB2 are commercially sold databases. mongodb and Amazon DynamoDB are nosql databases, which use other schemes for storing and retrieving information such as key-value stores and document stores. It has advantages (such as distributability among servers) and disadvantages (such as instant data integrity) when compared to RDBMS.
Most databases come with a database client for issuing SQL queries from a command prompt, and each has its own commands and syntax.
We start the sqlite3 client from a Command Prompt or Terminal.
Open a new database archive
sqlite3 new.db
If the file does not exist, this creates a brand new SQLite database file with the name new.db. If file does exist, opens the file.
Open an existing database archive:
sqlite3 session_2.db
(assumes that this file exists and is in current directory - if not, a new file is created)
Open an "in-memory" database (which can be saved later)
sqlite3
Please note that the syntax for opening a new file and opening an existing file are the same! This means that if you intend to open a new file but misspell the name, SQLite3 will simply create a new file; you'll then be confused to see that the file you thought you opened has nothing in it!
At the start of your session, issue the following two commands -- these will format your sqlite3 output so it is clearer, and add column headers.
sqlite> .mode column sqlite> .headers on sqlite> SELECT * FROM students; company state cost ---------- ---------- ---------- Haddad's PA 239.5 Westfield NJ 53.9 The Store NJ 211.5 Hipster's NY 11.98 Dothraki F NY 5.98 Awful's PA 23.95 The Clothi NY 115.2
You may note that columns are 10 characters wide and that longer values are cut off. You can set the width with values for each column for example .width 5 13 11 5 5 for the above table. Unfortunately this must be done separately for each table.
sqlite3 client commands start with a dot; SQL queries end with a semicolon.
Show tables in this database:
sqlite> .tables ad_buys revenue students ad_companies student_status user companyrev student_status_orig user_classes sqlite>
Special note: if you don't see any tables, then you have created a new database. If you meant to open an existing database, then you may have misspelled the name or are currently in a different directory than the database file.
Show structure of a selected database:
sqlite> .schema revenue CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);
The available database column types are defined by the specific database. Most are very similar, with small variations between databases. sqlite data types include INTEGER, REAL (for floating-point values), TEXT (for string data), and BLOB (for other data)
View contents of a table:
sqlite> SELECT * FROM revenue; company state cost ---------- ---------- ---------- Haddad's PA 239.5 Westfield NJ 53.9 The Store NJ 211.5 Hipster's NY 11.98 Dothraki F NY 5.98 Awful's PA 23.95 The Clothi NY 115.2 sqlite>
Note that SQL queries end in a semicolon. If you see a continuation line indicator ( ...> ) this means that the client is waiting for more SQL -- queries can be expressed in more than one line. If you're done, simply type the semicolon and hit [Enter].
Table columns specify a data type.
Again, to view the structure of a table:
sqlite> .schema students CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);
.schema shows us the statement used to create this table. (In other databases, the DESC [tablename] statement shows table columns and types.) As you can see, each column is paired with a column type, which describes what kind of data can be stored in that column. To create a new table, we must specify a name and a type for each column.
The CREATE TABLE statement syntax is the same that you see from the .schema command:
CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);
SQLite and mysql Column Types
value | sqlite3 | mysql, et al. |
whole number | INTEGER | all int types (TINYINT, BIGINT, INT, etc.) |
floating-point number | REAL | FLOAT, DOUBLE, REAL, etc. |
string data | TEXT | CHAR, VARCHAR, etc. |
non-typed data, including binary (image, etc.) | BLOB | BLOB |
Note that sqlite3 datatypes are nonstandard and don't exactly match types found in databases such as mysql/MariaDB, Oracle, etc.:
This slide can serve as a reference for SQL commands needed for this session.
list common commands sqlite> .help |
show name and filename of open database sqlite> .databases |
save result of next command in excel (defaults to .csv format) sqlite> .excel |
exit sqlite3 sqlite> .exit |
output result of subsequent queries to a file sqlite> .output <filename> |
save current database to a new file sqlite> .save <filename> |
run an OS (command line) command sqlite> .system <command> |
connect to a database (open a file) sqlite> .open sqlite3_trades.db |
show tables in the database sqlite> .tables |
describe a table sqlite> .schema stocks |
select specified columns from the table sqlite> SELECT date, trans_type, symbol, qty FROM stocks; |
select ALL columns from the table sqlite> SELECT * FROM stocks; |
select only rows WHERE a value is found sqlite> SELECT date, symbol, qty FROM stocks WHERE trans_type = 'BUY'; |
INSERT INTO: insert a row sqlite> INSERT INTO stocks (date, trans_type, symbol, qty) VALUES ('2015-01-01', 'BUY', 'MSFT', 10); |
connect to database from Python import sqlite3 conn = sqlite3.connect('sqlite3_trades.db') c = conn.cursor() |
execute a SELECT query from Python c.execute('SELECT date, symbol, qty FROM stocks WHERE trans_type = 'BUY') |
retrieve one row or many rows from a result set c.execute('SELECT * FROM stocks ORDER BY price') tuple_row = c.fetchone() # tuple row tuple_row = c.fetchmany(3) # list of tuple rows - specify # of rows tuple_rows = c.fetchall() # list of tuple rows - entire result set |
iterate over result set c.execute('SELECT * FROM stocks ORDER BY price') for tuple_row in c: print(tuple_row) |
insert a row c.execute("INSERT INTO stocks (date, trans_type, symbol, qty) VALUES ('2015-01-01', 'BUY', 'MSFT', 10)") conn.commit() # all changes to db must be committed |
These commands are similar to those in SQLite3.
start mysql client (command-line utility) $ mysql |
show available databases Maria DB []> show databases; |
select a database to use (mysql remembers this selection) Maria DB []> use trades; |
show tables within a selected database Maria DB [trades]> show tables; |
show a table's structure Maria DB [trades]> desc stocks; A mysql database table description looks like this: +-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | date | int(8) | YES | | NULL | | | mktrf | float | YES | | NULL | | | hml | float | YES | | NULL | | | smb | float | YES | | NULL | | | rf | float | YES | | NULL | | +-------+--------+------+-----+---------+-------+ "Field" is the column name. "Type" specifies the required data type for that column. |
select rows and show specific columns Maria DB [trades]> SELECT date, trans_type, symbol, qty FROM stocks; |
select rows and show all columns Maria DB [trades]> SELECT * FROM stocks; |
select rows that meet a condition Maria DB [trades]> SELECT date, symbol, qty FROM stocks WHERE trans_type = 'BUY'; |
add a row to a table Maria DB [trades]> INSERT INTO stocks (date, trans_type, symbol, qty) VALUES ('2015-01-01', 'BUY', 'MSFT', 10); |
connect to mysql from Python (sample connect information) import pymysql
host = 'localhost'
database = 'test'
port = 3306
username = 'ta'
password = 'pepper'
conn = pymysql.connect(host=host,
port=port,
user=username,
passwd=password,
db=database)
cur = conn.cursor()
|
select rows that meet a condition cur.execute("SELECT date, symbol, qty
FROM stocks
WHERE trans_type = 'BUY'")
|
select rows and order by column value; retrieve one or many rows from result set c.execute('SELECT * FROM stocks
ORDER BY price')
tuple_row = c.fetchone() # tuple row
tuple_rows = c.fetchmany() # list of tuple rows
|
iterate over rows in result set for tuple_row in c:
print(tuple_row)
|
insert a table row c.execute("INSERT INTO stocks
(date, trans_type, symbol, qty)
VALUES ('2015-01-01', 'BUY', 'MSFT', 10)")
conn.commit() # all changes to db must be committed
|
Databases are cool. Just saying.