Database

Here are two types of database technologies:

Networked Database Systems: These are database management systems (DBMS) designed to operate in networked environments. They use network protocols to establish connections and facilitate communication between clients and the database server. Examples of networked database systems include PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, and MongoDB (which can also operate in a networked mode).
Embedded or Local Database Systems: These database systems are designed to be embedded within applications or operate locally on a single machine without requiring network connectivity. They are often lightweight and optimized for local storage and processing. Examples of embedded or local database systems include SQLite, H2 Database, Berkeley DB, and LevelDB.

When you are building a service, it always uses network protocol as a connection. Even though a SQLite database is stored as a file on disk and doesn’t involve network protocols, the term “connection” is still used in the context of interacting with the database. In the case of SQLite, a “connection” refers to the interface or handle that allows you to establish a link to the database file, enabling you to perform various operations like executing SQL statements, retrieving data, and modifying the database contents.

For Networked databases, like PostgreSQL, it uses TCP/IP and the hostname is the IP address or DNS address (which resolves to one or more IP addresses).

Having a VPN (Virtual Private Network) should not directly affect your ability to access localhost.

The string: localhost in the browser http://localhost is interepreted as http://127.0.0.1:80. The default http port is 80, an alternative http port is 4000. It needs to be http, not https because it is not encrpyted locally. When you enter http://localhost:5050 in the browser’s address bar, it represents a request to access a web resource hosted on the local machine using the HTTP protocol and specifically targeting port number 5050. IPv6 version of localhost is [::1]

The version of the CLI client (psql) does not need to match the exact version of the PostgreSQL server. As long as the client version is not older than the server version, you should be able to connect and work with the database.

You can access a PostgreSQL (psql) database using SQL Workbench/J, which is a popular, free, and cross-platform SQL client tool. SQL Workbench/J supports various database systems, including PostgreSQL.

Get in to a docker DB running postgres docker exec -it containername psql -U username -d dbname

or from mysql if you have it installed: mysql -u username -p psql -u username -p

Port name define the DB connection. Port: \conninfo

Host:

SELECT boot_val,reset_val
FROM pg_settings
WHERE name='listen_addresses';;

Typically the host name is localhost

Current user: SELECT current_user;

On the Pgadmin website go to properties

Get into PSQL DB from python in SQLAlchemy

engine = create_engine('postgresql://username:password@localhost/mydatabase')

From db engine object in SQLAlchemy:

engine = db.get_bind()
engine.url.port
engine.url.host
engine.url.username
engine.url.password

In the python object, when the port is None, it typically indicates that the default port for the specific database system is being used.

Get the drivername with :engine.drivername

When you are making changes with the db object. They are not transmitted to the main db until you run db.commit(). So you can have a different instance to that in the cloud if you re-run operations locally without undoing changes

Different database systems have default ports for establishing connections. For example:

PostgreSQL default port: 5432
MySQL default port: 3306
SQL Server default port: 1433
Oracle default port: 1521

From PSQL CLI - Always add a semi-colon!!!!!

If you create a local instance using the psql windows installer, when you run psql the first time you will get an error because it is trying to log you in with the windows username. You need to specify postgres the root user. As there are no accounts yet.

psql -U postgres

See accounts with:

SELECT usename FROM pg_user;

Toggle on the row-wise view \x

SELECT * FROM codetype;

From python:

Type netstat -a to see all the TCP UDP connections:

The left most is the address of your device on your local network, not the address of the edge router on the global internet.

In a typical home network, the router usually assigns IP addresses in the range of 192.168.1.0 to 192.168.1.255 to devices on the network.

the IPv4 0.0.0.0 and IPv6 [::] means the local device - I.e. it doesn’t have an ip address, it is receiving information .

To see which IP you are broadcasting on: do

\x
SELECT * FROM pg_stat_activity;

If the PostgreSQL server is running inside a Docker container, you’ll need to connect to it using the Docker networking features. Here’s a step-by-step approach to connecting to a PostgreSQL container:

docker ps

When you make a change to the source code of itself in a running docker container it saves those changes to the original files the moment the file is saved. That happens visa versa, change in windows, or change in the docker container, unless you have unsaved changes when you save the other file. Make sure you only edit the container version while it is running, otherwise the changes won’t carry back.

