Microsoft SQL Server + pymssql

The default SQL port is 1433.

Linux install Microsoft SQL Server and tools here:

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

Connecting to database:

$ sqlcmd -S localhost -U SA -P password

Script location

/opt/mssql-tools/bin/sqlcmd
/opt/mssql-tools/bin/bcp

Some interesting SQL commands:

SELECT GETDATE()
2017-10-21 22:36:52.08

System catalog tables

SYSUSERS -  Database users
SYS.DATABASES  - All database segments
SYS.DATABASE_PERMISSIONS - All database permissions
SYS.DATABASE_FILES - All database files
SYSINDEXES - All indexes
SYSCONSTRAINTS - All constraints
SYS.TABLES - All database tables
SYS.VIEWS - All database views

Query's:

# Show all database
Select * from Sys.Databases

# Delete database from database
DROP DATABASE Database_name;

Python lib to connect to Microsoft SQL Server pymssql.

http://www.pymssql.org/en/stable/pymssql_examples.html

Connecting to database:

import pymssql

server = "localhost"

user = "SA"

password = "password"

conn = pymssql.connect(server, user, password, "TestDB")

cursor = conn.cursor()

Show all tables:

cursor.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")

for row in cursor:

    print("{}".format(row))

Get all table Inventory values:

cursor.execute("SELECT * FROM Inventory")

for row in cursor:

    print("{}".format(row))

conn.close()


Create table:

cursor.execute("""CREATE TABLE books (

               id INT NOT NULL,

               book_name VARCHAR(100),

               year VARCHAR(100),

               PRIMARY KEY(id)

               )

               """)

conn.commit()

Add entry to  to table:

cursor.execute("INSERT INTO books VALUES (1,'Freedom from known', 1955)")

con.commit()