Connecting to Databases

How to connect/work with Databases in Python ?

import sqlite3
conn = sqlite3.connect('example.db')
sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])
c = conn.cursor()
c.execute("INSERT INTO stocks VALUES ('2006-01-05','python','R',100,35.14)")
c.execute('SELECT * FROM table WHERE symbol=?', t)
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

>>> from sqlalchemy import create_engine
from sqlalchemy import text, Unicode
from sqlalchemy.sql import select
from sqlalchemy import MetaData, Table
from flask_sqlalchemy import SQLAlchemy
>>> engine = create_engine('sqlite:///:memory:')
engine = create_engine('sqlite:///census.sqlite')
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
engine = create_engine('mysql://scott:tiger@localhost/mydb')
engine = create_engine("oracle://scott:tiger@dsn", implicit_returning=False)
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
connection = engine.connect()

>>> pd.read_sql(SELECT * FROM my_table;, engine)
>>> pd.read_sql_table('my_table', engine)
>>> pd.read_sql_query(SELECT * FROM my_table;', engine)
print(engine.table_names())
results = sql.read_sql(query, con=conn)
results.head()
s = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))
metadata=MetaData()
census=Table('census', metadata, autoload=True, autoload_with=engine)

print(repr(census)) -- reflection
Reflection is the process of reading the database and building the metadata based on that information

print(census.columns.keys()) -- Print column names
print(repr(metadata.tables['census']))
result_proxy=connection.execute(stmt)
result_set= result_proxy.fetchall()

import cx_Oracle
conn_str = u'user/password@host:port/service'
conn = cx_Oracle.connect(conn_str)
connection = cx_Oracle.connect('userid/password@99.999.9.99:PORT/SID')
c = conn.cursor()
c.execute(u'select your_col_1, your_col_2 from your_table')
for row in c:
    print row[0], "-", row[1]
for row in cursor:
    output.writerow(row)
cursor.close()
conn.close()

ver = con.version.split(".")
row = cur.fetchone()
res = cur.fetchmany(numRows=3)
res = cur.fetchall()
cur.prepare('select * from departments where department_id = :id')
cur.execute(None, {'id': 110})
curs.arraysize=50
os.putenv('ORACLE_HOME', '/oracle/product/12.2.0/db_1')
os.putenv('LD_LIBRARY_PATH', '/oracle/product/12.2.0/db_1/lib')

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, data)
conn.commit()

Related Python Articles:  scipy package in Python            File Handling in Python