CRUD (Create, Read, Update, and Delete) it is the cornerstone of working with database applications. Today I will walk through using the Streamlit python framework to build a web based app that you can use to interact with a database. The app will allow you to Read, Update, and Delete tables in your database.
The creators of Streamlit framework have taken care of a large amount of the burden of creating a web based app. However not all of the widgets are feature complete. Streamlit is an asynchronous framework allowing 100s or 1000s of users to use your app. With CRUD operations that is great for the Create and Read, but for Update and Delete, not so much.
Today’s tutorial will show you how to overcome those limitations and hopefully inspire you on how you could use Streamlit for your own apps.
All of the code is available on my Github page, please use responsibly.
NOTE: The code presented is not production ready; i.e. no Exception blocks but it wouldn’t take much more effort to adapt the code to be production ready. Instead we will concentrate on the important parts and get you downloading files quickly. Let’s start the tutorial below.
sFTP is still used on a daily basis. Many times the simpler well proven technologies just do the job, just like a hammer. Today’s post will show how to use Python to connect to an sFTP site and securely upload or download your files.
Many times on stackoverflow you tend to get incomplete or partial samples of code that don’t explain step by step what you need to do. And with sFTP every step is critical. The code presented is not production ready; i.e. no Exception blocks but it wouldn’t take much more effort to adapt the code to be production ready. Instead we will concentrate on the important parts and get you downloading files quickly. Let’s go!
# -- LOCAL FILES -------------------------------------------------------------
fromsrc.file_upload importFileUpload
defmain(argv)->None:
file_list=['file_one.txt','file_two.txt']
# upload files to sftp
iflen(file_list)>0:
fp=FileUpload()
fp.send_files(file_list=file_list)
# Start the program
if__name__=='__main__':
main(sys.argv[1:])
Python
17
18
19
# Start the program
if__name__=='__main__':
main(sys.argv[1:])
The file is using the sys.argv[1:] to allow you to pass in arguments when you are running the program. In an effort to keep focus, we won’t being using arguments, and will just hard code the file names in the main function.
Now let’s go line by line.
Python
5
6
fromsrc.file_upload importFileUpload
This is the FileUpload class we will create later.
Python
7
8
9
10
11
12
13
14
15
16
17
18
19
defmain(argv)->None:
file_list=['file_one.txt','file_two.txt']
# upload files to sftp
iflen(file_list)>0:
fp=FileUpload()
fp.send_files(file_list=file_list)
# Start the program
if__name__=='__main__':
main(sys.argv[1:])
The main function, pretty simple isn’t it?
file_list is a list of files we will upload to the sFTP server. Conversely you could have a list of files you wish to download.
Next we check if the file_list has any values in it; in case the list is being passed in via sys.argv.
Next we assign fp to the FileUpload class
Last step is we send the files, by calling the send_files method in the class.
Configuration File
In the configuration directory we have three files.
The configuration file is in plain text, the values are pretty self-explanatory. We will talk about the KEY_FILENAME and KNOWN_HOSTS later on.
Make sure to specify your details, including HOSTNAME, USERNAME, LOCAL_DIRECTORY, REMOTE_DIRECTORY, etc…
config
The init file will be used to load the .yaml configuration file. It is handy way of being able to store the sFTP configuration parameters without having to change the main parts of the code.
Python
1
2
importpathlib,sys
frombox importBox
We need three libraries the standard pathlib and sys, Additionally I use python-box to help referencing values in the configuration. Python-box has a dependency on yaml parser. I use ruamel, finding it easier and quicker.
If not already installed, from your command line you can use pip to install them.
Now we will open the file, and convert the .yaml file into a Box object to be used by the FileUpload class later on.
Python
1
sftp_config:Box=load_config("sftp_config.yaml")
And last step, we call the load_config function.
File: file_upload.py
The FileUpload class will be used to connect to the sFTP server and than upload files. Additional methods could be added to download files, I will leave that exercise to you.
I am going to use the paramiko package. Again if not installed, from the command line use pip to install.
raiseException(f"Known_Hosts file can't be found: {self._known_hosts_file_path}")
Now we will read the contents of the known_hosts file. If the file doesn’t exist, we will raise an Exception.
__connect_client
When we connect to the ssh server, we will need a key. The user private key should be stored locally on your server. The key needs to be in the .pem file format. The private key should be created on the ssh_server or ask the ssh server’s admin to send one to you.
Create User private key in pem format
ZSH
1
2
$ssh-keygen-b4096
$cat.ssh/id_rsa.pub>>.ssh/authorized_keys
Then copy the .pem file to your local machine via FileZilla or another sftp tool.
We use paramiko function to read the key from the file.
Next we connect to the client and store the client object in self._ssh_client.
Know we need to load a list of known hosts to avoid a connection error.
Lastly we connect to the ssh server.
known_hosts file
One of the more difficult issues I saw with using the paramiko package was many folks were struggling with the error generated when there was not a known hosts file. Often the “solution” proposed by others was to use following line of code.
With the “….” replaced with many more characters. You should get the known hosts from the ssh server admin. However you can generate it if you are positive it is secure.
Where do you live? May be a simple question, but when companies are asking where someone lives, the answer isn’t always so easy. The physical address and the mailing address of a person can be two completely separate things. And when companies want to send direct mail, they need to ensure the message is appropriate.
Today I am going to show a Python program that I wrote that can process millions of addresses and verify if the address is a valid mailing address that matches the physical address. And how the majority of online mapping services get it wrong.
A question I’ve always wondered is their a magic formula for creating a number one song. Many song writers are prolific however their song doesn’t necessarily have commercial success. It may be difficult to quantify how the musical composition relates to the song’s success. However I am going to make an attempt at evaluating the lyrics of songs to determining if we can accurately predict if a song will be commercially successful.
The first step is getting the list of the songs by the artist. I am using the website http://www.azlyrics.com to obtain the list of songs and the lyrics for the songs.
If we want to compute the minimum number of flight segments between a starting city and target city, we can construct an undirected graph. In the graph the nodes represent cities and the edges represent the flight segments. We can count the number of segments to determine the shortest distance.
The following can be applied to any situation in finding the shortest path. It is an implementation of the breadth first search algorithm.
Testing for Unconnected Components in an Undirected Graph
With a graph structure it is possible that parts of the graph will not be connected to each other. An example of this would be with social networks, not all users are friends with other users.
The code will find the total number of connected components of the graph, or graph parts in an undirected graph.
Finding an Exit from a Maze using undirected graphs.
We can think of a maze as a rectangular grid of cells with paths between adjacent cells. If we want to find if there is a path from a given cell to a given exit from the maze, where the exit is represented by a cell, you can represent the maze as an undirected graph.
The nodes of the graph are cells of the maze, and two nodes are connected an undirected edge if they are adjacent and there is no wall between them. Therefore we can surmise we just need to see if a path, series of edges connecting the nodes, to determine if the two nodes are connected.
Today we will look at how to capture streaming data and perform some simple queries as the data is streamed. We will use the regular expressions library and the PySpak library. The streaming data comes from a weather station that transmits different weather at different intervals. We will need to find the correct data out of the stream and output the results.
Today we will look at the SQLContext object from the PySpark library and how you can use it to connect to a local database. In the example below we will:
Connect to a local PostgreSQL database and read the contents into a dataframe.
Run some simple SQL queries
And join two data frames together
