英文:
Using SQLAlchemy, how to copy a database of three tables over to my local MySQL database? What are the proper sequence of method calls?
问题
try:
# 从在线数据库中读取表并复制到本地MySQL
srcEngine = sqlalchemy.create_engine("mariadb+mariadbconnector://guest:relational@relational.fit.cvut.cz:3306/ConsumerExpenditures")
conn = srcEngine.connect()
meta = MetaData()
meta.reflect(bind=srcEngine)
meta.tables.keys() # 这里我看到了该数据库中的三个表
# 连接到本地数据库
database_uri = 'mysql+pymysql://root:1234@localhost:3306'
localEngine = sqlalchemy.create_engine(database_uri)
try:
with localEngine.connect() as conn_local:
# 如果不存在，则创建ConsumerExpenditures数据库，否则开始复制表
database = 'ConsumerExpenditures'
result = conn_local.execute(text("CREATE DATABASE IF NOT EXISTS {0} ".format(database)))
# 下面我试图从srcEngine获取元数据，并以某种方式复制其表和数据到刚刚创建的localEngine数据库。我不确定如何做这个？？
meta = MetaData()
messages = Table('EXPENDITURES', meta, autoload=True, autoload_with=srcEngine) # 尝试基于来自源引擎的元数据创建表，但出现错误。我应该调用哪些方法来复制它？
英文:
I want to make an exact copy (migration) from this online database:
https://relational.fit.cvut.cz/dataset/ConsumerExpenditures
and copy the tables and data inside of these tables over to my local MySQL database.
I have researched SQLAlchemy but with the jumble of reflection, metadata etc. I am confused as to the proper sequence of calls to copy these tables (see last part of the code block). Can anyone please let me know the proper way to do this?
Here is my code so far:
` try:
# Read tables from database and copy over to local MySQL
srcEngine = sqlalchemy.create_engine("mariadb+mariadbconnector://guest:relational@relational.fit.cvut.cz:3306/ConsumerExpenditures")
conn = srcEngine.connect()
meta = MetaData()
meta.reflect(bind=srcEngine)
meta.tables.keys() # here I see the three tables in this database
# connect to local database
database_uri = 'mysql+pymysql://root:1234@localhost:3306'
localEngine = sqlalchemy.create_engine(database_uri)
try:
with localEngine.connect() as conn_local:
# create ConsumerExpenditures database if not exists else start copying over tables
database = 'ConsumerExpenditures'
result = conn_local.execute(text("CREATE DATABASE IF NOT EXISTS {0} ".format(database)))
# below I am trying to get the metadata from the srcEngine and somehow copy its tables and data over to the localEngine database that was just created. I am sure of the syntax to do this?
meta = MetaData()
messages = Table('EXPENDITURES', meta, autoload=True, autoload_with=srcEngine) # tried to create a table based on the meta data from the source engine but is throwing an error. What is the call(s) I should make to copy it over?
`
答案1
得分: 1
假设这是一个常规的MySQL数据库或MariaDB，在我的经验中（并不是Python的狂热爱好者），在将数据库复制到另一台服务器时，使用MYSQLDUMP命令导出，然后使用MYSQL命令导入。
导出
mysqldump -u [用户名] -p [数据库名] > [文件名].sql
导入/恢复
mysql -u [用户名] -p [数据库名] < [文件名].sql
你可以使用Python来执行这些操作，使用subprocess或os，参考：
https://docs.python.org/3.6/library/subprocess.html
或
https://docs.python.org/3.6/library/os.html?highlight=system#os.system
或者，你也可以使用类似PHPMYADMIN或MYSQLworkbench这样的工具。
英文:
Assuming this is a regular MySql DB or MariaDb, in my experience (not being a massive fan of python), when copying a database to another server use MYSQLDUMP command to export then MYSQL command to import.
Export
mysqldump -u [user] -p [database_name] > [filename].sql
Import/restore
mysql -u [user] -p [database_name] < [filename].sql
You should be able to execute this with Python using subprocess or system see;-
https://docs.python.org/3.6/library/subprocess.html
or
https://docs.python.org/3.6/library/os.html?highlight=system#os.system
Alternatively use a tool like PHPMYADMIN or MYSQLworkbench.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论