Brief introduction of python general database operation tool pydbclib

pydbclib is a general python relational database operation toolkit, which uses the interface of pydbclib to operate various relational databases (such as oracle, mysql, postgres, hive, impala, etc.) for addition, deletion and modification. It is the encapsulation of various python database connection driver packages (such as sqlalchemy, pymysql, cx_Oracle, pyhive, pyodbc, impala, etc.). According to the simplest principle of python, SQL placeholders are in the form of ': [name]', which is the same as sqlalchemy

Installation

pip3 install pydbclib

Simple use

Take a look at a simple query example

from pydbclib import connect
#  Use with Context, can automatically submit, automatically close the connection 
with connect("sqlite:///:memory:") as db:
  db.execute('create table foo(a integer, b varchar(20))')
  #  Unified 1 Use ':[name]' Formal SQL Placeholder of 
  db.execute("insert into foo(a,b) values(:a,:b)", [{"a": 1, "b": "one"}]*4)
  print(db.read("select * from foo").get_one())
  print(db.read("select * from foo").get_all())
  print(db.read("select * from foo").to_df())
  db.get_table("foo").insert({"a": 2, "b": "two"})
  print(db.get_table("foo").find_one({"a": 2}))
  print(db.get_table("foo").find().get_all())
  print(db.get_table("foo").find().to_df())

The query result records are displayed in the form of dictionary, and the records written to the database are also in the form of dictionary. If you want to use the original progenitor form, add the parameter as_dict=False to the query function read

Interface document

Database connection, refer to the end of the article for more common database connection methods

# connect Function has a driver Parameter determines which database driver package you use to connect 
# driver The default value of the parameter is sqlalchemy That is, through sqlalchemy Driver package connection database 
>>> db = pydbclib.connect("sqlite:///:memory:")
>>> db = pydbclib.connect(":memory:", driver='sqlite3')
#  You can also pass in a driver package connection object 
>>> import sqlite3
>>> db = pydbclib.connect(driver=sqlite3.connect(":memory:"))
>>> from sqlalchemy import create_engine
>>> db = pydbclib.connect(driver=create_engine("sqlite:///:memory:"))

Native SQL interface

1. SQL is executed using execute method, which is basically the same as each database connection package. The difference is that it can be executed in a single piece or in batches (equivalent to executemany). In addition, the SQL placeholder of this method is in the form of ': [name]'

>>> record = {"a": 1, "b": "one"}
>>> db.execute('create table foo(a integer, b varchar(20))')
#  Insert a single record, and the result returns the number of affected rows 
>>> db.execute("insert into foo(a,b) values(:a,:b)", record)
1
#  Insert multiple records 
>>> db.execute("insert into foo(a,b) values(:a,:b)", [record, record])
2

2. Query the data

#  Query results only return 1 A record 
>>> db.read_one("select * from foo")
{'a': 1, 'b': 'one'}
#read Returns the iterator type, using the get Method to get the first few records, using the map Perform data cleansing for each record 
>>> db.read("select * from foo").map(lambda x: {f"foo.{k}": v for k,v in x.items()}).get(2)
# as_dict=False Return to the original ancestral record 
>>> db.read("select * from foo", as_dict=False).get(2)
[(1, 'one'), (1, 'one')]
#  You can also directly for Traversal 
>>> for r in db.read("select * from foo"):
...   print(r)
... 
{'a': 1, 'b': 'one'}
{'a': 1, 'b': 'one'}
{'a': 1, 'b': 'one'}
#  Convert to pandas dataframe Object ,  The premise is already installed pandas
>>> db.read("select * from foo").to_df()
  a  b
0 1 one
1 1 one
2 1 one

3. Submit, Rollback, Close Connection

>>> db.rollback()
>>> db.commit()
>>> db.close()

SQL interface encapsulation for table-level operations

Step 1 Insert a record

#  Insert single or multiple entries, the key value of the input parameter dictionary must have the same name as the field in the table 
>>> db.get_table("foo").insert({"a": 1, "b": "one"})
1
>>> db.get_table("foo").insert([{"a": 1, "b": "one"}]*10)
10

STEP 2 Query records

#  Query field a=1 No. 1 1 A record 
>>> db.get_table("foo").find_one({"a": 1})
{'a': 1, 'b': 'one'}
#  It can also be written directly as sql Conditional expressions, similar conditional parameters of other interfaces can be expressions 
>>> db.get_table("foo").find_one("a=1")
{'a': 1, 'b': 'one'}
#  Query field a=1 All records, find Returns the iterator object as above read Method 
>>> db.get_table("foo").find({"a": 1}).get_all()
[{'a': 1, 'b': 'one'},...{'a': 1, 'b': 'one'}]

Step 3 Update records

#  Will a=1 That record b Field value is updated to "first"
>>> db.get_table("foo").update({"a": 1}, {"b": "first"})
11
>>> db.get_table("foo").find({"a": 1}).get_one()
{'a': 1, 'b': 'first'}

Step 4 Delete records

#  Will a=1 That record is deleted 
>>> db.get_table("foo").delete({"a": 1})
11
>>> db.get_table("foo").find({"a": 1}).get_all()
[]

Common database connections

1. Common Driver

from pydbclib import connect
#  Use with Context, can automatically submit, automatically close the connection 
with connect("sqlite:///:memory:") as db:
  db.execute('create table foo(a integer, b varchar(20))')
  #  Unified 1 Use ':[name]' Formal SQL Placeholder of 
  db.execute("insert into foo(a,b) values(:a,:b)", [{"a": 1, "b": "one"}]*4)
  print(db.read("select * from foo").get_one())
  print(db.read("select * from foo").get_all())
  print(db.read("select * from foo").to_df())
  db.get_table("foo").insert({"a": 2, "b": "two"})
  print(db.get_table("foo").find_one({"a": 2}))
  print(db.get_table("foo").find().get_all())
  print(db.get_table("foo").find().to_df())

0

2. Sqlalchemy Driver

from pydbclib import connect
#  Use with Context, can automatically submit, automatically close the connection 
with connect("sqlite:///:memory:") as db:
  db.execute('create table foo(a integer, b varchar(20))')
  #  Unified 1 Use ':[name]' Formal SQL Placeholder of 
  db.execute("insert into foo(a,b) values(:a,:b)", [{"a": 1, "b": "one"}]*4)
  print(db.read("select * from foo").get_one())
  print(db.read("select * from foo").get_all())
  print(db.read("select * from foo").to_df())
  db.get_table("foo").insert({"a": 2, "b": "two"})
  print(db.get_table("foo").find_one({"a": 2}))
  print(db.get_table("foo").find().get_all())
  print(db.get_table("foo").find().to_df())

1

If you have any questions during the use, please comment and exchange
Project Address pydbclib

The above is the python general database operation tool pydbclib introduction to the use of detailed content, more information about python database operation tool pydbclib please pay attention to other related articles on this site!