####

Using PGAdmin

Once you have installed psql you can use PGadmin, a GUI to manage everything. You need a separate PGADmin account password too. pgAdmin does not have a separate username for logging into the application itself. Instead, it relies on the operating system’s user account for authentication and a root password created at install and you can reset it at login or resintall.

PGAdmin also has a CLI but it is not installed by default.

There is a hierarchy in DB management tools:

PGAdmin
- PSQL Server instance (you ‘register’ a new server)
  - PSQL Database (you “create” a database)
    - Table
      - Metadata
      - Columns and Rows

When you create a new server, you must supply:

Server name - anything you want
DNS Host name or IP Address - ‘localhost’ or 127.0.0.1
A port number (5432 is default psql port)
A maintenance DB name - typically ‘postgres’
A maintenance DB username - also ‘typically ‘postgres’
A maintenance DB password - i.e. ‘password’

When creating a server in PostgreSQL, the “maintenance database” refers to the initial database that is created specifically for administrative tasks and managing the PostgreSQL server itself. It is also known as the “template1” database.

The loopback interface, commonly referred to as localhost or 127.0.0.1, allows network software to communicate within the same machine without going through any physical network interface. It enables a client application, such as pgAdmin, to establish a connection to the local PostgreSQL server without the need to send the network traffic externally.

When you connect to the localhost database in pgAdmin, the communication occurs internally within the machine. The network packets are routed internally through the loopback interface, allowing the client application and the PostgreSQL server to interact directly without involving external network interfaces.

The IP address is typically associated with the server machine or the network interface on which the PostgreSQL server is running. It is not directly tied to individual databases within the PostgreSQL server. If you install more than one Postgres cluster on a machine, as happens when testing multiple versions of Postgres, then you must change the port number. Each cluster must be listening on its own port number. Here are some guidelines to help you pick a suitable port number: Unix-oriented OSes restrict access to ports under 1024.

Reserved Port Ranges: The Internet Assigned Numbers Authority (IANA) designates specific port ranges for different purposes. It is recommended to choose a port number outside the well-known port range (0-1023) and the registered port range (1024-49151) to avoid conflicts with standardized services. The dynamic port range (49152-65535) is typically used for ephemeral ports.
Avoid Commonly Used Ports: Some port numbers are commonly associated with specific services. To prevent conflicts with widely-used services, it is advisable to avoid well-known port numbers such as 80 (HTTP), 443 (HTTPS), 22 (SSH), and 5432 (default PostgreSQL port). However, note that if you choose a non-standard port, you need to ensure that any firewalls or security groups allow inbound connections to that port.

Load data into PSQL

Either from powershell:

createdb mydatabase

From within PSQL:

CREATE DATABASE dvdrental;

pg_restore -U postgres -d dvdrental "C:\Users\SebastianBec_mt6kag0\Downloads\dvdrental\dvdrental.tar"

Connect with Excel

https://www.npgsql.org/doc/installation.html

Doesn’t import the the column relationships

Need to install https://github.com/npgsql/npgsql/releases/tag/v4.0.13 And install the NGPSQL installation GAC connector part.

PowerBI

Import - local DirectQuery - via internet

Full Hierarchy - adds all the metadata and non-tabular permissions data

Connect to cd

SQLite sqlite3 …db mysql -u … -p -h hostname … psql -U … -h hostname -d … sqlcmd -U … -P … -S … -d …

The default values for each are as follows:

1. SQLite `sqlite3`:

Username: N/A
Password: N/A
Hostname: N/A (SQLite is a file-based database)
Servername: N/A
Database Name: Depends on the filename you specify

SQLite doesn’t have a concept of users and passwords in the same way other RDBMSs do. You just connect directly to a database file.

2. MySQL `mysql`:

Username: root
Password: Often empty by default, but this has changed in more recent installations where during the setup, you’re prompted to create a password for the root user.
Hostname: localhost (when running on the same machine)
Servername: N/A
Database Name: You can specify upon connection or use the USE database_name; command after connecting.

3. PostgreSQL (`psql`):

Username: postgres (common default superuser name)
Password: Depends on the installation; you might be prompted during setup. In some installations, it might initially be empty.
Hostname: localhost (when running on the same machine)
Servername: N/A
Database Name: postgres (default database, often used for initial connection)

