py插入postgres数据库代码示例2021-05-19

py插入postgres数据库代码示例

def saveToPostgis(feats,remarks):

    # 连接数据库
    conn = psycopg2.connect(database='postgis_21_sample', user='postgres', password='postgres', host='192.168.1.100', port='5432')
    curs = conn.cursor()
    # 编辑写入数据的sql
    # xy = [minx,miny,minx,maxy,maxx,maxy,maxx,miny,minx,miny]
    # geom = 'POLYGON(({} {},{} {},{} {},{} {},{} {}))'.format(*xy)
    # print(geom)

    delete_sql = 'delete from gridmanagement where \'remarks\'=\'%s\'' % remarks
    curs.execute(delete_sql)
    insert_sql = ''
    for feat in feats:
        geom = feat.geometry()
        wkt = geom.asWkt()
        id = feat.attribute('id')
        wgbh = feat.attribute('wgbh')

        insert_sql += 'insert into gridmanagement(NO,GEOM,remarks) values(\'%s\',ST_GeomFromText(\'%s\'),\'%s\');'%(wgbh,wkt,remarks)

    curs.execute(insert_sql)
    # #判断不存在再进行加入
    # sqlSelect = 'select count(*) from geosel where no = \'%s\''%bianhao
    # curs.execute(sqlSelect)
    # # 获取结果集的每一行
    # rows = curs.fetchall()
    # for r in rows:
    #     for nCount in r:
    #         if nCount==0:
    #             curs.execute(insert_sql)

    # 提交数据
    conn.commit()
    # 关闭数据库
    curs.close()

py插入 oracle

'''
Created on 2016年7月7日
@author: Tommy
'''
import json

import cx_Oracle


