python sqlalchemy 写数据

最新推荐文章于 2024-05-10 18:02:59 发布

尽拣寒枝不肯栖

最新推荐文章于 2024-05-10 18:02:59 发布

阅读量681

点赞数

分类专栏： database python

本文链接：https://blog.csdn.net/qq_27231343/article/details/51610664

版权

python 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

database

28 篇文章 0 订阅

订阅专栏

    # -*- coding: UTF-8 -*-

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, BigInteger, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

    # declare the connecting to the server
engine = create_engine('mysql://root:.......@127.0.0.1:3306/opensnsinit?charset=utf8', echo=True)

    # declare a Mapping,this is the class describe map to table column
Base = declarative_base()

    # connect session to active the action
Session = sessionmaker(bind=engine)
session = Session()

class Person(Base):
    __tablename__ = 'opensns_ucenter_member'
    id = Column(BigInteger, primary_key=True)
    username = Column(String, nullable=False, default='')
    password = Column(String, nullable=False, default='')
    email = Column(String, nullable=False, default='')
    mobile = Column(String, nullable=False, default='')
    reg_time = Column(Integer, nullable=False, default='')
    reg_ip = Column(BigInteger, nullable=False, default='')
    last_login_time = Column(Integer, nullable=True, default='')
    last_login_ip = Column(BigInteger, nullable=True, default='')
    update_time = Column(Integer, nullable=True, default='')
    status = Column(Integer, nullable=True, default='')
    type = Column(Integer, nullable=True, default='')


#     def __repr__(self):
#         return 'the info is ID %s Pname is %s Address is %s and Age is %s' % (self.Id, self.Pname, self.Address, self.Age)

if __name__ == '__main__':
        # add one
    rl = open('/home/......./selectmembersole')
    i = 0
    has = False
    j=0
    for line in rl.readlines():
        if(j<=8188519):
            j=j+1
            continue;
        line = line.strip('\n')
        arr = line.split('\t')
        id1 = arr[0]
        username1 = arr[1]
        password1 = '8285a9d62f00633ddf8f0c9cf07daabd'
        email1 = "111111@qq.com"
        mobile1 = ''
        reg_time1 = 1461830067
        reg_ip1 = 0
        last_login_time1 = 1461830099
        last_login_ip1 = 0
        update_time1 = 1461830067
        status1 = 1
        type1 = 1
        p = Person(id=id1, username=username1, password=password1, email=email1, mobile=mobile1, reg_time=reg_time1, reg_ip=reg_ip1, last_login_time=last_login_time1, last_login_ip=last_login_ip1, update_time=update_time1, status=status1, type=type1)
        arr = session.query(Person.username).filter(Person.id==id1).all()
        if(len(arr)!=0):
            continue;
        if(len(arr)==0 and has==False):
            print 0
            session.add(p)
            session.commit()
        else:
            print 1
            has = True
            session.add(p)
            session.commit()

#         print session.query(Person.username).filter(Person.password==username1, Person.reg_time!=1461830067).all()
#             session.add(p)
#             session.commit()
#         print query
#         print q
#         session.commit()
#         print session.query(q.exists())
#         query = session.query('username').select_from(Person).filter('id = 1')
#         print query
#         print query.first().name
#         print query
#         print query.filter(Person.id == 5)
#         session.add(p)
#         session.commit()

#     #         query one
#         p_1 = session.query(Person).filter_by(Pname='bruce').first()
#         print(p_1)
#
#             # delete one
#         p_2 = session.query(Person).filter_by(Pname='bruce').first()
#         if p_2:
#             session.delete(p_2)
#             session.commit()
#
#     #         # edit one
#         p_3 = session.query(Person).filter_by(Pname='bruce').first()
#         if p_3:
#             p_3.Age = 22
#             session.commit()