4. Microsoft SQL Server `sqlcmd`:

Username: sa (system administrator account)
Password: Depends on the installation. During setup, you can choose between “Windows Authentication” and “SQL Server Authentication.” If you choose SQL Server Authentication, you’ll set a password for the sa account.
Hostname: Typically the name of the machine, but it can also be localhost for local installations.
Servername: The name of the SQL Server instance. A default installation typically uses the machine name, but named instances will have a format like MACHINENAME\INSTANCENAME.
Database Name: master (default system database)

Comparison of commands

sqlite3 mysql psql sqlcmd

Connecting to a Database	`sqlite3 database_name.db`	`mysql -u username -p -h hostname database_name`	`psql -U username -h hostname -d database_name`	`sqlcmd -U username -P password -S server_name -d database_name`
Listing Databases	Only 1 in the file	SHOW DATABASES;	`\l` or `\list`	`SELECT name FROM sys.databases;`
Switching Databases	Have to make a new file	USE database_name;	\c database_name	You typically reconnect to change the database.
Listing Tables	`.tables`	`SHOW TABLES;`	`\dt`	`SELECT name FROM sys.tables;`
Displaying Table Schema	`.schema table_name;`	`DESCRIBE table_name;`	`\d table_name`	`EXEC sp_help table_name;`
Exiting the Interface	`.exit`	`EXIT;`	`\q`	ctrl+c
Clearing Screen:	`.shell clear`	`\c`	`\! clear`	create a new window

Importing a csv

sqlite3

If you want the table to have primary keys, first you have to predefine the structure of the table in a schema:

When you do this:

Order Matters: When you’re importing data from a CSV into SQLite using the .import command, the order of columns in your CSV should match the order of columns in the table definition. SQLite will attempt to insert the data based on the column order.
Column Names Don’t Need to Match: The names of the columns in the CSV file don’t matter for the import process as long as the order is correct. If your CSV has headers, SQLite will treat the header row as data unless you skip it. Typically, users exclude headers or ensure that the table is defined to handle them.

.mode csv
.import /path/to/csvfile.csv tablename

Differences of the sqlite3 Software compared with the other server based databases

SQLite is a unique database system with a specific design philosophy and set of use-cases in mind. The particularities of SQLite arise from these design choices:

Embedded Database: Unlike most other SQL databases, SQLite is not a separate process that applications communicate with. Instead, it’s a library that applications link against, making SQLite an embedded database. This allows for direct function calls, eliminating the inter-process communication overhead.
Portability: SQLite is designed to be cross-platform without any configuration. Database files created on one machine can be copied to and used on a different machine, even if the two machines are running different operating systems.
Simplicity and Reliability: SQLite focuses on being simple and reliable. Features that could compromise reliability are often left out. Its slogan is “Small. Fast. Reliable. Choose any three.” Limitations on altering tables stem from this philosophy; the simpler the ALTER TABLE logic, the fewer chances there are for bugs or corrupted databases.
File-based: Each SQLite database is stored in a single file on disk. This design choice allows for easy backups, copies, and transfers of databases.
Serverless: There’s no separate server process to install, setup, configure, initialize, manage, or troubleshoot. Applications directly read and write from the database files.
Zero Configuration: SQLite doesn’t require configuration, tuning, or administration. It aims to be zero-maintenance.
Transactional: SQLite supports atomic commit and rollback, ensuring database integrity.

Given its unique nature, there are some aspects of SQLite that differ from other relational databases:

Dynamic Typing: Unlike most SQL databases that are statically typed, SQLite uses dynamic typing. You can store any type of data in any column, regardless of the column’s declared data type.
Limited ALTER TABLE: As discussed, SQLite’s support for modifying tables is limited, requiring workarounds for many common alterations.
Concurrency: SQLite uses file-level locking, which is more restrictive than the row-level or page-level locking found in larger databases. Write transactions lock the entire database. This limitation makes SQLite less suitable for scenarios with high concurrent write operations.
No RIGHT JOIN or FULL OUTER JOIN: SQLite doesn’t support these JOIN operations. However, LEFT JOIN is supported, and you can often restructure queries to achieve similar results without RIGHT JOIN or FULL OUTER JOIN.
No Stored Procedures or Triggers: SQLite’s support for these features is limited compared to other systems like SQL Server or PostgreSQL.
Size Limitation: SQLite is optimized for small to medium-sized databases. While it supports databases up to 140 terabytes, it’s best suited for databases under a few gigabytes in size.

