How to access sqlite database files in remote server(ubuntu) using python script in local computer

Question

I am aware that SQLite works well for an embedded system but I am trying to set up a database system with different types of database such as MySQL, through API and SQLite.

The problem is that SQLite data files are stored in Ubuntu server and I want to connect remotely by using its ssh path from a user's computer.

I have tried to connect with paramiko which was successful to execute general command like 'ifconfig'. However, when I execute SQLite command, it didn't work.

import paramiko

cmd = 'ifconfig'
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(remote_ip, username =username, password=pw, port=port)
(stdin, stdout, stderr) = ssh.exec_command(cmd)

Is there any way to directly write an ssh path to connect?

The example Code is below:

import sqlite3
conn = sqlite3.connect('example.db')

You should look into using a different database program that uses a client/server model. — Shawn
– Shawn, Commented Sep 24, 2019 at 2:50
The only way I could see would be to write a new sqlite VFS that does the access through ssh - but maybe a bit to complex. How did you try to run the sqlite command via paramiko? — Lutz
– Lutz, Commented Sep 24, 2019 at 15:18
I put command 'sqlite3 sample.db' to access remotely through paramiko but no response (no output for a long time). Any other common ubuntu commands work though. — Jinny Han
– Jinny Han, Commented Sep 25, 2019 at 2:11

Steve · Accepted Answer · 2020-05-29 15:59:41Z

2

I just figured this out using Paramiko smashing together a few suggestions from places. It feels a little hacky, but I have a basic need to do some bulk checks on several things in a Python script, one of which is checking for a value in the sqlite3 db on several servers. This works for me:

import paramiko
from paramiko import SSHClient

ssh = SSHClient()
ssh.load_system_host_keys()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(host=hostname, username='username', password='password')
ssh_stdin, ssh_stdout, ssh_stderr = ssh.exec_command('sqlite3 /path/to/yourdb.sqlite3 "select * from table;"')
stdout=ssh_stdout.readlines()
print(stdout)

answered May 29, 2020 at 15:59

Steve

404 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to access sqlite database files in remote server(ubuntu) using python script in local computer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related