By: Levi Masonde | Updated: 2023-02-21 | Comments | Related: > Python
Problem
SKype has an API to access its data and in this article, we look at how to use this API with Python and how to load the data to a SQL Server database.
Solution
Skype has a Restful API for developers to get data from their accounts or other user accounts. With access to the API, you can get, create, update, or delete meeting data. In this tutorial, we will cover how to store Skype meeting data in a SQL Server database to be used for reports and analysis.
Prerequisites
- SQL Server Instance
- Python Version - 3.10.9
- Visual Studio Code, Version 1.72.1
- Windows 10 PC or Windows Server 2019/2022
- Skype Account
Quick Example Using the Skype API
Here is a short example of accessing data from Skype using the API.
Skype has an unofficial Python library called SkPy. Use the pip command below to install SkPy in your environment.
pip install skpy
Then create a Python file named UserData.py and define your Skype username (email) and password, as shown below.
mail = "[email protected]" password = "yourPassword"
Create another Python file named SkypeApp.py and add the following code:
from skpy import Skype from skpy import SkypeChat from UserData import password from UserData import mail sk = Skype(mail,password) conn = sk.conn contacts = sk.contacts chats = sk.chats.recent() print(chats)
When this script is run it will output a list of recent chats. Note the chat IDs, as you will need them to access useful information as we will show in later examples.
Endpoints Exposed with the Skype API
The SkPy Python library is used to access the Skype data. This library helps to bridge Skype for Web and Python code.
You use the Skype class to create a Skype client for the Python code. You can name the client anything, but in this tutorial the client is named "sk" with the code below:
sk = Skype(<your email>,<your email password>)
After declaring the Skype client, you can easily access any of these Skype endpoints:
- Contacts
- Groups
- Meetings
- User
- Chats
Login information to the API with Python
As we did in the first example, we will use the file UserData.py to store the login details as shown below:
mail = "<your email>" password = "<your password>"
Preparing SQL Server
Assuming you have SQL Server installed and running, you can now create a database for your API data.
Using the SQLCMD Utility to create a database
We will SQLCMD to connect to SQL Server to create a database and a table.
sqlcmd -S PC_NAME\SQL_INSTANCE -E 1> CREATE DATABASE SkypeDB; 2> GO
Using SQLCMD to create a table
In this section, you will use SQLCMD to create a table in your database.
Use the command below to connect to the database.
Use SkypeDB; GO
Now in the same console, use the CREATE TABLE command as follows to create the table.
CREATE TABLE SkypeTable(ID int NOT NULL PRIMARY KEY); GO
SQLAlchemy Configuration
After creating the database on your SQL Server, you need to set up a connection string between the Python code and SQL Server.
Use the pip command pip install sqlalchemy to install SQLAlchemy to your environment.
Since urllib comes pre-installed with Python, there's no need to install it. If you try you will get the message below.
Create a file named SkypyDbConnection.py and add the code below to configure your SQLAlchemy:
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
conn = urllib.parse.quote_plus(
'Data Source Name=KNITNETDS;'
'Driver={SQL Server};'
'Server= PC_NAME\SQL_INSTANCE'
'Database=SkypeDB;'
'Trusted_connection=yes;'
)
try:
connEngine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
print("Passed")
except:
print("failed!")
Accessing and Loading Skype Data to SQL Server
Great work getting your API data and creating a SQL Server table. Now you have to link them. To do this, create a new Python file named SkypeApp.py.
Get List of Chats
Before you can access your chats, you need to get the chat ids. To do this, add this code to the SkypeApp.py file:
from skpy import Skype from skpy import SkypeChat from UserData import password from UserData import mail sk = Skype(mail,password) conn = sk.conn contacts = sk.contacts chats = sk.chats.recent() print(chats)
After you write the code, click the Play button at the top right of the SQLCMD Visual Studio code to run the code. You will get a list of recent chats on your terminal, as shown below:
From here, you can either iterate through all the chat IDs or choose the chats from a meeting you want.
Get Specific Chat
Next, you will specify the meeting chats you want and write the messages to your database. To get a specific message, add the following code to SkypeApp.py:
from skpy import Skype from skpy import SkypeChat from UserData import password from UserData import mail sk = Skype(mail,password) conn = sk.conn contacts = sk.contacts chats = sk.chats.recent() #The "19:[email protected]" String is a chat id obtained on the first section of this tutorial. MSSQLTIPS_meeting = sk.chats["19:[email protected]"] meeting_messages = MSSQLTIPS_meeting.getMsgs() callMsg = meeting_messages[0] textMsg = meeting_messages[2] print(textMsg)
When run, the console should show details of the message, including the ChatId, the time of the message, and the content.
Write Chat Data to SQL Server
To write the data to your database, use the code below in the SkypeApp.py file:
from skpy import Skype
from skpy import SkypeChat
from UserData import password
from UserData import mail
import pandas as pd
from SkypyDbConnection import connEngine
sk = Skype(mail,password)
conn = sk.conn
contacts = sk.contacts
chats = sk.chats.recent()
ChatIDs = []
for i in chats:
ChatIDs.append(i)
print(ChatIDs)
meeting = []
for i in ChatIDs:
if "skype" in i:
print(i)
MSSQLTIPS_meeting = sk.chats[i]
meeting_messages = MSSQLTIPS_meeting.getMsgs()
textMsg = meeting_messages[2]
userID = meeting_messages[2].userId
content = meeting_messages[2].content
SkypeType = meeting_messages[0].type
chatId = meeting_messages[0].chatId
MeetingTime = str(meeting_messages[0].time)
meeting.insert(0,content)
meeting.insert(1,chatId)
meeting.insert(2,MeetingTime)
meeting.insert(3,SkypeType)
print(meeting)
df = pd.DataFrame({"time": meeting[2],"chatId": meeting[1],"meeting type":meeting[3],"Content":meeting[0]}, index=[0])
print(df)
try:
df.to_sql('SkypeTable',con=connEngine,if_exists='replace',index=True)
except Exception as e:
print("Failed!")
print(e)
Verify Data was Loaded to the Database
After running the script above, you can log into your server and check if the data has been stored on your database using SQLCMD.
sqlcmd -S PC_NAME\SQL_INSTANCE -E USE SQLDB; GO SELECT * FROM SkypeTable; GO
Conclusion
You learned how to access Skype's API service and how to store the data into a SQL Server database. This process can come in handy when you need to keep records of your meetings and do analysis on the data, like checking the duration of your meetings. Feel free to explore more options of the Skype API.
Next Steps
- Analyze your data using Power BI for reporting: Load API Data to SQL Server Using Python and Generate Report with Power BI
- Manage your SQL Server using the SQL Server Management Studio (SSMS): Use SQL Server Management Studio to Connect to Database
- Having issues connecting to your SQL Data Source? Follow this guide on how to set up your datasource.
About the author
Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-02-21
