db2ls 0.1.3

use python to handle SQL

How to install

pip install db2ls

git clone https://github.com/Jianfengliu0413/db2ls.git

usage:

from db2ls import db2ls

db path

# Example usage
db_path = 'test.db'

connect

# option 1:  dirrect connect
db2ls.connect(db_path)

<db2ls.db2ls.db2ls at 0x1110ff5c0>

db2ls.connect(db_path).vacuum()

Error executing query: no such table: change_log
Database vacuumed

create a table

table="germany"

# option 2: use "with" method
with db2ls(db_path) as db:
    db.create(table,["id integer primary key", "city text", "postcode text"])

Error executing query: no such table: change_log
Table created with definition: CREATE TABLE IF NOT EXISTS germany (id integer primary key, city text, postcode text)
Connection closed

insert info

with db2ls(db_path) as db:
    db.insert(table, ['id', 'city',"postcode"], [1,"Heidelberg","69115"])

Error executing query: no such table: change_log
Data inserted into germany: [1, 'Heidelberg', '69115']
Connection closed

take a look

with db2ls(db_path) as db:
    db.print(table, ['id', 'city',"postcode"])

(1, 'Heidelberg', '69115')
Connection closed

only check specific columns

with db2ls(db_path) as db:
    db.print(table, ['id', 'city'])

(1, 'Heidelberg')
Connection closed

or check all (*)

with db2ls(db_path) as db:
    db.print(table, ["*"])
    db.print(table, "*") # not work

(1, 'Heidelberg', '69115')
(1, 'Heidelberg', '69115')
Connection closed

insert more data

with db2ls(db_path) as db:
    db.insert(table, ['id', 'city',"postcode"], [2,"Neckargemuend","69151"])
    db.insert(table, ['id', 'city',"postcode"], [3,"Wiesloch","69168"])
    db.insert(table, ['id', 'city',"postcode"], [4,"Leimen","69181"])
    db.insert(table, ['id', 'city',"postcode"], [5,"Walldorf","69190"])
    db.insert(table, ['id', 'city',"postcode"], [6,"Schriesheim","69198"])
    db.insert(table, ['id', 'city',"postcode"], [7,"Sandhausen","69207"])

Error executing query: no such table: change_log
Data inserted into germany: [2, 'Neckargemuend', '69151']
Error executing query: no such table: change_log
Data inserted into germany: [3, 'Wiesloch', '69168']
Error executing query: no such table: change_log
Data inserted into germany: [4, 'Leimen', '69181']
Error executing query: no such table: change_log
Data inserted into germany: [5, 'Walldorf', '69190']
Error executing query: no such table: change_log
Data inserted into germany: [6, 'Schriesheim', '69198']
Error executing query: no such table: change_log
Data inserted into germany: [7, 'Sandhausen', '69207']
Connection closed

with db2ls(db_path) as db:
    db.print(table, ["*"])

(1, 'Heidelberg', '69115')
(2, 'Neckargemuend', '69151')
(3, 'Wiesloch', '69168')
(4, 'Leimen', '69181')
(5, 'Walldorf', '69190')
Connection closed

you see: only first 5 get printed

with db2ls(db_path) as db:
    db.print(table, "*",n=10)

(1, 'Heidelberg', '69115')
(2, 'Neckargemuend', '69151')
(3, 'Wiesloch', '69168')
(4, 'Leimen', '69181')
(5, 'Walldorf', '69190')
(6, 'Schriesheim', '69198')
(7, 'Sandhausen', '69207')
Connection closed

update table

if i want to change the postcode in city 'Leimen'

db.connect(db_path).execute("update germany set city='Tübingen' where city = 'Leimen'")
db.connect(db_path).print("germany")

Error executing query: no such table: change_log
(1, 'Heidelberg', '69115')
(2, 'Neckargemuend', '69151')
(3, 'Wiesloch', '69168')
(4, 'Tübingen', '69181')
(5, 'Walldorf', '69190')

with db2ls(db_path) as db:
    db.update(table, "postcode = '72076'", "postcode = '69181'")
    db.print(table,"*")

UPDATE germany SET postcode = '72076' WHERE postcode = '69181'
Error executing query: no such table: change_log
(1, 'Heidelberg', '69115')
(2, 'Neckargemuend', '69151')
(3, 'Wiesloch', '69168')
(4, 'Tübingen', '72076')
(5, 'Walldorf', '69190')
Connection closed

get columns names

db.connect(db_path).columns(table)

['id', 'city', 'postcode']

conver to DataFrame()

db.connect(db_path).to_df(table)

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	id	city	postcode
0	1	Heidelberg	69115
1	2	Neckargemuend	69151
2	3	Wiesloch	69168
3	4	Tübingen	72076
4	5	Walldorf	69190
5	6	Schriesheim	69198
6	7	Sandhausen	69207

db.connect(db_path).to_df(table)["city"].tolist()

['Heidelberg',
 'Neckargemuend',
 'Wiesloch',
 'Tübingen',
 'Walldorf',
 'Schriesheim',
 'Sandhausen']