Coding on a Chromebook – Python and SQL Server Databases

This post will show you how to access SQL Server databases using Python on a Chromebook. If you want to check out a video version I created of this post check it out here.

Prerequisites

There are some things you need setup before you continue. Check out the below videos I created to see which steps you need and how to do them.

Install Linux on your Chromebook.

Install Visual Studio Code in Linux

Access Databases using DBeaver in Chrome OS. This is not required but is very helpful.

Make sure you have your SQL Server credentials ready.

Installing SQL Server Driver

Check the version of Debian you have installed on your Chromebook. Type the below command in a terminal window:

cat /etc/*release

Open a web browser and go to this link:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

Scroll down until you reach this section and then click on the Debian link

You will then be taken to a list of debian commands, like below

Copy and paste all the commands onto a terminal window, but there are three commands labeled Debian 8, Debian 9 and Debian 10. Copy and paste only the version for your OS. For example I have Debian 10, so I’ll copy and past the Debian 10 command

Install Python pyodbc package

Open up a terminal and type

sudo -H pip3 install pyodbc

Code for accessing SQL Servers

Open up visual studio code and create a python file

Start by typing the below for creating the connection to the SQL Server:

import pyodbc

server = 'tcp:IP_ADDRESS'
database = 'DATABASE_NAME'
username = 'USER_NAME'
password = 'PASSWORD'


#Create connection to the database
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()

Replace IP_ADDRESS with the ip address or hostname of your SQL Server. Replace DATABASE_NAME with the name of the database you have access to on the SQL Server. Replace USER_NAME with your username. And replace PASSWORDwith your password.

Now to perform different database functions write the below code after the previous

To run a SELECT query

#Run a SELECT SQL query
cursor.execute("SELECT  * FROM SYSOBJECTS WHERE xtype = 'U';")
row = cursor.fetchone()

#Iterate through results
while row:
    print(row[0])
    row = cursor.fetchone()

#Run a SELECT SQL query
cursor.execute("SELECT  * FROM SYSOBJECTS WHERE xtype = 'U';")
row = cursor.fetchone()

#Iterate through results
while row:
    print(row[0])
    row = cursor.fetchone()

Create table query

#Create a new table
count = cursor.execute("CREATE TABLE Guitars ( " +
                        "GuitarID              INT           NOT NULL    IDENTITY    PRIMARY KEY, " +
                        "Name           VARCHAR(100)  NOT NULL, " +
                        "Manufacturer  VARCHAR(100), " +
                        ");").rowcount
conn.commit()

#Create a new table
count = cursor.execute("CREATE TABLE Guitars ( " +
                        "GuitarID              INT           NOT NULL    IDENTITY    PRIMARY KEY, " +
                        "Name           VARCHAR(100)  NOT NULL, " +
                        "Manufacturer  VARCHAR(100), " +
                        ");").rowcount
conn.commit()

Execute an INSERT query

#Run an INSERT query
count = cursor.execute("INSERT INTO Guitars (Name, Manufacturer) VALUES ('Les Paul', 'Fender')").rowcount
conn.commit()
print('Rows inserted: ' + str(count))

Execute an UPDATE query

#Run an UPDATE query
count = cursor.execute("UPDATE Guitars SET Manufacturer='Gibson' WHERE GuitarID=2").rowcount
conn.commit()
print('Rows update: ' + str(count))

Execute a DELETE query

#Run a DELETE query
count = cursor.execute("DELETE FROM Guitars WHERE GuitarID=2").rowcount
conn.commit()
print('Rows deleted: ' + str(count))

So with this here, you have what you need to run queries on SQL Server databases through Python.