Why there are 2 different ouputs

Question

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,create_engine
engine = create_engine('sqlite:///college.db',echo = True)
metadata = MetaData()
users = Table('users', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String(50)),
      Column('fullname', String(50)),
)

addresses = Table('addresses', metadata,
   Column('id', Integer, primary_key=True),
   Column('user_id', None, ForeignKey('users.id')),
   Column('email_address', String(50), nullable=False))
metadata.create_all(engine)

This is the first Output:

CREATE TABLE addresses (
id INTEGER NOT NULL,
user_id INTEGER,
email_address VARCHAR NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)

Then, I hit run again, the output changed:

2019-04-18 21:06:57,881 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-04-18 21:06:57,886 INFO sqlalchemy.engine.base.Engine ()
2019-04-18 21:06:57,892 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-04-18 21:06:57,899 INFO sqlalchemy.engine.base.Engine ()
2019-04-18 21:06:57,904 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2019-04-18 21:06:57,909 INFO sqlalchemy.engine.base.Engine ()
2019-04-18 21:06:57,923 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses")
2019-04-18 21:06:57,928 INFO sqlalchemy.engine.base.Engine ()
metadata.create_all(engine)
2019-04-18 21:07:17,156 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2019-04-18 21:07:17,157 INFO sqlalchemy.engine.base.Engine ()

Could annyone explain why there are some changes after i hit run the second time?

score 0 · Accepted Answer · answered Apr 19 '19 at 08:25

When you run your code the second time, the tables are already created, and that is why you get the output that you see. If you want to get the same output again, you have to drop your tables first:

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,create_engine
engine = create_engine('sqlite:///college.db',echo = True)
metadata = MetaData()
users = Table('users', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String(50)),
      Column('fullname', String(50)),
)

addresses = Table('addresses', metadata,
   Column('id', Integer, primary_key=True),
   Column('user_id', None, ForeignKey('users.id')),
   Column('email_address', String(50), nullable=False))
metadata.create_all(engine)
users.drop(engine)
addresses.drop(engine)

But that will leave you with an empty database of course! To get an exception when re-creating tables you could use the checkfirst parameter:

metadata.create_all(engine, checkfirst=False)

The PRAGMA output you see is useful to get some metadata of your tables:

foo = engine.execute('PRAGMA table_info("addresses")').fetchall()
print(foo)

Why there are 2 different ouputs

1 Answers1