In conclusion, while SQLite has limitations compared to more extensive RDBMS systems, its simplicity, reliability, and portability make it an excellent choice for many applications, especially mobile and desktop apps, embedded systems, prototyping, and small web applications. Always evaluate your specific needs to determine if SQLite is the right choice for your project.

SQLite only has four primitive data types: INTEGER, REAL, TEXT, and BLOB. APIs that return database values as an object will only ever return one of these four types.

In practice, VARCHAR(N) in SQLite is similar to TEXT. The length N in VARCHAR(N) does not enforce a limit on the number of characters in the column; instead, it’s an indication of the expected maximum.

Information schema = `sqlite_master` in sqlite3

SQLite does not have the information_schema database that you typically find in other relational databases like PostgreSQL, MySQL, or SQL Server.

Instead, to get a list of all tables in SQLite, you’d query the sqlite_master table:

PRAGMA table_info(your_table_name);

SELECT name FROM sqlite_master WHERE type='table';

This query will return the names of all tables in the current SQLite database.

When you import from csv, you have to procreate the entire schema, and you have to delete the first two rows.

.dump is a special command for SQLite that outputs the SQL statements that would be needed to recreate the entire database (or a specific table if a table name is provided).

.tables to get a string of all the tables.

If you want to filter the list of tables, you need to use the select * from sqlite_master where type='table' ...; command

In sqlite3, to better visually format the output of the data, run:

.headers on
.mode column

sqlite3 Reserved words

https://raw.githubusercontent.com/AnanthaRajuC/Reserved-Key-Words-list-of-various-programming-languages/master/language-files/SQLite%20Reserved%20Keywords.md

  - ABORT
  - ACTION
  - ADD
  - AFTER
  - ALL
  - ALTER
  - ANALYZE
  - AND
  - AS
  - ASC
  - ATTACH
  - AUTOINCREMENT
  - BEFORE
  - BEGIN
  - BETWEEN
  - BY
  - CASCADE
  - CASE
  - CAST
  - CHECK
  - COLLATE
  - COLUMN
  - COMMIT
  - CONFLICT
  - CONSTRAINT
  - CREATE
  - CROSS
  - CURRENT_DATE
  - CURRENT_TIME
  - CURRENT_TIMESTAMP
  - DATABASE
  - DEFAULT
  - DEFERRABLE
  - DEFERRED
  - DELETE
  - DESC
  - DETACH
  - DISTINCT
  - DROP
  - EACH
  - ELSE
  - END
  - ESCAPE
  - EXCEPT
  - EXCLUSIVE
  - EXISTS
  - EXPLAIN
  - FAIL
  - FOR
  - FOREIGN
  - FROM
  - FULL
  - GLOB
  - GROUP
  - HAVING
  - IF
  - IGNORE
  - IMMEDIATE
  - IN
  - INDEX
  - INDEXED
  - INITIALLY
  - INNER
  - INSERT
  - INSTEAD
  - INTERSECT
  - INTO
  - IS
  - ISNULL
  - JOIN
  - KEY
  - LEFT
  - LIKE
  - LIMIT
  - MATCH
  - NATURAL
  - NO
  - NOT
  - NOTNULL
  - NULL
  - OF
  - OFFSET
  - ON
  - OR
  - ORDER
  - OUTER
  - PLAN
  - PRAGMA
  - PRIMARY
  - QUERY
  - RAISE
  - RECURSIVE
  - REFERENCES
  - REGEXP
  - REINDEX
  - RELEASE
  - RENAME
  - REPLACE
  - RESTRICT
  - RIGHT
  - ROLLBACK
  - ROW
  - SAVEPOINT
  - SELECT
  - SET
  - TABLE
  - TEMP
  - TEMPORARY
  - THEN
  - TO
  - TRANSACTION
  - TRIGGER
  - UNION
  - UNIQUE
  - UPDATE
  - USING
  - VACUUM
  - VALUES
  - VIEW
  - VIRTUAL
  - WHEN
  - WHERE
  - WITH
  - WITHOUT