class TimestampMixin:
"""
Class to be inherited by other classes to get user trail attributes
such as created_on and last_updated_on
:ivar created_by: User who created this row
:vartype created_by: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar created_on: Timestamp for creation time
:vartype created_on: class:`sqlalchemy.dialects.mysql.types.TIMESTAMP`
:ivar last_updated_by: User who last updated the record
:vartype last_updated_by: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar last_updated_on: Timestamp for last updation
:vartype: last_updated_on: class:`sqlalchemy.dialects.mysql.types.TIMESTAMP`
"""
__table_args__ = {'mysql_engine': 'InnoDB'}
created_by = Column(NVARCHAR(250), default="SYSTEM", nullable=False)
created_on = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP"), nullable=False)
last_updated_by = Column(NVARCHAR(250), default="SYSTEM", nullable=False)
last_updated_on = Column(TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
server_onupdate=FetchedValue(), nullable=False)
class TracksTable(TimestampMixin, BASE):
"""
ORM class for the Tracks table
:ivar track_id: Primary key of Track Table
:vartype track_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar name: Name of Track
:vartype name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar composer: Name of composer
:vartype composer: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar milliseconds: Length of track in milliseconds
:vartype milliseconds: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar bytes: size of tracks in bytes
:vartype bytes: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar unit_price: unit price of track
:vartype unit_price: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar album_id: Foregin key representing the album id this track belongs to
:vartype album_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar media_type_id: Foregin key representing the mediatype this track belongs to
:vartype media_type_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar genre_id: Foregin key representing the GenreId this track belongs to
:vartype genre_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
"""
__tablename__ = 'track'
track_id = Column(INTEGER(unsigned=True), name="TrackId", primary_key=True, autoincrement=True, nullable=False)
# NVARCHAR types
name = Column(NVARCHAR(200), name="Name", nullable=False)
composer = Column(NVARCHAR(220), name="Composer")
# Numeric and Integer Types
milliseconds = Column(INTEGER(unsigned=True), name="Milliseconds", nullable=False)
bytes = Column(INTEGER(unsigned=True), name="Bytes")
unit_price = Column(NUMERIC(10, 2), name="UnitPrice", nullable=False)
# Foreign Key Columns
album_id = Column(INTEGER(unsigned=True), ForeignKey('album.AlbumId', onupdate="NO ACTION", ondelete="NO ACTION"),
name="AlbumId", nullable=False, index=True)
media_type_id = Column(INTEGER(unsigned=True), ForeignKey('mediatype.MediaTypeId', onupdate="NO ACTION",
ondelete="NO ACTION"), name="MediaTypeId",
nullable=False, index=True)
genre_id = Column(INTEGER(unsigned=True), ForeignKey('genre.GenreId', onupdate="NO ACTION", ondelete="NO ACTION"),
name="GenreId")
class Question(Base):
__tablename__ = 'zhihu_questions'
id = Column(INTEGER(), primary_key=True)
url = Column(VARCHAR(45))
title = Column(NVARCHAR(100))
content = Column(MEDIUMTEXT(), nullable=True)
topic = Column(NVARCHAR(200))
answers_num = Column(INTEGER())
follower = Column(INTEGER())
watcher = Column(INTEGER())
crawl_time = Column(DATETIME())
class ModelFeature(Base):
__tablename__ = 'model_feature'
id = Column(INTEGER, primary_key=True)
value_from = Column(DOUBLE)
value_to = Column(DOUBLE)
max_value = Column(DOUBLE)
min_value = Column(DOUBLE)
process_method = Column(NVARCHAR(length=200))
model_id = Column(INTEGER, ForeignKey(Model.id))
model = relationship(Model,backref="model")
feature_name = Column(NVARCHAR(50), ForeignKey(Feature.name))
feature = relationship(Feature)
class AlbumTable(TimestampMixin, BASE):
"""
ORM class for the Album table
:ivar album_id: Primary key of Album Table
:vartype album_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar title: Title of Album
:vartype title: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar artist_id: Foregin key representing the artist id involving in this album
:vartype artist_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar tracks: List of all tracks that belongs to this album
:vartype tracks: list
"""
__tablename__ = 'album'
album_id = Column(INTEGER(unsigned=True), name="AlbumId", primary_key=True, autoincrement=True, nullable=False)
title = Column(NVARCHAR(160), name="Title", nullable=False)
# Foreign Key
artist_id = Column(INTEGER(unsigned=True), ForeignKey('artist.ArtistId', onupdate="NO ACTION", ondelete="NO "
"ACTION"),
name="ArtistId", nullable=False, index=True)
# Relationships
tracks = relationship("TracksTable", backref=backref("album"), cascade="all, delete, delete-orphan")
class Data(Base):
__tablename__ = 'data'
id = Column(INTEGER, primary_key=True)
time = Column(TIMESTAMP)
monitoring = Column(BOOLEAN)
pod = Column(NVARCHAR(length=100))
featureData = relationship("FeatureData")
class FeatureData(Base):
__tablename__ = 'feature_data'
id = Column(INTEGER, primary_key=True)
value = Column(DOUBLE)
feature_name = Column(NVARCHAR(50), ForeignKey(Feature.name))
# feature = relationship(Feature)
data_id = Column(INTEGER, ForeignKey(Data.id))
data = relationship(Data,backref="dataItem")
class Model(Base):
__tablename__ = 'model'
id = Column(INTEGER, primary_key=True)
data_time_from = Column(TIMESTAMP)
data_time_to = Column(TIMESTAMP)
kpi_index = Column(DOUBLE)
max_pdf = Column(DOUBLE)
min_pdf = Column(DOUBLE)
mean_pdf = Column(DOUBLE)
kpi_pdf = Column(DOUBLE(asdecimal=False))
description = Column(NVARCHAR(200))
features = relationship("ModelFeature")
class Answer(Base):
__tablename__ = 'zhihu_answers'
q_id = Column(INTEGER(), ForeignKey('zhihu_questions.id'))
question = relationship('Question', backref='answer')
answer_id = Column(INTEGER(), primary_key=True)
author_id = Column(VARCHAR(100))
author_name = Column(NVARCHAR(20))
author_is_advertiser = Column(BOOLEAN())
created_time = Column(DATETIME())
updated_time = Column(DATETIME())
voteup_num = Column(INTEGER())
comment_num = Column(INTEGER())
content = Column(MEDIUMTEXT())
def test_column_datatype_to_string(self):
test_cases = (
(DATE(), "DATE"),
(VARCHAR(length=255), "VARCHAR(255)"),
(
VARCHAR(length=255, charset="latin1", collation="utf8mb4_general_ci"),
"VARCHAR(255)",
),
(NVARCHAR(length=128), "NATIONAL VARCHAR(128)"),
(TEXT(), "TEXT"),
)
for original, expected in test_cases:
actual = MySQLEngineSpec.column_datatype_to_string(
original, mysql.dialect()
)
self.assertEqual(actual, expected)
class GenreTable(TimestampMixin, BASE):
"""
ORM class for the genre table
:ivar genre_id: Primary key of Genre Table
:vartype genre_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar name: Name of genre
:vartype name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar tracks: List of all tracks that belongs to this genre
:vartype tracks: list
"""
__tablename__ = 'genre'
genre_id = Column(INTEGER(unsigned=True), name="GenreId", primary_key=True, autoincrement=True, nullable=False)
name = Column(NVARCHAR(120), name="Name")
# Relationships
tracks = relationship("TracksTable", backref=backref("genre"), cascade="all, delete, delete-orphan")
class ArtistTable(TimestampMixin, BASE):
"""
ORM class for the Artist table
:ivar artist_id: Primary key of Artist Table
:vartype artist_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar name: Name of Artist
:vartype name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar albums: List of all albums that belongs to this Artist
:vartype albums: list
"""
__tablename__ = 'artist'
artist_id = Column(INTEGER(unsigned=True), name="ArtistId", primary_key=True, autoincrement=True, nullable=False)
name = Column(NVARCHAR(120), name="Name")
# Relationships
albums = relationship("AlbumTable", backref=backref("artist"), cascade="all, delete, delete-orphan")
def storeDatafileToDB(newdata):
engine = create_engine(
'mysql+pymysql://root:yourpasswordhere@localhost:3306/Weblogs')
meta = MetaData()
weblogInfo = Table('weblogInfo', meta,
Column('url', NVARCHAR(60), primary_key=True),
Column('empty1', TEXT(60), nullable=False),
Column('empty2', TEXT(60), nullable=False),
Column('date', TEXT(60), nullable=False),
Column('get', TEXT(60), nullable=False),
Column('value1', Integer(), nullable=False),
Column('value1', Integer(), nullable=False))
weblogInfo.create(engine)
# path to a file to be read
path = '/Users/damian/Desktop/Python_Projects/Weblogs/access_log_Aug95'
# Performing convertion to structured data
newData = readWebLogs.structureFile(path)
newData[5] = newData[5].str.replace("\ufffd", "")
return newData.to_sql(con=engine,
name='weblogInfo',
if_exists='replace',
index=False)
class PlaylistTable(TimestampMixin, BASE):
"""
ORM class for the Playlist Table
:ivar play_list_id: Primary key of Playlist Table
:vartype play_list_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar name: The name of playlist
:vartype name:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar tracks_in_playlist: List of tracks in this playlist
:vartype tracks_in_playlist: list
"""
__tablename__ = 'playlist'
play_list_id = Column(INTEGER(unsigned=True), name="PlaylistId", primary_key=True, autoincrement=True,
nullable=False)
name = Column(NVARCHAR(120), name="Name")
# Relationships
tracks_in_playlist = relationship("TracksTable", backref=backref("playlist_involved"), secondary="playlisttrack")
class EmployeeTable(TimestampMixin, BASE):
"""
ORM class for the Employee Table
:ivar employee_id: Primary key of Employee Table
:vartype employee_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar last_name: Last Name of employee
:vartype last_name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar first_name: First name of employee
:vartype first_name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar title: Title of employee
:vartype title: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar address: Address of employee
:vartype address: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar city: City of employee
:vartype city: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar state: State of employee
:vartype state: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar country: Country of employee
:vartype country: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar postal_code: PostalCode of employee
:vartype postal_code: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar phone: Phone of employee
:vartype phone: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar fax: Fax of employee
:vartype fax: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar email: Email of employee
:vartype email: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar birth_date: Birthdate of empolyee
:vartype birth_date: class:`sqlalchemy.dialects.mysql.types.DATETIME`
:ivar hire_date: HireDate of empolyee
:vartype hire_date: class:`sqlalchemy.dialects.mysql.types.DATETIME`
:ivar reports_to: Foregin Key representing the Manager to whom the emplyee reports to
:vartype reports_to: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar manages: List of all employee the manager manages
:vartype manages: list
:ivar customer: List of customers the employee handles
:vartype customer: list
"""
__tablename__ = 'employee'
employee_id = Column(INTEGER(unsigned=True), name="EmployeeId", primary_key=True, autoincrement=True,
nullable=False)
# NVARCHAR data type
last_name = Column(NVARCHAR(20), name="LastName", nullable=False)
first_name = Column(NVARCHAR(20), name="FirstName", nullable=False)
title = Column(NVARCHAR(30), name="Title")
address = Column(NVARCHAR(70), name="Address")
city = Column(NVARCHAR(40), name="City")
state = Column(NVARCHAR(40), name="State")
country = Column(NVARCHAR(40), name="Country")
postal_code = Column(NVARCHAR(10), name="PostalCode")
phone = Column(NVARCHAR(24), name="Phone")
fax = Column(NVARCHAR(24), name="Fax")
email = Column(NVARCHAR(24), name="Email")
# DATETIME data type
birth_date = Column(DATETIME, name="BirthDate")
hire_date = Column(DATETIME, name="HireDate")
# Foreign Key
reports_to = Column(INTEGER(unsigned=True), ForeignKey("employee.EmployeeId", onupdate="NO ACTION",
ondelete="NO ACTION"), name="ReportsTo", index=True)
# Relationships
manages = relationship("EmployeeTable", backref=backref("manager", remote_side=[employee_id]))
customers = relationship("CustomerTable", backref=backref("support_rep"), cascade="all, delete, delete-orphan")
df[['name', 'owner', 'species',
'sex']] = df[['name', 'owner', 'species',
'sex']].astype(sqlalchemy.dialects.mysql.VARCHAR) #text
df['birth'] = df['birth'].astype('datetime64[ns]')
df['death'] = df['death'].astype('datetime64[ns]')
df2 = pd.DataFrame([[u'小黑狗', u'陳一宏', 'dog', 'm', '2011-01-03']],
columns=['name', 'owner', 'species', 'sex', 'birth'])
df2
df = df.append(df2, ignore_index=True)
df
import sqlalchemy
from sqlalchemy.dialects.mysql import VARCHAR, NVARCHAR
from sqlalchemy import create_engine
dbname = 'menagerie'
connstr = "mysql+mysqldb://ehome4829:[email protected]:3306/{}?charset=utf8".format(
dbname) # 要加入?charset=utf8才不會亂碼
engine = create_engine(connstr)
conn = engine.connect()
df.to_sql('pet',
conn,
if_exists='replace',
index=False,
dtype={
'name': NVARCHAR(20),
'owner': NVARCHAR(20),
'species': VARCHAR(5)
})
df.to_sql('pet', conn, if_exists='replace', index=False) # 會變成text格式...
print("database: raritan_chatbot is just created")
else:
print("database: raritan_chatbot exists")
#create table
from sqlalchemy import MetaData, Table, Column
from sqlalchemy.dialects.mysql import NVARCHAR, TINYINT, SMALLINT, LONGTEXT, MEDIUMTEXT, DATETIME, DECIMAL, INTEGER
import datetime
meta = MetaData()
chatlog = Table(
'chatlog', meta, Column('id', INTEGER, primary_key=True),
Column('ui_date_time', DATETIME),
Column('webhook_date_time', DATETIME, default=datetime.datetime.now),
Column('raritan_chatbot', NVARCHAR(length=40)),
Column('user_name', NVARCHAR(length=40)),
Column('icon_url', MEDIUMTEXT), Column('qna_id', NVARCHAR(length=50)),
Column('rule_id', SMALLINT), Column('session_id', NVARCHAR(50)),
Column('question', MEDIUMTEXT), Column('answer', LONGTEXT),
Column('short_message', NVARCHAR(length=100)),
Column('component_id', SMALLINT),
Column('component_value', NVARCHAR(length=300)),
Column('qna_score', DECIMAL(5, 2)), Column('qna_source', NVARCHAR(50)),
Column('status_id', SMALLINT), Column('user_agent', MEDIUMTEXT),
Column('ip', NVARCHAR(length=16)),
Column('TicketNumber', NVARCHAR(length=16)),
Column('TicketID', NVARCHAR(length=5)),
Column('TicketState', NVARCHAR(length=5)))
status_master = Table('status_master', meta,
Column('status_id', INTEGER, primary_key=True),
class CustomerTable(TimestampMixin, BASE):
"""
ORM class for the Customer Table
:ivar customer_id: Primary key of Customer Table
:vartype customer_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar last_name: Last Name of employee
:vartype last_name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar first_name: First name of employee
:vartype first_name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar company: Company of employee
:vartype company: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar address: Address of employee
:vartype address: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar city: City of employee
:vartype city: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar state: State of employee
:vartype state: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar country: Country of employee
:vartype country: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar postal_code: PostalCode of employee
:vartype postal_code: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar phone: Phone of employee
:vartype phone: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar fax: Fax of employee
:vartype fax: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar email: Email of employee
:vartype email: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar support_rep_id: Foreign Key representing the Employee who handles this customer
:vartype support_rep_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar manages: List of all employee the manager manages
:vartype manages: list
:ivar invoices: List of invoices in which this customer was involved
:vartype invoices: list
"""
__tablename__ = 'customer'
# Primary Key
customer_id = Column(INTEGER(unsigned=True), name="CustomerId", primary_key=True, autoincrement=True,
nullable=False)
# NVARCHAR data type
last_name = Column(NVARCHAR(20), name="LastName", nullable=False)
first_name = Column(NVARCHAR(40), name="FirstName", nullable=False)
company = Column(NVARCHAR(80), name="Company")
address = Column(NVARCHAR(70), name="Address")
city = Column(NVARCHAR(40), name="City")
state = Column(NVARCHAR(40), name="State")
country = Column(NVARCHAR(40), name="Country")
postal_code = Column(NVARCHAR(10), name="PostalCode")
phone = Column(NVARCHAR(24), name="Phone")
fax = Column(NVARCHAR(24), name="Fax")
email = Column(NVARCHAR(60), name="Email", nullable=False)
# Foreign Key
support_rep_id = Column(INTEGER(unsigned=True), ForeignKey("employee.EmployeeId", onupdate="NO ACTION",
ondelete="NO ACTION"), name="SupportRepId", index=True)
# Relationships
invoices = relationship("InvoiceTable", backref=backref("customer"), cascade="all, delete, delete-orphan")
class InvoiceTable(TimestampMixin, BASE):
"""
ORM class for the Invoice Table
:ivar invoice_id: Primary key of Invoice Table
:vartype invoice_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar billing_address: BillingAddress of invoice
:vartype billing_address: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_city: BillingCity of invoice
:vartype billing_city: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_state: BillingState of invoice
:vartype billing_state: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_country: BillingCountry of invoice
:vartype billing_country: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_postal_code: BillingPostalCode of invoice
:vartype billing_postal_code: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar invoice_date: Date of invoice
:vartype invoice_date: class:`sqlalchemy.dialects.mysql.types.DATETIME`
:ivar total: Total cost of invoice
:vartype total: class:`sqlalchemy.dialects.mysql.types.NUMERIC`
:ivar customer_id: Foreign key representing the customer involved in this invoive
:vartype customer_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar purchased_tracks: List of tracks involved in this invoice
:vartype purchased_tracks: list
"""
__tablename__ = 'invoice'
# Primary Key
invoice_id = Column(INTEGER(unsigned=True), name="InvoiceId", primary_key=True, autoincrement=True, nullable=False)
# NVARCHAR data type
billing_address = Column(NVARCHAR(70), name="BillingAddress")
billing_city = Column(NVARCHAR(40), name="BillingCity")
billing_state = Column(NVARCHAR(40), name="BillingState")
billing_country = Column(NVARCHAR(40), name="BillingCountry")
billing_postal_code = Column(NVARCHAR(10), name="BillingPostalCode")
# DATETIME data type
invoice_date = Column(DATETIME, name="InvoiceDate", nullable=False)
# Numeric Data Type
total = Column(NUMERIC(10, 2), name="Total", nullable=False)
# Foreign Key
customer_id = Column(INTEGER(unsigned=True), ForeignKey("customer.CustomerId", onupdate="NO ACTION",
ondelete="NO ACTION"), name="CustomerId",
nullable=False, index=True)
# Relationships
purchased_tracks = relationship("TracksTable", backref=backref("invoices"), secondary="invoiceline")
class Feature(Base):
__tablename__ = 'feature'
name = Column(NVARCHAR(length=50), primary_key=True)
