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)