pydbclib It is a general python relational database operation toolkit. It uses a unified interface to operate various relational databases (such as oracle, mysql, postgres, hive, impala, etc.) for addition, deletion, and query. It is a connection driver package for various python databases (such as sqlalchemy, pymysql, CX_ The encapsulation of oracle, pyhive, pyodbc, impala, etc.) unifies SQL placeholders into ': [name]' according to python's simplest principle, which is the same as sqlalchemy
install
pip3 install pydbclib
Easy to use
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 use':[name]'Formal SQL Placeholder for 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 record is displayed in the form of a dictionary, and the record written to the library is also in the form of a dictionary. If you want to use the primitive primitive form, add as to the query function read_ Dict = false parameter
Interface documentation
Database connection, refer to more common database connection methods End of article
# connect Function has driver Parameters determine which database driver package you are connecting to # driver The default value of the parameter is sqlalchemy，Through sqlalchemy Driver package connection database >>> db = pydbclib.connect("sqlite:///:memory:") >>> db = pydbclib.connect(":memory:", driver='sqlite3') # You can also pass in the 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. Use execute method to execute SQL, which is basically the same as each database connection package. The difference is that it can be executed individually 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 return 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 data
# Query result returns only one record >>> db.read_one("select * from foo") {'a': 1, 'b': 'one'} #read Return iterator type, using get Method to get the first few records, use the map Data cleaning 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 original ancestor record >>> db.read("select * from foo", as_dict=False).get(2) [(1, 'one'), (1, 'one')] # It can also be direct for ergodic >>> 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 and close the connection
>>> db.rollback() >>> db.commit() >>> db.close()
SQL interface encapsulation of table level operations
1. Insert record
# Insert single and 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
2. Query record
# Query fields a=1 First record >>> db.get_table("foo").find_one({"a": 1}) {'a': 1, 'b': 'one'} # It can also be written directly sql Conditional expression. The conditional parameters of other interfaces can be expressions >>> db.get_table("foo").find_one("a=1") {'a': 1, 'b': 'one'} # Query fields a=1 All records, find Return iterator object is the same as above read method >>> db.get_table("foo").find({"a": 1}).get_all() [{'a': 1, 'b': 'one'},...{'a': 1, 'b': 'one'}]
3. Update records
# take a=1 That record b Field value 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'}
4. Delete records
# take a=1 That record was deleted >>> db.get_table("foo").delete({"a": 1}) 11 >>> db.get_table("foo").find({"a": 1}).get_all() []
Common database connections
1. Common Driver
# Using a normal database driven connection, driver Parameter specifies the driver package name # for example pymysql package driver='pymysql',connect Function and the rest of the parameters driver Parameter specifies that the package creation connection parameters are consistent # connect mysql db = pydbclib.connect(user="user", password="password", database="test", driver="pymysql") # connect oracle db = pydbclib.connect('user/password@local:1521/xe', driver="cx_Oracle") # adopt odbc Mode connection db = pydbclib.connect('DSN=mysqldb;UID=user;PWD=password', driver="pyodbc") # Connect via existing drive connection import pymysql con = pymysql.connect(user="user", password="password", database="test") db = pydbclib.connect(driver=con)
2. Sqlalchemy Driver
# use Sqlalchemy Package to connect to the database, drvier The parameter defaults to'sqlalchemy' # connect oracle db = pydbclib.connect("oracle://user:password@local:1521/xe") # connect mysql db = pydbclib.connect("mysql+pyodbc://:@mysqldb") # Through existing engine connect from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://user:password@localhost:3306/test") db = pydbclib.connect(driver=engine)
If you have any questions during use, please comment
Project address pydbclib