用python连接mysql数据库,原生sql建表

用python连接mysql数据库,原生sql建表

import MySQLdb   #mysqlclient  如果是mysqlclient那就导入这个包
import pymysql   #pymysql      如果是pymysql 那就导入这个包

''' 
两个包使用方法类似
conn = MySQLdb.connect(host='数据库IP地址',port=3306,
                       user='账户',password='密码',
                       database='数据库',charset='utf8')
print(conn)#返回一个数据库对象
'''



conn = pymysql.connect(host='数据库IP地址',port=3306,
                       user='用户',password='用户密码',
                       database='数据库',charset='utf8')
print(conn)#返回一个数据库对象

– python连接数据库标准写法
–增加数据

import MySQLdb   #mysqlclient
import pymysql   #pymysql

'''
conn = MySQLdb.connect(host='47.111.252.112',port=3306,
                       user='chenmeng',password='Chenmeng.123',
                       database='school',charset='utf8')
print(conn)
'''

#1.创建数据库连接
conn = pymysql.connect(host='47.111.252.112',port=3306,
                       user='chenmeng',password='Chenmeng.123',
                       database='school',charset='utf8')  #autocommit=True

#print(conn)


try:
#2.获取游标对象（通过游标对象可以向Mysql数据库发出SQL语句)
    with conn.cursor() as cursor:
    
        #3.通过游标执行SQL语句并获取结果
        restult = cursor.execute('insert into tb_student values(1031,"唐冰玉","女","98-2-23","2")')
        #在sql语句中如果使用变量可以使用占位符%s ，这个不是格式化字符串
        #假设有五个变量,id,name,sex,birth,collid
        # restult = cursor.execute('insert into tb_student values(%s,%s,%s,%s,%s)',(id,name,sex,birth,collid))
        
        if restult == 1:
            print('新增数据成功!')
            conn.commit()手动模式添加   如果autocommit=true的话,就不需要使用commit方法!
            
  
 #4.回滚(撤销操作)         
except MySQLdb.MySQLError as err:
    print(err)
    conn.rollback()


#5.释放数据库连接
finally:
    conn.close()

– 删除数据

import pymysql   #pymysql

#1.创建数据库连接
conn = pymysql.connect(host='47.111.252.112',port=3306,
                       user='chenmeng',password='Chenmeng.123',
                       database='school',charset='utf8')  #autocommit=True

#print(conn)
no = int(input('id:'))

try:
#2.获取游标对象（通过游标对象可以向Mysql数据库发出SQL语句)
    with conn.cursor() as cursor:
        #3.通过游标执行SQL语句并获取结果
        restult = cursor.execute('delete from tb_student where id=%s',(no,))
        if restult == 1:
            print('删除数据成功!')
            conn.commit()
except:
    #4.回滚(撤销操作)
    conn.rollback()


#5.释放数据库连接
finally:
    conn.close()

–查询数据

import pymysql


conn = pymysql.connect(host='47.111.252.112',port=3306,
                       user='chenmeng',password='Chenmeng.123',
                       database='school',charset='utf8')  #autocommit=True



try:

    with conn.cursor(pymysql.cursors.DictCursor) as cursor:
    				#这里如果不指定的话,默认是元祖型游标
        cursor.execute('select * from tb_student')
        row = cursor.fetchone()#返回一条数据
        print(row)


finally:
    conn.close()

建表

import psycopg2


class CreateTable(object):
    '''pgsql ORM'''

    def __init__(self, database, user, password, host, port=5432, **kwargs):
        self._conn = psycopg2.connect(database=database,
                                      user=user,
                                      password=password,
                                      host=host,
                                      port=port,
                                      **kwargs)

        self._cursor = self._conn.cursor()

    @property
    def conn(self):
        return self._conn

    @conn.setter
    def conn(self, value):
        self._conn = value
        self._cursor = self._conn

    @property
    def cursor(self):
        return self._cursor

    def to_sql(self, sql):
        try:
            self._cursor.execute(sql)
        except Exception, e:
            print u'error', e

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

    def __call__(self, sql, *args, **kwargs):
        self.to_sql(sql)
        self.save()

    def __del__(self):
        self._conn.close()
        self._cursor.close()


if __name__ == '__main__':
    print 1
    # conn = psycopg2.connect(database="scanner", user="ifns", password="", host="192.168.1.60", port="5432")
    # cursor = conn.cursor()
    # cursor.execute("CREATE TABLE test_conn (id SERIAL PRIMARY KEY, name text)")
    # conn.commit()
    # cursor.close()
    # conn.close()