class Oracle(object):
    """  oracle db operator  """

    def __init__(self, userName, password, host, instance):
        self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName, password, host, instance))
        self.cursor = self._conn.cursor()

    def queryTitle(self, sql, nameParams={}):
        if len(nameParams) > 0:
            self.cursor.execute(sql, nameParams)
        else:
            self.cursor.execute(sql)

        colNames = []
        for i in range(0, len(self.cursor.description)):
            colNames.append(self.cursor.description[i][0])

        return colNames

    # query methods
    def queryAll(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def queryOne(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    def queryBy(self, sql, nameParams={}):
        if len(nameParams) > 0:
            self.cursor.execute(sql, nameParams)
        else:
            self.cursor.execute(sql)

        return self.cursor.fetchall()

    def insertBatch(self, sql, nameParams=[]):
        """batch insert much rows one time,use location parameter"""
        self.cursor.prepare(sql)
        self.cursor.executemany(None, nameParams)
        self.commit()

    def commit(self):
        self._conn.commit()

    def __del__(self):
        if hasattr(self, 'cursor'):
            self.cursor.close()

        if hasattr(self, '_conn'):
            self._conn.close()


def test1():
    # sql = """select user_name,user_real_name,to_char(create_date,'yyyy-mm-dd') create_date from sys_user where id = '10000' """
    sql = """select user_name,user_real_name,to_char(create_date,'yyyy-mm-dd') create_date from sys_user where id =: id """
    oraDb = Oracle('test', 'java', '192.168.0.192', 'orcl')

    fields = oraDb.queryTitle(sql, {'id': '10000'})
    print(fields)

    print(oraDb.queryBy(sql, {'id': '10000'}))


def test2():
    oraDb = Oracle('sde', 'sde', '192.168.100.101', 'SDE')
    cursor = oraDb.cursor

    create_table = """
    CREATE TABLE python_modules (
    module_name VARCHAR2(50) NOT NULL,
    file_path VARCHAR2(300) NOT NULL
    )
    """
    from sys import modules

    cursor.execute(create_table)
    M = []
    for m_name, m_info in modules.items():
        try:
            M.append((m_name, m_info.__file__))
        except AttributeError:
            pass

    sql = "INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)"
    oraDb.insertBatch(sql, M)

    cursor.execute("SELECT COUNT(*) FROM python_modules")
    print(cursor.fetchone())
    print('insert batch ok.')

    cursor.execute("DROP TABLE python_modules PURGE")


def test3():
    oraDb = Oracle('sde', 'sde', '192.168.100.101', 'SDE')
    cursor = oraDb.cursor

    delete_records = """
    delete from RECTS;
    """
    from sys import modules

    # cursor.execute(delete_records)

    # M = []
    # for m_name, m_info in modules.items():
    #     try:
    #         M.append((m_name, m_info.__file__))
    #     except AttributeError:
    #         pass

    M = []


    with open("data.json", 'r') as load_f:
        load_dict = json.load(load_f)
        idx = 3
        for item in load_dict:
            xmin = item[0]
            ymin = item[3]
            xmax = item[2]
            ymax = item[1]
            matching=item[4]
            type=item[5]
            geom = 'SDE.ST_GEOMETRY(\'polygon(({} {},{} {},{} {},{} {},{} {}))\', 4490)'.format(xmin, ymin,xmin, ymax,xmax, ymax,xmax, ymin,xmin, ymin)
            rec = (idx,matching,type,geom)
            idx+=1
            M.append(rec)
            # print(item)
            sql = "insert into rects(objectid,matching,type,shape) values({},{},'{}',SDE.ST_GEOMETRY(\'polygon(({} {},{} {},{} {},{} {},{} {}))\', 4490))".format(idx,matching,type,xmin, ymin,xmin, ymax,xmax, ymax,xmax, ymin,xmin, ymin)
            # oraDb.insertBatch(sql, rec)
            print(sql)
            cursor.execute(sql)

    oraDb.commit()
    print('M')
    # sql = "INSERT INTO RECTS("MATCHING","TYPE","SHAPE") VALUES (:1, :2, :3)
    # sql = "insert into rects(objectid,matching,type,shape) values((select case when max(objectid) is null then 1 else max(objectid)+1 end from rects),:1,:2,':3')"
    # sql = "insert into rects(objectid,matching,type) values(%d,%f,%s)"
    # print(sql)
    # oraDb.insertBatch(sql, M)
    # oraDb.executemany(sql, M)

    # cursor.execute("SELECT COUNT(*) FROM python_modules")
    # print(cursor.fetchone())
    # print('insert batch ok.')

    # cursor.execute("DROP TABLE python_modules PURGE")


test3()
# https: // blog.csdn.net / neweastsun / article / details / 51852304

py插入sqlserver

import pymssql #引入pymssql模块


def get_conn():
    try:
        conn = pymssql.connect(
            host='192.168.100.6',  # 主机名或ip地址
            user='sa',  # 用户名
            password='1a!23456',  # 密码
            charset='utf8',  # 字符编码
            database='YBDB')  # 库名

        return conn
    except pymssql.Error as e:
        print(e)
        return None

def conn():
    connect = pymssql.connect(server='192.168.100.6', user='sa', password='1a!23456', database='YBDB') #服务器名,账户,密码,数据库名
    if connect:
        print("连接成功!")
    return connect

    # pymssql.connect(host="192.168.100.6:1544",user="sa",password="1a!23456",database="YBDB",charset="utf8")


if __name__ == '__main__':
    conn = conn()
    cursor = conn.cursor()

    sql = 'SELECT * FROM XMYBDD'  # 使用execute()方法执行SQL语句
    cursor.execute(sql.encode('cp936'))

    # 使用fetall()获取全部数据
    data = cursor.fetchone()
    # 打印获取到的数据
    print(data)
    # 关闭游标和数据库的连接
    cursor.close()
    conn.close()
    # get_conn()

py插入mysql

# 导入pymysql模块
import pymysql


class MYSQL:
    # 初始化函数，初始化连接列表
    def __init__(self, host, user, pwd, dbname):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.dbname = dbname

    # 获取数据库游标对象cursor
    # 游标对象：用于执行查询和获取结果
    def getCursor(self):

        # 建立数据库连接
        # self.db = pymysql.connect(self.host, self.user, self.pwd, self.dbname)
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.pwd, db=self.dbname)

        # 创建游标对象
        cur = self.db.cursor()

        # 返回
        return cur

    # 查询操作
    def queryOperation(self, sql):

        # 建立连接获取游标对象
        cur = self.getCursor()

        # 执行SQL语句
        cur.execute(sql)

        # 获取数据的行数
        row = cur.rowcount

        # 获取查询数据
        # fetch*
        # all 所有数据,one 取结果的一行，many(size),去size行
        dataList = cur.fetchall()

        # 关闭游标对象
        cur.close()

        # 关闭连接
        self.db.close()

        # 返回查询的数据
        return dataList, row

    # 删除操作
    def deleteOperation(self, sql):

        # 获取游标对象
        cur = self.getCursor()
        try:
            # 执行SQL语句
            cur.execute(sql)

            # 正常结束事务
            self.db.commit()

        except Exception as e:
            print(e)

            # 数据库回滚
            self.db.rollback()

        # 关闭游标对象
        cur.close()

        # 关闭数据库连接
        self.db.close()

    # 数据更新
    def updateOperation(self, sql):
        cur = self.getCursor()
        try:
            cur.execute(sql)
            self.db.commit()
        except Exception as e:
            print(e)
            self.db.rollback()

        cur.close()
        self.db.close()

    # 添加数据
    def insertOperation(self, sql):

        cur = self.getCursor()
        try:
            cur.execute(sql)
            self.db.commit()
        except Exception as e:
            print(e)
            self.db.rollback()

        cur.close()
        self.db.close()



if __name__ == '__main__':
    ins = MYSQL("localhost","root","1a!23456","TEST")
    ret = ins.queryOperation("select * from T1")
    a = ret[0]
    b=ret[1]
    b=ret[1]

py插入sqlite

import sqlite3


class MyEasySqlite:
    """
    sqlite数据库操作工具类
    database: 数据库文件地址，例如：db/mydb.db
    """
    _connection = None

    def __init__(self, database):
        # 连接数据库
        self._connection = sqlite3.connect(database)

    def _dict_factory(self, cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def execute(self, sql, args=[], result_dict=True, commit=True) -> list:
        """
        执行数据库操作的通用方法
        Args:
        sql: sql语句
        args: sql参数
        result_dict: 操作结果是否用dict格式返回
        commit: 是否提交事务
        Returns:
        list 列表，例如：
        [{'id': 1, 'name': '张三'}, {'id': 2, 'name': '李四'}]
        """
        if result_dict:
            self._connection.row_factory = self._dict_factory
        else:
            self._connection.row_factory = None
        # 获取游标
        _cursor = self._connection.cursor()
        # 执行SQL获取结果
        _cursor.execute(sql, args)
        if commit:
            self._connection.commit()
        data = _cursor.fetchall()
        _cursor.close()
        return data


if __name__ == '__main__':
    db = MyEasySqlite('imgCheck.db')
    # print(db.execute("select name from sqlite_master where type=?", ['table']))
    # print(db.execute("pragma table_info([user])"))
    # print(execute("insert into user(id, name, password) values (?, ?, ?)", [2, "李四", "123456"]))

    print(db.execute("CREATE TABLE IF NOT EXISTS IMGCODE2 (id INTEGER PRIMARY KEY AUTOINCREMENT,CODE TEXT, GUID TEXT, [CreatedTime] TimeStamp NOT NULL DEFAULT (datetime('now','localtime')));"))
    # print(db.execute("insert into IMGCODE values('%s','%s','%s')" % ('aa','b','c')))
    print(db.execute("insert into IMGCODE(code,guid) values('%s','%s')" % ('aa','c')))
    print(db.execute("select * from IMGCODE where code='aa' and guid='b'", result_dict=True))