def engine_to_db(to_db):
MYSQL_CONN_URL_NEW = "mysql+mysqldb://"+MYSQL_USER+":"+MYSQL_PWD+"@"+MYSQL_HOST+"/"+to_db+"?charset=utf8"
engine = create_engine(
MYSQL_CONN_URL_NEW,
encoding='utf8',convert_unicode=True
)
return engine
def save_data(df, database_filename):
"""
INPUT
df - Dataframe with cleaned and contatenated data
database_filename - Name to be used for saving to database
OUTPUT
Saves dataframe to database table
"""
engine = create_engine('sqlite:///'+database_filename)
df.to_sql('ResponseData', engine, index=False)
def createReclassifyList():
#this is a sub function for createMTR(). references the mtr value in psotgres to create a list containing arbitray trajectory value and associated new mtr value
engine = create_engine('postgresql://*****:*****@144.92.235.105:5432/usxp')
df = pd.read_sql_query('SELECT "Value", mtr from pre.traj_cdl_b',con=engine)
print df
fulllist=[[0,0,"NODATA"]]
# fulllist=[]
for index, row in df.iterrows():
templist=[]
value=row['Value']
mtr=row['mtr']
templist.append(int(value))
templist.append(int(mtr))
fulllist.append(templist)
print 'fulllist: ', fulllist
return fulllist
virtualenv 及 IPython 操作视频：
Play Video
后续的 SQLAlchemy 基本操作都提供了详细的操作代码和预期的结果，不涉及到难以理解的操作内容，操作过程中需要细心理解 sqlalchemy 模块中每个类及函数的作用。
注意：在 ipython 中连续键入两个回车换行就会进入一个新的输入，这个时候如果你的类还没有写完，那么不要连续键入两次回车。
连接数据库
使用 SQLAlchemy 连接数据库主要通过 Engine 对象进行，在 IPython 终端中输入以下代码：
In [1]: from sqlalchemy import create_engine
In [2]: engine = create_engine('mysql://root:@localhost/shiyanlou')
In [3]: engine.execute('select * from user').fetchall()
Out[3]: [(1, 'aiden', '*****@*****.**'), (2, 'lxttx', '*****@*****.**')]
首先导入了 create_engine, 该方法用于创建 Engine 实例，传递给 create_engine 的参数定义了 MySQL 服务器的访问地址，其格式为 mysql://<user>:<password>@<host>/<db_name>，例子中访问的正是上文中创建的 shiyanlou 数据库。
接着通过 engine.execute 方法执行了一条 SQL 语句，查询了 user 表中的所有用户，非常容易。
对象关系映射
如果想使 Python 类映射到数据库表中，需要基于 SQLAlchemy 的 declarative base class，也就是宣言基类创建类。当基于此基类，创建 Python 类时，就会自动映射到相应的数据库表上。创建宣言基类，可以通过 declarative_base 方法进行，如下代码：
In [12]: from sqlalchemy.ext.declarative import declarative_base
In [13]: Base = declarative_base()
创建基类以后，在 IPython 终端中输入下面代码，创建 User 类，该类将映射到上文中创建的 user 表：
m sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 创建到数据库的引擎
engine = create_engine(
# mysql+pymysql://用户:密码@服务器/数据库?参数
'mysql+pymysql://root:[email protected]/tedu1903?charset=utf8',
encoding='utf8',
# echo=True # 打印日志信息，生产环境下不要启用
)
# 创建会话类，用于客户端对服务器执行增删改查
Session = sessionmaker(bind=engine)
# 创建实体类的基类
Base = declarative_base()
# 创建实体类
class Departments(Base):
__tablename__ = 'departments' # 设置与库中的哪张表关联
dep_id = Column(Integer, primary_key=True)
dep_name = Column(String(20), unique=True)
class Employees(Base):
__tablename__ = 'employees'
emp_id = Column(Integer, primary_key=True)
emp_name = Column(String(20))
birth_date = Column(Date)
def dfToDb(df,table_name,user,password,db_url):
engine= create_engine('oracle+cx_oracle://%s:%s@%s' % (user,password,db_url))
df.to_sql(table_name,engine,if_exists='append',index=False,chunksize=5000,dtype=type_mapping(df))
Base = declarative_base()
class petugas(Base):
__tablename__ = "petugas"
id_petugas = Column(Integer, primary_key=True)
nama_petugas = Column(String)
tempat_lahir = Column(String)
alamat = Column(String)
email = Column(String)
jenis_kelamin = Column(String)
id_laporan = Column(Integer)
engine = create_engine('sqlite:///:memory', echo=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session= Session()
session.close()
class laporan():
def __init__(self, *args, **kwargs):
self.__id_laporan = kwargs.get('id_laporan', '0001')
self.__jumlah_peminjaman = kwargs.get('jumlah_peminjaman', '14')
self.__bulan_tahun= kwargs.get('bulan_tahun', 'Maret 2020')
self.__jumlah_pengunjung = kwargs.get('jumlah_pengunjung', '87')
print "Resultados de mysql.connector:"
import mysql.connector
miConexion = mysql.connector.connect(host='localhost',
user='******',
passwd='PASS',
db='neoguias')
cur = miConexion.cursor()
cur.execute("SELECT nombre, apellido FROM usuarios")
for nombre, apellido in cur.fetchall():
print nombre, apellido
miConexion.close()
import create_engine
import pandas as pd
db = "sisVentas" #base de datos
table = "consulta" #tabla donde se almacena
#xls=pd.ExcelFile('DataPrueba.xlsx')
path = "C:\python\DataPrueba.xlsx" #ubicacion del archivo
url = "http://localhost/phpmyadmin://root:admin@Localhost/"
#url="mysql+mysqlconnector://root:admin@localhost/"
engine = create_engine(url + db, echo=False)
df = pd.read_excel(path)
#Print("read ok")
df.to_sql(name=table, con=engine, if_exists='append', index=False)
def test_connection_db(self):
engine = create_engine('postgresql://*****:*****@localhost:5432/cs162', echo = True)
import create_engine from sqlalchemy
engine.connect()
m sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Date
from datetime import datetime
from sqlalchemy.orm import sessionmaker
from datetime import datetime, timedelta
engine = create_engine('sqlite:///todo.db?check_same_thread=False')
Base = declarative_base()
today = datetime.today()
class Task(Base):
__tablename__ = 'task'
id = Column(Integer, primary_key=True)
task = Column(String, default='Nothing to do!')
deadline = Column(Date, default=datetime.today())
def __repr__(self):
return self.task
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
def engine():
engine = create_engine(
MYSQL_CONN_URL,
encoding='utf8',convert_unicode=True
)
return engine
from pandas import pandas as pd
import snownlp
import pymysql
import sqlalchemy import create_engine
df = pd.read_csv('./book_douban/comment_4913064.txt')
df["sentiments"]= df["content"].map(lambda c : snownlp.SnowNLP(c).sentiments)
engine = create_engine('mysql+pymysql://192.168.2.189:root@Aa1234')
df.to_sql(name='test_snownlp', con=engine, chunksize=1000, if_exists='replace', index=None)
def db_connect():
"""Performs database connection using database settings from settings.py.
Returns sqlalchemy engine instance
"""
return create_engine(URL(**settings.DATABASE))
import sqlachemy import create_engine
import requests
import googl
class Imgur(callbacks.Plugin):
"""Add the help for "@plugin help Imgur" here
This should describe *how* to use this plugin."""
threaded = True
def __init__(self, irc):
self.shortenDbEngine =
create_engine('mysql+mysqldb:://%s:%s@%s/%s' %
(self.registryValue('shorten_mysql_user'), self.registryValue('shorten_mysql_pass'),
self.registryValue('shorten_mysql_server'),
self.registryValue('shorten_mysql_db')), pool_size = 2, pool_recycle=3600,
echo=False)
self.shorten = googl.Googl(self.shortenDbEngine,
self.registryValue('shorten_api_key'))
def _lookUpHash(self, imgur_hash):
albumService = 'http://imgur.com/gallery/%s.json'
try:
if imgur_hash is None or imgur_hash == '':
return None
response = requests.get(albumService % imgur_hash)
if response.status_code == 200:
return response.json['data']['image']
else:
return None
def save_data():
df = pd.read_csv('./book_douban/comment_4913064.txt')
df["sentiments"]= df["content"].map(lambda c : snownlp.SnowNLP(c).sentiments)
engine = create_engine('mysql+pymysql://192.168.2.189:root@Aa1234')
df.to_sql(name='test_snownlp', con=engine, chunksize=1000, if_exists='replace', index=None)
USERNAME = '******'
PASSWORD = '******'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8'.\
format(username=USERNAME,
password=PASSWORD,
host=HOSTNAME,
port=PORT,
db=DATABASE)
engine = create_engine(DB_URI)
# declarative_base()是一个工厂函数，它为声明性类定义构造基类
Base = declarative_base(engine)
# Session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。
# sessionmaker方法创建了一个Session工厂
session = sessionmaker(engine)()
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=False)
def __repr__(self):
return self.username
#Provides a way to build SQL Statements in Python
form sqlalchemy import create_engine
engine = create_engine('ssglite:///filename.sglite')
connection = engine.connect
from sqlalchemy import Table, MetaData
metadata = MetaData()
census = Table('Table', metadata, autoload=True, autoload_with=engine)
stmt = select([Table])
results = connection.excute(stmt).fetchall()
#SQLAlchemy select statement works the same as a SQl select statement
#requires a list of one or more Tables or Columns
#Will select all the columns of all the rows in the table
stmt = select([Table])
#The print(stmt) will output the statement 'SELECT * FROM Table'
