(8.0.22)
接続元：CentOS7
接続先：CentOS7
https://qiita.com/valzer0/items/2f27ba98397fa7ff0d74
https://basicincome30.com/python3-mysql-connector
-- 1. 環境設定
-- 1.1. mysql-connector-pythonのインストール
yum update
yum install python3 python3-devel mysql-devel gcc
pip3 install mysql-connector-python
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',current_timestamp());
insert into tab1 values(2,'B',current_timestamp());
select * from tab1;
-- 3. DB接続確認
vim a.py
---
import mysql.connector
conn = mysql.connector.connect(host="mmm066" ,port="3306" ,database="test" ,user="test" ,password="test")
print ("---- select(バインド変数有) ---- ")
cur = conn.cursor()
cur.execute("select * from tab1 where col1 >= %s",(1,))
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
print ("---- ")
cur.execute("select * from tab1 where col1 >= %s",(2,))
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- select(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("select * from tab1 where col1 >= 1")
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- insert(バインド変数有) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(%s,%s,%s)",(100,"X","2021/01/01"))
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
print ("---- insert(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
conn.close()
---
python3 a.py
(19c)
接続元：CentOS7
接続先：CentOS7
https://qiita.com/kngsym2018/items/04514d623248a070adee
http://www.denzow.me/entry/2017/12/02/180320
https://qiita.com/nakaie/items/2c5a4b5d8b2ed77d843f
-- 1. 環境設定
-- 1.1. Oracle Instant Clientのダウンロードとインストール
rpm -ivh oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-devel-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64.rpm
-- 1.2. cx_Oracleのインストール
yum update
yum install python3
pip3 install cx_oracle
-- 2. テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(10),col3 timestamp);
insert into tab1 values(1,'A',sysdate);
insert into tab1 values(2,'B',sysdate);
commit;
select * from tab1;
-- 3. DB接続確認
vim a.py
---
import cx_Oracle
conn = cx_Oracle.connect(user='test', password='test', dsn='mmm065:1521/pdb1.example.com')
print ("---- select(バインド変数有) ---- ")
cur = conn.cursor()
cur.prepare("select * from tab1 where col1 >= :col1")
for row in cur.execute(None, {"col1":1}):
print(row[0],"\t",row[1],"\t",row[2])
print ("---- ")
for row in cur.execute(None, {"col1":2}):
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- select(バインド変数なし) ---- ")
cur = conn.cursor()
for row in cur.execute("select * from tab1 where col1 >= 1"):
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- insert(バインド変数有) ---- ")
cur = conn.cursor()
cur.prepare("insert into tab1(col1,col2,col3) values(:col1,:col2,:col3)")
cur.execute(None, {"col1":100,"col2":"X","col3":"2021/01/01"})
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
print ("---- insert(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
conn.close()
---
python3 a.py
(13)
接続元：CentOS7
接続先：CentOS8
https://www.ashisuto.co.jp/db_blog/article/20160308_postgresql_with_python.html
https://qiita.com/tom-sato/items/b5f1263911fdedd5e81b
https://qiita.com/hoto17296/items/0ca1569d6fa54c7c4732
https://www.psycopg.org/articles/2012/10/01/prepared-statements-psycopg/
https://www.yokoyan.net/entry/2018/10/12/183250
https://stackoverflow.com/questions/24441132/how-can-i-get-affected-row-count-from-psycopg2-connection-commit
※ドライバレベルでのprepare文はサポートされていない模様
※タプルの要素が1つでもカンマを付与する
-- 1. 環境設定
-- 1.1. psycopg2のインストール
yum update
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install epel-release centos-release-scl
yum install postgresql13-devel
yum install python3 python3-devel
yum install gcc
export PATH=$PATH:/usr/pgsql-13/bin
pip3 install psycopg2
python3 -c "import psycopg2"
echo $?
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
select * from tab1;
-- 3. DB接続確認
vim a.py
---
import psycopg2
conn = psycopg2.connect("host=mmm067 port=5432 dbname=test user=postgres password=postgres")
print ("---- select(バインド変数有) ---- ")
cur = conn.cursor()
cur.execute("select * from tab1 where col1 >= %s",(1,))
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
print ("---- ")
cur.execute("select * from tab1 where col1 >= %s",(2,))
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- select(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("select * from tab1 where col1 >= 1")
for row in cur:
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- insert(バインド変数有) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(%s,%s,%s)",(100,"X","2021/01/01"))
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
print ("---- insert(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
conn.close()
---
python3 a.py
(2019)
接続元：CentOS7
接続先：Windows Server 2016
https://docs.microsoft.com/ja-jp/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15
https://github.com/mkleehammer/pyodbc/
-- 1. 環境設定
-- 1.1. UnixODBCのインストール
yum update -y
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
yum remove unixODBC-utf16 unixODBC-utf16-devel
ACCEPT_EULA=Y yum install msodbcsql17
yum install unixODBC-devel
ls /usr/lib64/libodbc.so
-- 1.2. pyodbcのインストール
yum install python3 python3-devel gcc gcc-c++
pip3 install pyodbc
pip3 list
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 datetime2);
insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
select * from tab1;
-- 3. DB接続確認
vim a.py
---
import pyodbc
server = 'tcp:mmm064'
database = 'test'
username = 'sa'
password = 'Aaa!1234'
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
conn.autocommit = False
print ("---- select(バインド変数有) ---- ")
cur = conn.cursor()
for row in cur.execute("select * from tab1 where col1 >= ?",(1,)):
print(row[0],"\t",row[1],"\t",row[2])
print ("---- ")
for row in cur.execute("select * from tab1 where col1 >= ?",(2,)):
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- select(バインド変数なし) ---- ")
cur = conn.cursor()
for row in cur.execute("select * from tab1 where col1 >= 1"):
print(row[0],"\t",row[1],"\t",row[2])
cur.close()
print ("---- insert(バインド変数有) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(?,?,?)",(100,"X","2021/01/01"))
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
print ("---- insert(バインド変数なし) ---- ")
cur = conn.cursor()
cur.execute("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
conn.commit()
print(f"ret = {cur.rowcount}")
cur.close()
conn.close()
---
python3 a.py
